Sunday, September 27, 2009

Using OGC functions in SQL Server 2008 part 2

In part 1 I created a table that contained some spatial UK postcode data. Now the next task is to work out how to query this data. A typical question we may want to answer is ‘what are the nearest postcodes to a particular postcode?’. OK, what we probably want to answer is ‘what is the nearest station/cash machine/post office etc to a particular postcode?’ but the only spatial data I’ve got is for postcodes, so we’ll use that. And the query to answer this question is pretty straightforward.

SELECT TOP 100 P.Postcode, P.Location.STDistance(PL.Location) AS Distance 
FROM Postcodes P, Postcodes PL
WHERE PL.Postcode='KT1 3EG'
ORDER BY Distance

The MSDN documentation for the STDistance function is a bit sparse but from what I can find from other sources, it will return the distance between two locations in metres.

Obviously this has all been possible in the past by writing your own code to do the calculation, but executing it directly against the database makes life a lot simpler. I have no idea if it will be quicker but simpler generally wins out for me.

No comments: