473,230 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

SQL to calculate surrounding suburbs

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.

Nov 14 '06 #1
6 3808
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

Nov 15 '06 #2
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.
Nov 15 '06 #3
>>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.

Nov 15 '06 #4
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)

Nov 16 '06 #5
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 - <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.
Nov 16 '06 #6
>PS. if that is Joe Celko, Joe you rock! (read your book) <<

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

Nov 16 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Phil Powell | last post by:
Relevancy scores are normally defined by a MySQL query on a table that has a fulltext index. The rules for relevancy scoring will exclude certain words due to their being too short (minimum...
1
by: Piet | last post by:
Hello, I am still struggling with a dynamic dialog box. I have created a dialog box that will show a variable number of controls depending on a selection made in a ComboBox also present in the...
1
by: Building Blocks | last post by:
Hi, All I need is a simle calculate form script which contains this: A script that can handle text input, radio buttons, checkboxes, and dropdowns. Each one of these variables will contain a...
4
by: Jan Szymczuk | last post by:
I'm creating an MS Access 2000 database where I have a number of people entered using simple basic fields, Surname: SMITH Forenames: John DoB: 09/09/1958 Age:...
2
by: dennis.sprengers | last post by:
Ik ben bezig met een eigen UBB editor. Als iemand aan het typen is, zorgt CTRL-B voor een \-tag en nogmaals CTRL-B voor een \ tag. Als je eerst een selectie maakt en dan CTRL-B drukt, wordt de...
3
by: Libber39 | last post by:
Hi everyone, Have a query on how to calculate the amount of weeks and days contained in a number in an access query. ie: the difference in days between 2 dates amounts to 17 days. I want to now...
1
by: whnkee | last post by:
I need to audit the freight bills charged by freight company which has over 20,000 consignment per week. The basic charge is base on distance and weight, something like this: SYD-MEL <1kg $6...
0
by: SuzK | last post by:
I am trying to calculate in VBA in Access 2002 a moving average and update a table with the calculations. Fields in my WeeklyData table are Week Ending (date) ItemNbr (double) Sales Dollars...
3
by: ozboss | last post by:
Hello, Being new to ASP .net and visual studio express 2008 I am not sure how to create a webform that allows the user to select a suburb from a drop down list to search an Access 2007 database of...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.