MySQL通过中心点和半径算,根据半径从地图数据库中选择点

灰太狼 2023-01-24 04:20 40阅读 0赞

![Image 1][]

I have a database which has latitude/longitude of points. If I want to select all points within a specific range centered in a specific point it works fine BUT if there is any point located at this center, it will not get selected!

I use this query:

SELECT \*, ( 6371 \* acos( cos( radians(-27.5796498) ) \* cos( radians( latitude ) ) \* cos( radians( longitude ) - radians(-48.543221) ) + sin( radians(-27.5796498) ) \* sin( radians( latitude ) ) ) ) AS distance FROM map HAVING distancia <= 2

In the case above the radius is "2" and the center of the map is at \[-27.5796498,-27.5796498\]. This query works really fine BUT if some point is located at this very exact center, it will not get selected. Why?

EDIT: I discovered that the formula above returns a good value for all the points BUT to the point located at the center MYSQL returns the value NULL to the column "distance"! How do the professionals deal with this kind or problem of using SQL to select points within a range including the center point?

EDIT2: I could create another query to select all the points located at the very center of the radius, but that's not efficient, maybe some math wizard could come up with a better formula.

解决方案

Sometimes the parameter to ACOS() can be just slightly greater than 1 -- slightly outside the domain of that function -- when distances are small. There's a better distance formula available, due to Vincenty. It uses the ATAN2(y,x) function rather than the ACOS() function and so is more numerically stable.

This is it.

DEGREES(

ATAN2(

SQRT(

POW(COS(RADIANS(lat2))\*SIN(RADIANS(lon2-lon1)),2) +

POW(COS(RADIANS(lat1))\*SIN(RADIANS(lat2)) -

(SIN(RADIANS(lat1))\*COS(RADIANS(lat2)) \*

COS(RADIANS(lon2-lon1))) ,2)),

SIN(RADIANS(lat1))\*SIN(RADIANS(lat2)) +

COS(RADIANS(lat1))\*COS(RADIANS(lat2))\*COS(RADIANS(lon2-lon1))))

There's a more complete writeup, including a stored-function definition for MySQL, here.

Another solution is to use ISNULL(ACOS(formula), 0.0)