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 - <somethingand @lat + <something>

and pc_lon between @lon - <somethingand @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.