[cfaussie] [OT] Calculate distance with long/lat

[Taco Fleur]

I am able to retrieve surrounding suburbs with the following SQL statement, but have no idea how to retrieve the distance in that same SQL statement, does anyone know?

 

DECLARE @latitude REAL
DECLARE @longitude REAL
DECLARE @postalCode CHAR( 4 )
DECLARE @radius INT

SET @postalCode = '4037'
SET @radius = 4

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

Thanks in advance

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups "cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en
-~----------~----~----~----~------~----~------~--~---