CODEWITHSUNDEEP
mysqldistance
user3439871
user3439871

Reputation:

How to get nearest coordinates from database in mysql?

I have got a table with id,latitude (lat),longitude (lng),altitude (alt). I have some coordinates and I would like to find the closest entry in the DB. I used this but not yet working correctly:

SELECT lat,ABS(lat - TestCordLat), lng, ABS(lng - TestCordLng), alt AS distance
FROM dhm200
ORDER BY distance
LIMIT 6

I have a table with the 6 nearest points displaying me the lattitude, longtitude and altitude.

Upvotes: 2

Views: 1189

Answers (2)

Mazin Luriahk
Mazin Luriahk

Reputation: 878

Query to get nearest distance in kilometer (km) from mysql:

SELECT id, latitude, longitude, SQRT( POW(69.1 * (latitude - 4.66455174) , 2) + POW(69.1 * (-74.07867091 - longitude) * COS(latitude / 57.3) , 2)) AS distance FROM ranks ORDER BY distance ASC;

You may wish to limit radius by HAVING syntax.

... AS distance FROM ranks HAVING distance < '150' ORDER BY distance ASC;

Example:

mysql> describe ranks;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| latitude   | decimal(10,8) | YES  | MUL | NULL    |                |
| longitude  | decimal(11,8) | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT id, latitude, longitude, SQRT( POW(69.1 * (latitude - 4.66455174) , 2) + POW(69.1 * (-74.07867091 - longitude) * COS(latitude / 57.3) , 2)) AS distance FROM ranks ORDER BY distance ASC;
+----+-------------+--------------+--------------------+
| id | latitude    | longitude    | distance           |
+----+-------------+--------------+--------------------+
|  4 |  4.66455174 | -74.07867091 |                  0 |
| 10 |  4.13510880 | -73.63690401 |  47.59647003096195 |
| 11 |  6.55526689 | -73.13373892 | 145.86590936973073 |
|  5 |  6.24478548 | -75.57050110 | 149.74731096011348 |
|  7 |  7.06125013 | -73.84928550 | 166.35723903407165 |
|  9 |  3.48835279 | -76.51532198 | 186.68173882319724 |
|  8 |  7.88475514 | -72.49432589 | 247.53456848808233 |
|  1 | 60.00001000 | 101.00001000 |  7156.836171031409 |
|  3 | 60.00001000 | 101.00001000 |  7156.836171031409 |
+----+-------------+--------------+--------------------+
9 rows in set (0.00 sec)

Upvotes: 2

Lajos Arpad
Lajos Arpad

Reputation: 76424

You will need to use the Haversine formula to calculate distances taking into account the latitude and longitude:

dlon = lon2 - lon1 
 dlat = lat2 - lat1 
 a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2 
 c = 2 * atan2( sqrt(a), sqrt(1-a) ) 
 distance = R * c (where R is the radius of the Earth)

However, the altitude raises the difficulty of the problem. If between point A and point B, having different altitudes the road contains a lot of high altitude differences, then assuming that the altitude's line's derivative between the two points is unchanged might be misleading, not taking that into account at all might be very misleading. Compare the distance between a point in China and a point in India, having the Himalaja in between with the distance between two points on the surface of the Pacific ocean. A possibility would be to vary R to be the average of the altitudes for each comparisons, but in case of large distances this could be misleading, as discussed earlier.

Upvotes: 0

Related Questions