Advanced spatial data querying
December 1, 2012 Leave a comment
In this post (part 2 of this series), I’ll try to explain the power of spatial data with real-life polygons.
First of all, we need some data. There are several sources of spatial data, and I’ve described importing this data in a previous post. For this post, I’ll be using the Netherlands as an example dataset. You can also use the queries below on your own set of spatial data.
If you start working with spatial data, there are several things to keep in mind. One of these things is that spatial data consists of layers. Every set of objects you select (or draw yourself) is turned into a layer. Layers are stackable and can be joined together (with a UNION operator).
After importing the shape file, we start with a simple selection. In this case the city I love the most in the Netherlands: Rotterdam. This beautiful port-city in the province of South-Holland has a very recognizable shape, and is excellent for the upcoming examples.
Selecting the polygon of Rotterdam can be done with this query:
SELECT GEOG FROM NLD_adm2 WHERE NAME_2 = 'Rotterdam';
Every spatial object (Geography and Geometry) has extended methods you can use. These methods vary from very easy (draw a buffer around my object), to calculate the closest distance to another object.
Starting with a very easy one: adding a buffer to a spatial object. You can do this by using the STBuffer() method. The distance you can add as an argument, is based on the reference system of the shape file you imported. In this case we’re working with the EPSG system (SRID 4326), and the unit it used is meter:
SELECT * FROM sys.spatial_reference_systems WHERE spatial_reference_id = 4326
So, if you like to draw a buffer of 1 kilometer (1000 meters, or 0.62 miles/ 1091.2 yards for my American friends) around Rotterdam, we use the following query:
SELECT GEOG FROM NLD_adm2 WHERE NAME_2 = 'Rotterdam' UNION ALL SELECT GEOG.STBuffer(1000) FROM NLD_adm2 WHERE NAME_2 = 'Rotterdam';
As you can see, I selected the city of Rotterdam as a separate layer, with the “buffered layer” on top of it. I did this for visual purposes, so you can actually see what is happening to your object.
Get surrounding cities
One of the advantages of spatial data, is that polygons are actually connected to each other. So looking for cities around Rotterdam is pretty easy. What I did is draw a buffer of 1 meter around my city, and ask what intersects my buffer. This query actually works without the buffer, but again, I added it for visual reasons.
DECLARE @City geography; SET @City = (SELECT GEOG FROM NLD_adm2 WHERE NAME_2 = 'Rotterdam'); SELECT GEOG FROM NLD_adm2 WHERE GEOG.STIntersects(@City.STBuffer(1)) = 1;
Sometimes you want to create your own polygon by aggregating some existing polygons. This can be accomplished by using the method UnionAggregate(). This method aggregates all polygons you select in your query. In this case, all surrounding cities and villages of Rotterdam.
DECLARE @CityBuffer geography; SET @CityBuffer = (SELECT GEOG FROM NLD_adm2 WHERE NAME_2 = 'Rotterdam'); SELECT Geography::UnionAggregate(GEOG) FROM NLD_adm2 WHERE GEOG.STIntersects(@CityBuffer.STBuffer(1)) = 1;
As you can see in the post above, Spatial Data is a very powerful part of SQL Server. My guess is that it will only become more and more important for businesses.
I’m still looking for a subject for part 3 of this series. I’m thinking about storage, indexing or performance ofw spatial data, but I’d like to hear it from you. What do you want to see in part 3?