Scott Gifford wrote:
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).
So, some one else is doing GIS appliations, huh...
Like I was saying earlier, unfortunately, even if you did have the
distance function, it would not work, because the results are calculated
on a planar surface. The SRIDs in the GIS functions are there, but
pretty much useless.
And, here you go:
SELECT(
DEGREES(
ACOS(
SIN(RADIANS( latitue1 )) * SIN(RADIANS( latitue2 ))
+ COS(RADIANS( latitue1 )) * COS(RADIANS( latitue2 ))
* COS(RADIANS( longitude1 - longitude2 ))
) * 60 * 1.1515
)
) AS distance
Just sort that by distance.
--
http://www.douglassdavis.com
