MySQL query to get nearest location from WordPress database WHERE longitude and latitude saved as post meta

3 Views

I have a custom post type “Store” and store address saved in postmeta table as geo_latitude, geo_longitude, geo_public, geo_address as WordPress recommend. Then we have all registered user latitude and longitude and stored in user meta. Now I want to get nearest 5 or 10 store id (basically postid) from the database using Haversine formula to calculate the radius.

I found a good thread in StackOverflow but there latitude and latitude as table row but my case latitude and latitude is row value (meta value). A sample database structure has attached.

Read More

database stracture

Post Views: 3

Related posts

1 comment

  1. This will do it (no pivot table) with self joins:

    SELECT a.post_id, a.meta_value as lat, b.meta_value as lng, c.meta_value as  address
FROM lbt_postmeta as a, 
lbt_postmeta as b,
lbt_postmeta as c

WHERE a.meta_key = "geo_latitude"
AND b.meta_key= "geo_longitude"
AND c.meta_key= "geo_address"
AND a.post_id = b.post_id
AND a.post_id = c.post_id

    gives you a table with post_id, lat, lng, address

Comments are closed.