0

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.

4

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;
    }
}
  • Unfortunately it doesn't work. When I debug it it returns _ MySQL returned an empty result set (i.e. zero rows)._ I'll play with it again tomorrow. Thank you for your help :) – Rafal Oct 24 '16 at 2:16
  • It returns #1054 - Unknown column 'p.post_id' in 'field list' – Rafal Oct 24 '16 at 13:06
  • @Rafal I've updated it from p.post_id to p.ID so it's fixed. Please mark my answer as accepted since it solved your question. :-) – cowgill Oct 25 '16 at 1:12
  • where is $radius being set here? – Marcel Nov 28 '17 at 20:22
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.