List:General Discussion« Previous MessageNext Message »
From: douglass_davis Date:August 25 2005 9:28am
Subject:Re: ORDER BY distance from a point
View as plain text  

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


Thread
ORDER BY distance from a pointScott Gifford24 Aug
  • Re: ORDER BY distance from a pointSGreen24 Aug
  • Re: ORDER BY distance from a pointdouglass_davis25 Aug