Post queries by latitude and longitude

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments