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