Home > Article > Backend Development > SQL query for nearby people
The longitude and latitude passed from the client are saved in the database, and SQL statements are used to query nearby people. This article mainly shares with you examples of SQL queries for nearby people, hoping to help everyone.
TABLE_NAME Table structure, which are auto-increment ID, city ID, longitude and latitude
id city_id y x
1 1901 22.982087 113.318505
2 1901 23.079377 113.298556
LAT/LNG are the latitude and longitude respectively, which are passed by the client
select city_id,y,x, ACOS(SIN((LAT * 3.1415) / 180) * SIN((y * 3.1415) / 180 ) + COS((LAT* 3.1415) / 180 ) * COS(( y * 3.1415) / 180 ) *COS((LNG* 3.1415) / 180 - (x * 3.1415) / 180 ) ) * 6380 as distance from TABLE_NAME WHERE city_id=1901 ORDER BY distance
mysql example
select city_id,y,x,ACOS(SIN((23.13678584271096 * 3.1415) / 180) * SIN((y * 3.1415) / 180 ) + COS((23.13678584271096 * 3.1415) / 180 ) * COS((y * 3.1415) / 180 ) *COS((113.2937260476958* 3.1415) / 180 - (x * 3.1415) / 180 ) ) * 6380 as distance from TABLE_NAME WHERE city_id=1901 ORDER BY distance
Related recommendations:
PHP implements the function of searching for people nearby
How to query nearby people and their distance in PHP_PHP
The above is the detailed content of SQL query for nearby people. For more information, please follow other related articles on the PHP Chinese website!