MySQL get closest value

Maxpv

Hi guys,

I have a database with placenames and the latitude and longitude, heres an example:

ID Placename lat long
1 Galway 53.4 -9.8
2 Dublin 53.9 -6.9

What I want to do is find out the closest place to a certain coordinate, like this:

Select * from tbl where lat is closest to 53.6 and long is closest to -8.9.

How would I go about doing this?

Thanks for helping!

Ross

I haven't mucked about with any of this stuff, but I have a feeling you're in for some fun. I'd say these will point you in the right direction:

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html

http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude
http://stackoverflow.com/questions/2695365/geolocation-sql-query-not-finding-exact-location
http://stackoverflow.com/questions/1813460/database-best-performance-way-to-query-geo-location-data

Freddio

you need to use pythagoras' therom

oeb

Freddio wrote: »

you need to use pythagoras' therom

I don't believe so.

Latitude and longitude lines are not an equal distance apart. Something like Haversine is more appropriate.


http://www.codecodex.com/wiki/Calculate_distance_between_two_points_on_a_globe

Freddio

I stand corrected

oeb

It's actually pretty straight forward when it all comes down to it. I can't remember the exact formula that I used the last time I had to do this (It was a contract for a pharmaceutical company, and I had to calculate all the clinics within x miles from a typed town).

It's a relatively complex formula, but once you have it, that's 90% of the work

I think I have the code on my computer at home, I'll take a look this evening when I get home.

ressem

Not an expert but there's different methods depending on whether you have MySql spatial extensions installed, whether you can convert the co-ordinates to a column of point type, how accurate you need to be.

There's a presentation from mysql on the basics at
http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

komodosp

Well for starters to get the closest value it's

SELECT * FROM tbl WHERE ABS(lat - 53.6) = (SELECT MIN(ABS(lat - 53.6)) FROM tbl)

As for getting the closest point in two dimensions you might need a mathemagician to help you there...

At a guess (and let's pretend you use Pythagoras theorem which oeb already said you don't... but changing the formula as appropriate)

SELECT * FROM tbl WHERE SQRT(POW(lat - 53.6, 2) + POW(long - 8.9, 2)) = (SELECT MIN(SQRT(POW(lat - 53.6, 2) + POW(long - 8.9, 2))) FROM tbl