Techie Head

Getting a really good spatial index on a geography column took a lot more effort and reading than I'd ever have dreamed of! It's really hard to find good resources on them so I thought I'd type up a few of the things I've learnt over the last few days trying to get some query times down. Our queries ended up running in about 1 tenth the time as they did with the first attempt at a spatial index. However, even that first index sped the queries up by about 10x. Which leads me to my first point:  Any spatial index is always better than not having one. Hang on Rob, what queries?!  Good question! In the database we have a few thousand administrative boundaries that we want to ask questions about, for the sake of this post (and of tuning the index) our main question is: What boundaries is a point p in? In SQL this looks like: DECLARE @ p geography SET @ p = geography::Point( - 35 , 139 , 4326 ) SELECT BoundaryName FROM dbo.Boundaries WHERE data .STInter...

In a project we needed a set of boundaries in order to answer domain questions such as:   Which services that we know about can provide activity a at point p? The services we were interested in happened to coincide with the administrative boundaries of a country. For example, states and towns. I might blog about how we linked the services in our database to their boundaries later but the first challenge was getting some spatial data into a database to start messing around with. The first tool I discovered was  http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx . It looked like it would do everything I needed... However Australia and the UK provide their data in different coordinate systems. Data for the UK is available through the Ordinance Survey and uses the British Grid System funnily enough, Australia doesn't use the  British  grid! I decided to use the excellent  dotspatial  to spin up my own quick and dirty importer that wo...