SQL Server Spatial - Distance Test

This is just a quick post about using spatial data in business reporting.

Quite often distance to site is a very important element when client is running some types of appointment campaign or service campaign. Below is a short script can be used to do the distance test:

 declare @lat float = 145.41, @lng float = -38.02  
 declare @geo geography   
 select @geo = geography::STGeomFromText('point(' + Cast(@lat as varchar(20)) + ' ' + cast(@lng as varchar(20)) + ')', 4326)  
 select top 10 geom.MakeValid() as Geom  
      , geography::STGeomFromText(geom.MakeValid().AsTextZM(), 4326).STDistance(@geo) as Distance  
      , POA_NAME as Postcode  
 from POA  
 order by 2   

Here is some explanation:

Firstly, POA is a table from Australia Bureau of Statistics (ABS). You can find a full explanation at
http://www.abs.gov.au/ausstats/abs@.nsf/mf/1259.0.30.001?OpenDocument. To get shape file imported into the database, you can use Shape2Sql, which you can find at http://www.sharpgis.net/page/shape2sql

Secondly, we need to know latitude and longitude of a particular place, for instance, a business site or a clinic. I found this site is quite helpful, as it allows you to do batch geocode: http://www.findlatitudeandlongitude.com/batch-geocode/

Thirdly, you may find Paf Postcode file from Australia Post is quite helpful. But unfortunately starts from 2014, this resource is not free to individual user anymore, though they provides a pdf formated postcode file.

So after you get your resources sorted out, running above query will give you some results such as top 10 postcodes to a business site, and with some extra works in SSRS and T-Sql, we can make the result more clear to the end user.