Recently I found a wrapper around the google geocode API here. Which got me thinking about map coordinates of Australian suburbs and being able to do distance from or surrounding suburbs searches for starters.
One thing I found with using this wrapper is that I had to go to google and register for access to use the API (and I was issued a key to use with the wrapper). To register for the key I had to enter the URL that would be making calls to the Google API but I found that the calls worked either from a win forms app or an ASP.NET website both running locally (and in debug mode).
I then found a site that publish a CSV file (amoungst others) with all Australia post codes and their lats and longs here.
The only thing left was to find a routine that calculated the distance between 2 lat and longs. I have attached the SQL CLR user defined function source file to the bottom of this article.
The only thing left was to create a SQL function that returned the results. Simply pass in the postode you are starting from and the distance you want to search from the postode. I group by postcode and get the max distance because in most instances there postcodes that cover multiple suburbs. This function retrieves the "distinct" list of souurounding suburbs.
I'm sure there can be improvements in my implementation.
CREATE FUNCTION [dbo].[SurroundingSuburbs]
(
@Postcode int,
@Distance float
)
RETURNS TABLE
AS
RETURN
(
SELECT
P2.Postcode,
MAX(dbo.GeoDistance(P1.Latitude,P1.Longitude,P2.Latitude,P2.Longitude, 'K')) AS 'Distance'
FROM
Postcode P1, Postcode P2
WHERE
P1.Postcode = @Postcode
AND P1.Latitude IS NOT NULL
AND P2.Latitude IS NOT NULL
AND dbo.GeoDistance(P1.Latitude,P1.Longitude,P2.Latitude,P2.Longitude, 'K') < @Distance
GROUP BY
P2.Postcode
)
GeoDistance.cs (1.17 kb)
Subscribe