|
|
Posted by Pacific Fox on 11/16/06 03:13
Hi Steve,
I will have a go at this.
Steve Kass wrote:
> 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]
|