SQL request, find people closest to me with MySQL-CodePudding

I am currently working on a Wordpress/PHP/MySQL project.

I would like to run a query to find people geographically close to the logged in user.

To do this I have two custom fields for a user present in the usermeta table, latitute and longitude like this:

I'd like to generate an SQL query, where "45.7699" is the user's latitude and "4.8037" is their longitude:

SELECT lat AS lat, lng AS lng, (6366*acos(cos(radians(45.7699))*cos(radians(lat))*cos(radians(lng) -radians(4.8037)) sin(radians(45.7699))*sin(radians(lat)))) AS dist FROM air_usermeta WHERE (6366*acos(cos(radians(45.7699))*cos(radians(lat))*cos(radians(lng) -radians(4.8037)) sin(radians(45.7699))*sin(radians(lat))))<='50' ORDER by dist ASC

My problem is at the beginning:

SELECT lat AS lat, lng AS lng

How to retrieve values in Wordpress context where lat and lng fields are not columns but meta_value.

Thanks a lot for your help.

[RESOLVED]

SELECT a.user_id, a.meta_value AS lat, b.meta_value AS lng FROM air_usermeta as a, air_usermeta as b WHERE a.meta_key = "lat" AND b.meta_key= "lng" AND a.user_id = b.user_id AND (6366*acos(cos(radians(45.7699))*cos(radians(a.meta_value))*cos(radians(b.meta_value) -radians(4.8037)) sin(radians(45.7699))*sin(radians(a.meta_value))))<='20' ORDER by (6366*acos(cos(radians(45.7699))*cos(radians(a.meta_value))*cos(radians(b.meta_value) -radians(4.8037)) sin(radians(45.7699))*sin(radians(a.meta_value)))) ASC

CodePudding user response：

you can select this using self join

SELECT a.id, a.meta_value as lat, b.meta_value as lng
FROM lbt_postmeta as a, 
lbt_postmeta as b
WHERE a.meta_key = "lat"
AND b.meta_key= "lng"
AND a.id = b.id