MySQL: simple geo/spacial query

Question

I have been researching how best to retrieve subsets of location data from a MySQL database. This answer had links to many hope-inducing articles, but none of which provided any concrete, simple examples:

Database: Best performance way to query geo location data?

Let's say I have a database with a table PLACES. PLACES could have millions of entries in it. Each row has column types LAT, LON, and NAME.

I would like to form a functional statement from the following semi-pseudocode:

Select * from PLACES where distance((LAT, LON), (givenLat, givenLon)) < 100 meters;
return *[NAME];

It's fairly simple, but though I've used MySQL extensively I have never used the spacial extensions before. I am willing to work with them but I am also willing to write custom functions if that would be faster. The above query is literally what I need to produce. Any pointers beyond the docs would be very helpful, thank you.

here http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude and here http://www.movable-type.co.uk/scripts/latlong-db.html and here http://www.marketingtechblog.com/calculate-distance/ and here http://zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/ — Sergey Benner, Feb 02 '12 at 00:49
Thank you! Those were all great resources - I guess I should have kept at it a bit longer; I didn't have all the right search vocabulary yet. I will provide an answer which summarizes your input when I am permitted by the system. — SG1, Feb 02 '12 at 01:11

score 0 · Answer 1 · answered Feb 05 '12 at 09:02

Although I do not master mysql geospatial extentions, I would suggest you to read this presentation http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL , for it contains an interesting proposal of geo distance function that I have been using quite satisfactorily.

SELECT destination.*,
3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat - dest.lat) *  pi()/180 / 2), 2) +COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) * POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) ))
asdistanceFROM users destination, users originWHERE origin.id=userid
and destination.longitudebetween lon1 and lon2 and destination.latitudebetween lat1 and lat2

score 0 · Accepted Answer · answered Feb 26 '12 at 17:47

Sergey Benner deservers the credit for this answer (I just want to close the question). His comment above links to several outstanding and functional resources on the topic. I also discovered this resource which works perfectly if you are working on iOS:

http://www.thismuchiknow.co.uk/?p=71

MySQL: simple geo/spacial query

2 Answers2