1

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.

database stracture

Community
  • 1
  • 1
Meathanjay
  • 2,023
  • 1
  • 18
  • 24
  • So the problem is you have the long. and lat. in seperate rows but need them both in the same row? Basically you're looking for a query to transform vertical to horizontal data: http://stackoverflow.com/questions/4071811/how-to-transform-vertical-data-into-horizontal-data-with-sql – developerwjk Oct 08 '15 at 19:22
  • Yes, something like that. I am following WP recommendation for geo data, and I want to know is it possible anyhow before changing database structure? – Meathanjay Oct 08 '15 at 19:30
  • With the link I added to the comment above you should be able to do a query to get it all in one row. – developerwjk Oct 08 '15 at 19:31
  • Yeah, trying to use Pivot table, thanks for the link :) – Meathanjay Oct 08 '15 at 19:35
  • Do you think anyhow is it possible to transform vertical to horizontal data and, then use in another SQL query? I mean first I wanna transform to horizontal and the used in the query calculate radius? – Meathanjay Oct 08 '15 at 19:53

1 Answers1

2

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

AnneMz
  • 484
  • 1
  • 6
  • 16