Post queries by latitude and longitude

by

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.

2 Answers
2

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;
    }
}

Leave a Comment