Mysql find 10 nearest point from given lat/long per type

MySQLnearest neighbor

I have a database given with 3 different types of locations A, B and C. These points have latitude and longitude information with them.

Now I have a random lat-long given and I want to find the closest 10 points per type to that given location – how can I do that? Anyone has an idea?`

Thanks for any help

Best Answer

Searching for the nearest 10 points of interest gives, usually, near points. In that case the usual cartesian distance (SQRT((LAT1 - LAT2)^2 + (LONG1 - LONG2)^2)) can be used.

Given a generic table (for the definition of latitude and longitude I've used this pointer.)

CREATE TABLE POI (
   ID INT IDENTITY(1, 1)
 , `Name` Varchar(100)
 , `Type` CHAR(1)
 , `LAT`  Decimal(10, 8)
 , `LNG`  Decimal(11, 8)
)

The query would be something like

DECLARE myLAT Decimal(10, 8) DEFAULT 45.48561;
DECLARE myLNG Decimal(11, 8) DEFAULT 123.47981;

(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'A'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)
UNION ALL
(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'B'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)
UNION ALL
(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'C'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)

In case the point of interest are few and far from the 'here' point and from themselves you have to use a different formula for the distance, to account that the earth is, more or less, a sphere.

Related Question