|
|
Posted by Pacific Fox on 11/16/06 03:11
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)
Navigation:
[Reply to this message]
|