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