Query nearby location information based on longitude and latitude

Now there is such a demand. I want to query the five locations nearby two kilometers in the data table based on the longitude and latitude of the current location. If the query result does not include five locations, I want to expand the nearby range until five locations are found. . I thought of two options:

The first option is to implement it completely using sql statements. If it is completely implemented using sql statements, I don’t know how to write this sql,

The second option is to query first, and then make a judgment based on the business results. If the 5 locations are not met, expand the nearby range and query in the database again until 5 locations are found.

I don’t know which of the two options is more efficient, please give me some advice. Grateful.

Reply content:

Now there is such a demand. I want to query the five locations nearby two kilometers in the data table based on the longitude and latitude of the current location. If the query result does not include five locations, I want to expand the nearby range until five locations are found. . I thought of two options:

The first option is to implement it completely using sql statements. If it is completely implemented using sql statements, I don’t know how to write this sql,

The second option is to query first, and then make a judgment based on the business results. If the 5 locations are not met, expand the nearby range and query in the database again until 5 locations are found.

I don’t know which of the two options is more efficient, please give me some advice. Grateful.

I don’t understand your two options at all.

Can’t you simplify your logic first?

Isn’t your request equal to: query the five nearest locations. Why do we need to distinguish between within two kilometers or two kilometers away.

If you query the latest five points, why do you need to consider any business judgment? Isn’t it just a Limit SQL statement?

The second option is feasible. It is completely implemented using SQL. It puts greater pressure on the database. The data layer and business layer must be executed separately to achieve higher efficiency.

You need Amap LBS Cloud, he will meet your needs

Try mysql spatial coordinate database,

<code>CREATE TABLE `spa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `loc` point DEFAULT NULL,
    /*  ^---------------------------------- 保存经纬度  */
  PRIMARY KEY (`id`)
)</code>

<code>/* 查询 */
SELECT * FROM spa WHERE DISTANCE(POINT($当前经度, $当前纬度), loc) < ($距离(单位米) / 111195) LIMIT 5;
/*                      ^-----单位为度                                                ^---1度多少米  */</code>

If you want to sort, you can order distance.

You need to use GEOHASH to turn the 2D coordinates into strings and then compare them