Hello,
I'd like to sort my query results based on their distance from a given
point. The actual data I have will be in (longitude,latitude) format,
but I can convert to something else if that will work better.
For example, I may have data like this
Item Latitude Longitude
---- -------- ---------
Scott's House 37.4419 -122.1419
Tom's House 37.4519 -122.2419
Mary's House 37.4619 -122.3419
Sally's House 37.4719 -122.4419
and I'd like to see these rows sorted by distance from (38,-121).
My actual data has many more columns (about 30) and rows (about
25,000), and joins in a few other tables. Most queries will have a
LIMIT clause with 10 results, possibly starting a few hundred rows in
(LIMIT 240,10). Currently all searches take much less than 1 second,
and I'd like to keep it that way.
Is there a way to have MySQL do this query efficiently? I know how to
do the calculations, but MySQL has to calculate the Great Circle
distance to this point for each row in the table, which is slow for
25,000 rows.
I tried using the GIS functions, but in the version of MySQL I have
(4.1.7 on Debian Linux) the Distance() function isn't implemented, so
that doesn't help much. I'm willing to look at upgrading MySQL if
that will help.
My database friends tell me I want to use an RTREE index, but I
haven't yet found a version of MySQL that implements those yet except
with the GIS functions.
Thanks for any advice, help, or hints!
----ScottG.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
