May 31, 2012

Spatiality Part 4, Fiddling with SQL Server Spatial SQL

SQL Server is quite potent when it comes to spatial queries. It follows the Open Geospatial Consortium Simple Features for SQL, Version 1.1 and implements about 70 functions covering areas such as intersects, unions and the like. Basic GIS-stuff.

It is a bit different syntax from what most people are used to, but once you get past that it's a breeze.

SQL Server 2008 supports two kinds of spatial data.

Geometry, that means flat.
Geography, that means spherical.
I still believe that the world is spherical even though I read a lot of hard evidence suggesting otherwise here. 


So I would choose the geography style. However, all my data is flat so...


In my spatial database I have lots and lots of areas. And it would be nice to get all areas within say 20 km from my position. (I can't believe some third world countries still hang on to miles)


You can probably do this the hard way and use a lot of math to get an answer, or you can do it the GIS way.


GIS (Geographical Information Systems) is a neat science. One of its strengths is to get new information by making calculations on thematic layers on a map. For me it started with reading the book Map Algebra by Dana Tomlin and I was hooked. If you are interested read more here.


Spatial SQL is a bit different but you can achieve a lot by using similar techniques.

Back to the task.


My plan is as follows:
  1. Create a geographical area covering a 20 km circle with it's center in the middle of stockholm.
  2. Use that circle to overlay my data and by SQL return all the objects that intersects my buffer.
  3. Show it in my blog.
Let's start with number one in the list.

DECLARE @stockholm geometry = geometry::STPointFromText('POINT (18.06861 59.32944)', 4326)

So... what do we do here? We (or actually I) create a point geometry by using the geometry::STPointFromText function. As parameter we have the coordinates as a string followed by the mystical 4326 which simply points out that we are using the reference system WGS84. The same reference system as my own data.

This will give us a one dimensional point.


To make it into a circle we use:

DECLARE @stockholmbuffer geometry = @stockholm.STBuffer(0.2)

We simply tell SQL Server to make a buffer around our point with the distance 0.2. 

The distance on my map is in degrees. Lats and longs. 

And a degree is around 110 km and 20% of that is just close enough 20 km to make me happy. 
I'm not picky.

So now we have declared a 20ish km radius circle with its center in Stockholm.

Next step is to overlay it with the existing data.

This is how we do it:


SELECT *
FROM areas
WHERE ogr_geometry.STIntersects(@stockholmbuffer) = 1

Just a simple call to the intersect function that returns 1 on an overlap.


So to summarize:


DECLARE @stockholm geometry = geometry::STPointFromText('POINT (18.06861 59.32944)', 4326)


DECLARE @stockholmbuffer geometry = @stockholm.STBuffer(0.2)


SELECT *
FROM areas
WHERE ogr_geometry.STIntersects(@stockholmbuffer) = 1

And the result is this:






No comments:

Post a Comment