SEO

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.

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.

is this Oracle or MSSQL? Oh well, it probably doesn't matter.

Try this:
select myzip as varchar(32) from centroids
join long_lat on zip_code = coordinate
where distance < .75km
and postcode in (4101, 4106, 4000, 2580 4169)

I am pretty sure that should take care of it for you.
Regards

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.
>

>>Does anyone know the SQL statement for calculating surrounding suburbs, or can point me in the right direction? <<

I do not know the Australian postal code system, but in the US, we can
get tables of Zones for all the ZIP code (our postal code system).
Zips in Zone 1 are closest, and Zone 9 is the furhterest away The Zones
are used for computing shipping charge. I assume that you have
something like that.

I've been able to get the following going, although I don't know
whether it provides the best performance?

CREATE PROCEDURE [dbo].[spCalculateSurrounding] (
@latitude REAL = NULL
, @longitude REAL = NULL
, @postalCode CHAR( 4 ) = NULL
, @radius INT = 0
) AS

DECLARE @_latitude REAL;
DECLARE @_longitude REAL;
IF ( NOT @latitude IS NULL AND NOT @longitude IS NULL ) BEGIN
SET @_latitude = @latitude;
SET @_longitude = @longitude;
END
ELSE IF ( NOT @postalCode IS NULL AND LEN( @postalCode ) = 4 ) BEGIN
SELECT @_latitude = latitude
, @_longitude = longitude
FROM dbo.postalcode_centroid
WHERE ( postalCode = @postalCode )
END
ELSE IF ( @latitude IS NULL AND @longitude IS NULL AND ( @postalCode IS
NULL OR LEN( @postalCode ) <> 4 ) ) BEGIN
RETURN
END
ELSE BEGIN
RETURN
END

SELECT suburb, postalCode
FROM dbo.postalcode_centroid
WHERE ROUND( ( ACOS( ( SIN( @_latitude / 57.2958 ) * SIN( latitude /
57.2958 ) ) +
( COS ( @_latitude / 57.2958 ) * COS( latitude / 57.2958 ) * COS(
longitude/57.2958 - @_longitude / 57.2958 ) ) ) ) * 6378.135, 3 ) <=
@radius
GO

This basically gets the long/lat for the postal code in question and
then gets surrounding suburbs, however, it would be nice if I could
also get the distance for each record from the postal code in question.
Really, the postal code is of no importance here, its just used to make
it user friendly getting the long/lat (wouldn't want users to figure
out what their long/lat is).

Thanks guys.

PS. if that is Joe Celko, Joe you rock! (read your book)

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.
> >

>> PS. if that is Joe Celko, Joe you rock! (read your book) <<

Thanks! And I have seven SQL books Collect the complete set!