Aggregating shape data in T-SQL

SQL Server 2008 | SQL Server 2008 R2

In order to utilize map  data in SQL Server the spatial data types and the spatial functions of the SQL Server are important. In my last post I “aggregated” detailed shapes up to a surrounding shape by using the built-in STUnion() function.

For the current post I’m using the detailed data for the German PLZ regions from my last post together with Craig Dunn’s function for aggregating shapes. The installation of these aggregate functions is pretty straight forward. After compiling the library from the source code you have to register the new aggregate in your SQL Server database. You will find all the needed information in his post.

Using this library, the aggregation of the shape becomes pretty easy. For example. The full aggregation for postal code region 42 now looks like this:

Not aggregated	Aggregated
select geom from dbo.post_pl where PLZ99 like ‚42%‘	select dbo.UnionAggregate(geom.MakeValid()) from dbo.post_pl where PLZ99 like ‚42%‘

This is much easier than the T-SQL function from my last post. I still recommend to persist the results for later use because the geo spatial operation may be more time consuming than “normal” queries.

However, there are two special topics to take care of:

1. Not all polygons may be correct (Open Geospatial Consortium type)
2. The shapes may not fit together perfectly

The first topic could result in errors when using the aggregation function above as well as the built-in T-SQL STUnion() function. This can be easily avoided using the MakeValid() function as shown in the example above. If you omit this function and remove the where-condition to get the aggregate of the full German map you will get a .Net error because of incorrect shapes.

For the second topic, take a closer look to the right picture from above. You will notice some lines within the shape. This effect becomes even more visible if we use the STBoundary() function to reduce the shape to its boundaries:

Without STBuffer()	With STBuffer()
select dbo.UnionAggregate(geom) .STBoundary() from dbo.post_pl where PLZ99 like ‚42%‘	select dbo.UnionAggregate(geom) .STBuffer(0.00001).STBoundary() from dbo.post_pl where PLZ99 like ‚42%‘

STBuffer() simply adds some buffer around the existing shape. This can be easily examined using the following T-SQL statement with its corresponding result:

As you can see, there is a small gap between the two squares. If you don’t want to look at the coordinates, you can query the distance by using the STDistance function, for example:

The distance is actually 0.1 units.

If we now add the STBuffer function the result looks like this:

By actually extending the shape a little bit, the tiny gap in the middle of the shape disappears. This is why the map on the right side looks smoother than then one on the left side. You still need to be careful with the parameter for STBuffer. Higher parameters will result in a totally different shape. For example, if we use 4 as the parameter value in the example above, the shape looks like this:

Also higher values of STBuffer result in more time needed to compute the new shape. So in practice, you would try small values first or you could use the STDistance-function to compute the distance between the shapes.

As some people are surprised, the samples above are also fully functional with SQL Server 2008 because the spatial data type and functions have been introduced with SQL Server 2008. New in SQL Server 2008 R2 is the map support for Reporting Services, not the support for spatial data.