How to query posts based on lat-lng coordinate as post meta?

I'm planning a custom WordPress theme where the Custom Post Type (CPT) will have latitude and longitude coordinate as it's meta value. The latitude and longitude will be displayed as a Marker in a Google Map.

So far I don't have any problem in showing the Google Map and the CPT as it's Marker. That is if I query the CPT using the default order.

The problem occurs when I need n CPTs that is closest to a coordinate whether it is current user position or a location clicked on the map. Another situation is to query all CPTs that is in x km radius of a coordinate.

The question is : How do I query posts based on latitude-longitude value that is saved on the post meta?

I'm not really sure how to do it, but I think the latitude and longitude value should be saved in a separate custom field.

This is a simple mathimatical problem. You will indeed need access to both your longitude and latitude, so save it in a metafield.

than you will have to query your posts like this as a sql query. Haven't got a chance to test it. and or pour it into wordpress. Don't have access to my test env now. But I guess you could do it yourself :) if not I'll do it later on when I can.

``````set @latitude = xxx; — center latitude
set @longitude = xxx; — center longitude
set @distance = xx; — search distance

select p.ID, p.post_name, ((ACOS(SIN(@latitude * PI() / 180) * SIN(`latitude.meta_value` * PI() / 180) + COS(@latitude * PI() / 180) * COS(`latitude.meta_value` * PI() / 180) * COS((@longitude – `longitude.meta_value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
from wp_posts p
left join wp_postmeta latitude on latitude.post_id = p.ID and latitude.meta_key = ‘_latitude’
left join wp_postmeta longitude on longitude.post_id = p.ID and longitude.meta_key = ‘_longitude’
having distance < @distance;
``````
• This is a great start. Thank you very much. I'm not that good on sql queries, I'll respond if i've figured it out. Commented Aug 14, 2012 at 10:26
• I was wondering if you managed to get it to work? Commented Aug 17, 2012 at 18:16

For anyone looking for this answer in the future, I took woony's code the extra mile and got everything working within Wordpress' post_meta table structure. This assumes you have two separate custom fields, one for Latitude (city_latitude) and one for longitude (city_longitude). Just pass the latitude, longitude, and distance parameters into a WP function and you should be set.

Here's the WP function. Drop this in your functions.php file:

``````function get_nearby_cities(\$lat, \$long, \$distance){
global \$wpdb;
\$nearbyCities = \$wpdb->get_results(
"SELECT DISTINCT
city_latitude.post_id,
city_latitude.meta_key,
city_latitude.meta_value as cityLat,
city_longitude.meta_value as cityLong,
((ACOS(SIN(\$lat * PI() / 180) * SIN(city_latitude.meta_value * PI() / 180) + COS(\$lat * PI() / 180) * COS(city_latitude.meta_value * PI() / 180) * COS((\$long - city_longitude.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance,
wp_posts.post_title
FROM
wp_postmeta AS city_latitude
LEFT JOIN wp_postmeta as city_longitude ON city_latitude.post_id = city_longitude.post_id
INNER JOIN wp_posts ON wp_posts.ID = city_latitude.post_id
WHERE city_latitude.meta_key = 'city_latitude' AND city_longitude.meta_key = 'city_longitude'
HAVING distance < \$distance
ORDER BY distance ASC;"
);

if(\$nearbyCities){
return \$nearbyCities;
}
}
``````

Return the value in your template file:

``````\$nearbyCities = get_nearby_cities(get_post_meta(\$post->ID, 'city_latitude', true), get_post_meta(\$post->ID, 'city_longitude', true), 25);
``````
• hey @mread ... can you tell me that the result we get is in miles or kilometers Commented Feb 15, 2017 at 6:44
• The result is in miles, I'm not sure what the formula is for kilometers, but I'm sure you can find that somewhere. You would need to update this line `((ACOS(SIN(\$lat * PI() / 180) * SIN(city_latitude.meta_value * PI() / 180) + COS(\$lat * PI() / 180) * COS(city_latitude.meta_value * PI() / 180) * COS((\$long - city_longitude.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)` Commented Feb 16, 2017 at 14:05
• Jsut for future reference - this ist the code for kilometers: `(6371 * acos(cos(radians( \$lat )) * cos(radians( city_latitude.meta_value )) * cos(radians( city_longitude.meta_value ) - radians( \$long )) + sin(radians( \$lat )) * sin(radians( city_latitude.meta_value )))) AS distance,` Commented Jan 18, 2021 at 18:24
• Okay I am rreally lost here. Can anyone help? I have a post with custom fields `t_lat` and `t_long`. How can I use the above function to check if the values of `t_lat` and `t_long` are nearby a given lat and lng value? Commented Dec 23, 2021 at 14:00
• @RobbTe you should be able to add the `get_nearby_cities()` function in your `functions.php` file. Then you'll need to update all instances of `city_latitude` to match your custom field name `t_lat`. Same with `city_longitude` => `t_long`. That SQL method should grab all the post ID's that have a `t_lat` and `t_long` within the radius provided. To test it... execute the function in your template file by hard coding some values for lat, long and distance for say a 25 mile radius in Chicago IL: `get_nearby_cities(41.8781, 87.6298, 25);` Commented Mar 1, 2022 at 15:00

Another solution build upon the other ones:

• Used \$wpdb->prepare for parameter escaping
• Used table names from \$wpdb
• Added option to switch between miles and km
• Added option to set post type
• Extracted names for meta fields
``````     /**
* @param float   \$latitude Latitude of the center
* @param float   \$longitude longitude of the center
* @param int     \$min_distance Minimal distance from the center (Default: 0).
* @param int     \$max_distance Maximal distance from the center (Default: 100).
* @param int     \$limit Maximal number of results (Default: 20).
* @param string  \$post_type Post type to filter for (Default: 'pois').
* @param boolean \$use_miles Set to true, if you are using miles instead of kilometers (Default: false).
* @return array|object
*/
function my_get_nearby_locations( \$latitude, \$longitude, \$min_distance = 0, \$max_distance = 100, \$limit = 20, \$post_type = 'pois', \$use_miles = false ) {
global \$wpdb;

\$meta_key_latitude = 'location_lat';
\$meta_key_longitude = 'location_long';

\$miles_to_km = \$use_miles ? 1 : 1.609344;

\$query = "SELECT DISTINCT
t_lat.post_id,
t_post.post_title,
t_lat.meta_value as latitude,
t_long.meta_value as longitude,
((ACOS(SIN(%f * PI() / 180) * SIN(t_lat.meta_value * PI() / 180) + COS(%f * PI() / 180) * COS(t_lat.meta_value * PI() / 180) * COS((%f - t_long.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * {\$miles_to_km}) AS distance
FROM {\$wpdb->postmeta} AS t_lat
LEFT JOIN {\$wpdb->postmeta} as t_long ON t_lat.post_id = t_long.post_id
INNER JOIN {\$wpdb->posts} as t_post ON t_post.ID = t_lat.post_id
WHERE t_lat.meta_key = %s AND t_long.meta_key = %s AND t_post.post_type = %s
HAVING distance > %d AND distance < %d ORDER BY distance ASC LIMIT %d;";

\$prepared_query = \$wpdb->prepare( \$query, [ \$latitude, \$latitude, \$longitude, \$meta_key_latitude, \$meta_key_longitude, \$post_type, \$min_distance, \$max_distance, \$limit ] );

return \$wpdb->get_results( \$prepared_query );
}

``````
• Nicely done. Is there any way to include this into an existing wp_query or I am better to just include additionnal (optional) parameters into this? Commented Jan 21, 2021 at 15:16
• @PatricePoliquin Not sure, but there is a "post_clauses" filter which might work for this. developer.wordpress.org/reference/hooks/posts_clauses Commented Jan 25, 2021 at 8:21
• Hi, you stated 2 times `\$latitude` in the `\$prepared_query` is that correct? Also, any chance you can make this code working for advanced custom fields, maps field? It stores the lat and lng in an array like \$array['lat']. How to incorporate this into your solution? Thanks a million! Commented Dec 22, 2021 at 14:49
• @RobbTe, yes \$latitude is used 2 times (at the beginning in SIN and COS...). It might be improved using named parameter ... I'm using ACF OpenStreetMap field and are storing the coordinates in seperates fields within a save hook. If both parameters are stored as an array, you can't use the sinus /cosinus i.e. Commented Dec 24, 2021 at 7:54
• Hi @Adrian, thanks for getting back to me. For some reason I can't get this to work. I have a CPT called `locations`. I define this like `\$post_type = 'locations';`. My location posts have now the custom fields `location_lat` and `location_long`. The variables `\$latitude` and `\$longitude` hold the center lat and long values to search for locations nearby right? For some reason the returned array stays empty.. Any ideas? Commented Dec 24, 2021 at 9:31