443,760 Members | 1,630 Online Need help? Post your question and get tips & solutions from a community of 443,760 IT Pros & Developers. It's quick & easy.

# SQL to calculate surrounding suburbs

 P: n/a 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 Replies

 P: n/a 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

 P: n/a 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 - and pc_lon between @lon - and uf_Distance(@lat, @lon, pc_lat, pc_lon) <= @neighbordistance ) as T You'll have to create functions or precalculate the 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

 P: n/a >>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

 P: n/a 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

 P: n/a 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 - and pc_lon between @lon - and uf_Distance(@lat, @lon, pc_lat, pc_lon) <= @neighbordistance ) as T You'll have to create functions or precalculate the 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

 P: n/a >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 discussion thread is closed

Replies have been disabled for this discussion. 