Re: Finding closest points to a given point

OfficeOfTheLaw wrote:
> The Client only had mysql 3.x (crap, crap, crap) so I had to resort to
> other methods. After a quick search here I found this query and it did
> the trick pretty darned good!
>
> SELECT *,6371.04 * acos(cos(pi()/2-radians(90-lat)) *
> cos(pi()/2-radians(90-'$lat')) * cos(radians(`long`)-radians('$long'))
> + sin(pi()/2-radians(90-lat)) * sin(pi()/2-radians(90-'$lat'))) AS
> dist
> FROM geo_addresses
> ORDER BY dist
> LIMIT $limit

The advantage of the MySQL 5 Spatial Geometry is not just the included
distance function, it is the fact that you can create a SPATIAL INDEX
of your points, which uses a quadtree/rtree system to partition the
space the points are in so that it can search more optimally instead of
running through every point.
This could be possibly done yourself in MySQL 3.x if you wanted to
create a seperate table that managed the partitioning of your space
(including a reference to the points in your geo_addresses table) so
when you searched you might only need to check the partitions
containing and around the point you are figuring your distance from.
There would obviously be more overhead associated with using another
table when you add points, and unless you expect to have a large amount
of points in your table, not necessarily a significant saving. Of
course if you intend to find all points within a certain distance of
each other or something like that (for collecting points together when
the map is zoomed out for instance) this method might have significant
advantages.

However as opposed to the MySQL Spatial Geometry package, I must
applaud any distance function which takes into consideration that these
are coordinates on a 'sphere' (and so should not be upset by crossing
the dateline and to give a more meaniful number as distance).
Nice work.