SQL to calculate surrounding suburbs

[Pacific Fox]

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.

[matthewf_boi@yahoo.com]

Re: SQL to calculate surrounding suburbs

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

[Steve Kass]

Re: SQL to calculate surrounding suburbs

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(@Fr omLat))
* Sin(Radians(@To Lat))
+ Cos(Radians(@Fr omLat))
* Cos(Radians(@To Lat))
* Cos(Radians(@To Long)-Radians(@FromLo ng))

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(@la t, @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(@la t, @lon, pc_lat, pc_lon) <= @neighbordistan ce
) 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 @neighbordistan ce
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.
>

[--CELKO--]

Re: SQL to calculate surrounding suburbs

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

[Pacific Fox]

Re: SQL to calculate surrounding suburbs

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

CREATE PROCEDURE [dbo].[spCalculateSurr ounding] (
@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)

[Pacific Fox]

Re: SQL to calculate surrounding suburbs

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(@Fr omLat))
* Sin(Radians(@To Lat))
+ Cos(Radians(@Fr omLat))
* Cos(Radians(@To Lat))
* Cos(Radians(@To Long)-Radians(@FromLo ng))
>
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(@la t, @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(@la t, @lon, pc_lat, pc_lon) <= @neighbordistan ce
) 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 @neighbordistan ce
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.

[--CELKO--]

Re: SQL to calculate surrounding suburbs

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

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