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.