MariaDB的距离公式最近200个没有半径的地方

Question

I have MariaDB, Server version: 10.0.23-MariaDB, with latitude and longitude columns (float 10,6) plus a geo_location column (geometry) that was calculated from the latitude and longitude columns.

I would like to find the nearest 200 people from a person. The person at the center has a latitude and longitude that is passed to the query. Is there a way to do that without a radius? So, if the population density is high the radius would be small. If the population density is low then the radius would be large.

There are about 4 million rows, and it needs to be as fast as possible. The rows can be filtered first based on the county that they reside. Some counties are super large with low population density and others are small counties with high population density. I need the fastest way to find the nearest 200 people.

Answer 1

SELECT *, ST_DISTANCE(geo_location, POINT(lon, lat)) AS distance 
FROM geotable 
ORDER by distance DESC
LIMIT 200;

The bad news is that it will be very slow, because no spatial indexes are used by st_distance(). You should try to restrict your query by using a maximum radius to select less records:

set @dist = 100;
set @rlon1 = lon-@dist/abs(cos(radians(lat))*69);
set @rlon2 = lon+@dist/abs(cos(radians(lat))*69);
set @rlat1 = lat-(@dist/69);
set @rlat2 = lat+(@dist/69); 

SELECT *, ST_DISTANCE(geo_location, POINT(lon, lat)) AS distance 
FROM geotable 
WHERE ST_WITHIN(geo_location,ENVELOPE(LINESTRING(point(@rlon1, @rlat1), point(@rlon2, @rlat2)))) 
ORDER by distance DESC 
LIMIT 200;

Or if you have the POLYGON coordinates of each country, you could use that instead of a maximum radius.