|
|
Posted by Steve Kass on 11/15/06 05:20
Pacific,
Here is a function to calculate distance between two lat/lon
pairs (in kilometers, I think). I probably didn't test it
for the Southern Hemisphere, so test to be sure it works.
create function uf_Distance (
@FromLat float, @FromLong float, @ToLat float, @ToLong float
) returns float as begin
declare @X float
SET @X =
Sin(Radians(@FromLat))
* Sin(Radians(@ToLat))
+ Cos(Radians(@FromLat))
* Cos(Radians(@ToLat))
* Cos(Radians(@ToLong)-Radians(@FromLong))
SET @X = Acos(@X)
RETURN 1.852 * 60.0 * Degrees(@X)
end
go
select dbo.uf_Distance (41.63,-87.73,41.7,-88.07)
go
Using this to find all codes within a given distance could
be time-consuming if you have thousands of codes. One way
to reduce the number of codes to look at is to query like
this, which selects codes within a square box (which can
be optimized) around the given code, and also within a
circle (which can't be optimized).
select
PostalCode, distance
from (
select
PostalCode,
uf_Distance(@lat, @lon, pc_lat, pc_lon)
from PostalCodes as PC
where pc_lat between @lat - <something> and @lat + <something>
and pc_lon between @lon - <something> and @lon + <something>
and uf_Distance(@lat, @lon, pc_lat, pc_lon) <= @neighbordistance
) as T
You'll have to create functions or precalculate the <something>s
separately for latitude and longitude - these should be the
N/S and E/W separations that would alone be @neighbordistance
apart. Also watch out for the longitude one if you are near
the international date line.
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Pacific Fox wrote:
> Hello all,
>
> Does anyone know the SQL statement for calculating surrounding suburbs,
> or can point me in the right direction?
> I have a database of Australian postal codes and their centroids in
> longitude and latitude, I'd like to pass it either the long/lat or
> postal code to calculate from. And preferably return distance as well,
> in KM..
>
> Thanks in advance.
>
Navigation:
[Reply to this message]
|