Aggregating shape data in T-SQL

Technical Value

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


select geom
from dbo.post_pl
where PLZ99 like '42%'
select dbo.UnionAggregate(geom.MakeValid())
from dbo.post_pl
where PLZ99 like '42%'
image image

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()

from dbo.post_pl where PLZ99 like '42%'
from dbo.post_pl where PLZ99 like '42%'
image image

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

  1. DECLARE @s1 AS GEOMETRY = 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'      
  2. DECLARE @s2 as GEOMETRY = 'POLYGON((1.1 0, 2.1 0, 2.1 1, 1.1 1, 1.1 0))'      
  3. select @s1.STUnion(@s2).STBoundary()


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:

  1. select @s1.STDistance(@s2)

The distance is actually 0.1 units.

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

  1. DECLARE @s1 AS GEOMETRY = 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'      
  2. DECLARE @s2 as GEOMETRY = 'POLYGON((1.1 0, 2.1 0, 2.1 1, 1.1 1, 1.1 0))'      
  3. select @s1.STUnion(@s2).STBuffer(0.1).STBoundary()


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.


Eric Geddes
So, 03.06.2012 - 02:23

Even when you're buffering the shape by a minuscule amount, you can run into trouble with selections or calculations you perform with it afterwards. The resulting, slightly larger, polygon may overlap shapes, or features, that the original polygons did not.

An alternate solution that isn't much more difficult is to select only the exterior ring using STExteriorRing. It'd be great if that was all, but the result is a polyline not a polygon. Fortunately it's easy to convert from one to another by simply replacing the type in the text representation of the geometry. Something like:

DECLARE @g geometry = '...'

SELECT geometry::STGeomFromText(
REPLACE(@g.STExteriorRing().ToString(),'LINESTRING (','POLYGON((')+')'
, @g.STSrid)

Painless. The STSrid is optional but a good practice.

Thanks for the good info.

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden