

Calculate Latitude/Longitude Distances in MySQL with the Haversine Function - heyjonboy
http://tumblr.jonthornton.com/post/1419487206/calculate-latitude-longitude-distances-in-mysql-with

======
thibaut_barrere
Anecdote on Haversine: in 1.6, MongoDB's built-in "geonear" was using non-
spherical indexing, but I needed to get more accurate results in a project, so
I queried 3 times more records and used Haversine afterward to sort records
again, client-side. Here's the ruby code:

<http://gist.github.com/559482>

Since then, MongoDB 1.7 has been released with spherical sort support
([http://www.mongodb.org/display/DOCS/Geospatial+Indexing#Geos...](http://www.mongodb.org/display/DOCS/Geospatial+Indexing#GeospatialIndexing-
TheEarthisRoundbutMapsareFlat))

------
bravo_sierra
Why in the world would you use this when MySQL has almost proper geospatial
support? It's far more efficient and less incorrect.

I swear - next time I'm going to get a R-Tree novelty account.

~~~
bad_user
Because Mysql doesn't have support for distance searches.

~~~
bravo_sierra
Using the Buffer function it does. Then MySQL will utilize the geometry index,
rather than scanning the table. Not clear in the docs, but it's there.

~~~
bad_user
Do you have an example?

~~~
thibaut_barrere
I'm interested as well (really, as someone who uses sphinx for that
currently).

~~~
bad_user
Hey, I'm also using Sphinx :)

Also, thing is filtering is not much of a problem as you can use the Haversine
formula ... it gets to be a problem if you also want sorting from closest to
furthest.

------
akharris
One of those things you never think about when reading a map or driving in a
car, but it's the explanation behind why you fly all the way up past nova
scotia when on a plane to europe.

------
philfreo
Sphinx (<http://sphinxsearch.com/>) is really good at geo/spatial searches on
MySQL data

Example: [http://www.god-object.com/2009/10/20/geospatial-search-
using...](http://www.god-object.com/2009/10/20/geospatial-search-using-sphinx-
search-and-php/)

~~~
thibaut_barrere
Seconded; I've been using it with the thinking_sphinx rubygems and it always
worked great.

------
wazoox
<rant>I've got a better idea: use postgis and get rid of mysql and its quirks.

Yep, I just reinstalled an old app yesterday, and for some unfathomable reason
it loses communication with mysql at some point (it worked perfectly for what,
6 years?). Gosh, I hate mysql.</rant>

~~~
thibaut_barrere
Honest question: if it's unfathomable, what makes you think it's caused by
mysql, rather than some system update, driver or app dependency issue ?

~~~
wazoox
It's a perl program running on Debian stable. Apparently it fails to
communicate properly with mysql 5, but works fine with mysql 4. I assume the
Debian dbd-mysql package to be compatible with the Debian mysql 5 server, so
of course it could be Debian fault, or some error in the program. But I prefer
to unjustly incriminate mysql because postgres is so much better anyway :)

------
ljegou
Beware, coordinates are not always expressed in the same referencial (geoid,
ellipsoid, etc.).

------
lzimm
wont that completely negate the value of the indices that lat/lon may/may not
sit on and result in a complete tablescan?

~~~
zachster
I've got this on a site I'm building that offers a sort by distance option.
There's definitely a performance hit. I'm going to look into 'dumber' ways of
filtering out the data prior to running this function. Maybe start by
including state in the where clause, for example.

Any other ideas?

One solution I saw used more simple arithmetic to calculate a range of
coordinates within levels of distance. That could be pre-cached, but it's a
lot less accurate.

~~~
nl
Geohash: <http://en.wikipedia.org/wiki/Geohash>

(Edit: to be more specific, you can get a pretty good distance measurement
using Geohash and comparing strings. Obviously, indexing strings is something
databases do well. The exact distance a single character corresponds to
depends on longitude & latitude, but there are lookup tables for that. There
are also edge conditions to be aware of which may affect your application)

Or, use Postgres which has geospatial indexes.

~~~
zachster
Thanks for the pointer! This looks interesting. The edge conditions seem like
they might pose a problem. I'll have to check out how often it would occur.
Maybe the geospatial indexes are a better bet. It looks like MongoDB supports
them also. Good excuse to try that out.

~~~
bravo_sierra
The edge cases happen all the time.

Using a B-Tree on a Geohash (like MongoDB does) is a bit more efficient that
just indexing min/max values, but not by much. MySQL, PostgreSQL and even
SQLite have R-Tree indices that perform 10x better.

