Post queries by latitude and longitude

The question:

I am struggling with getting post queries by coordinates. I have meta fields map_lat and map_lng for almost all post types. I am trying to return posts from one custom post type (“beaches” in this example):

function get_nearby_locations($lat, $long, $distance){
    global $wpdb;
    $nearbyLocations = $wpdb->get_results( 
    "SELECT DISTINCT    
        map_lat.post_id,
        map_lat.meta_key,
        map_lat.meta_value as locLat,
        map_lng.meta_value as locLong,
        ((ACOS(SIN($lat * PI() / 180) * SIN(map_lat.meta_value * PI() / 180) + COS($lat * PI() / 180) * COS(map_lat.meta_value * PI() / 180) * COS(($long - map_lng.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance,
        wp_posts.post_title
    FROM 
        wp_postmeta AS map_lat
        LEFT JOIN wp_postmeta as map_lng ON map_lat.post_id = map_lng.post_id
        INNER JOIN wp_posts ON wp_posts.ID = map_lat.post_id
    WHERE map_lat.meta_key = 'map_lat' AND map_lng.meta_key = 'map_lng'
    AND post_type='beaches'
    HAVING distance < $distance
    ORDER BY distance ASC;"
    );

    if($nearbyLocations){
        return $nearbyLocations;
    }
}

and im calling it with:

$nearbyLocation = get_nearby_cities(get_post_meta($post->ID, 'map_lat', true), get_post_meta($post->ID, 'map_lng', true), 25);

but it doesn’t return what I want.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Close. You need another INNER JOIN and should escape all your variables using $wpdb->prepare.

I’ve also included a more efficient Haversine formula (source) to calculate the radius.

If you use kilometers, then change the $earth_radius to 6371.

Also, a great way to debug is to echo the sql and paste it into phpMyAdmin (or whatever db app you use) and tweak it in there.

function get_nearby_locations( $lat, $lng, $distance ) {
    global $wpdb;

    // Radius of the earth 3959 miles or 6371 kilometers.
    $earth_radius = 3959;

    $sql = $wpdb->prepare( "
        SELECT DISTINCT
            p.ID,
            p.post_title,
            map_lat.meta_value as locLat,
            map_lng.meta_value as locLong,
            ( %d * acos(
            cos( radians( %s ) )
            * cos( radians( map_lat.meta_value ) )
            * cos( radians( map_lng.meta_value ) - radians( %s ) )
            + sin( radians( %s ) )
            * sin( radians( map_lat.meta_value ) )
            ) )
            AS distance
        FROM $wpdb->posts p
        INNER JOIN $wpdb->postmeta map_lat ON p.ID = map_lat.post_id
        INNER JOIN $wpdb->postmeta map_lng ON p.ID = map_lng.post_id
        WHERE 1 = 1
        AND p.post_type = 'beaches'
        AND p.post_status = 'publish'
        AND map_lat.meta_key = 'map_lat'
        AND map_lng.meta_key = 'map_lng'
        HAVING distance < %s
        ORDER BY distance ASC",
        $earth_radius,
        $lat,
        $lng,
        $lat,
        $distance
    );

    // Uncomment and paste into phpMyAdmin to debug.
    // echo $sql;

    $nearbyLocations = $wpdb->get_results( $sql );

    if ( $nearbyLocations ) {
        return $nearbyLocations;
    }
}

Method 2

I managed to make it work:

function get_nearby_locations( $lat, $long, $distance ) {
    global $wpdb;

    // Radius of the earth 3959 miles or 6371 kilometers.
    $earth_radius = 3959;

    $sql = $wpdb->prepare( "
            SELECT DISTINCT
                map_lat.post_id,
                p.post_title,
                map_lat.meta_value as locLat,
                map_lng.meta_value as locLong,

                 (
        6371 * ACOS(
          COS(RADIANS( %s )) * COS(RADIANS(map_lat.meta_value)) * COS(
            RADIANS(map_lng.meta_value) - RADIANS( %s )
          ) + SIN(RADIANS( %s )) * SIN(RADIANS(map_lat.meta_value))
        )
      ) AS distance
     FROM $wpdb->posts p
            INNER JOIN $wpdb->postmeta map_lat ON p.ID = map_lat.post_id
            INNER JOIN $wpdb->postmeta map_lng ON p.ID = map_lng.post_id
            WHERE 1 = 1
            AND p.post_type = 'beaches'
            AND p.post_status = 'publish'
            AND map_lat.meta_key = 'geo_lat'
            AND map_lng.meta_key = 'geo_lng'
            HAVING distance < %s
            ORDER BY distance ASC",
            $earth_radius,
            $lat,
            $lng,
            $lat,
            $radius
        );

        // Uncomment to echo, paste into phpMyAdmin, and debug.
        // echo $sql;

        $nearbyLocations = $wpdb->get_results( $sql );

        if ( $nearbyLocations ) {
            return $nearbyLocations;
        }
    }


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment