Hilmar Buchta

SQL Server 2008 R2

As a response on my last post I was asked if this is also possible with postal code regions (PLZ). It’s more difficult to find free, usable map data for this purpose. But with the help of a popular search engine I came up with the following link: http://arnulf.us/PLZ

Here you can get the ESRI shape file for the German postal code regions. You can load this data in exactly the same way as shown in my last post.

This is how the map looks after importing it into a SQL Server table (displayed in SQL Spatial Query Visualizer):

Spatial data

As you can see, the map is very detailed. It would be perfect, if we could add some layer of aggregation. For the German postal code, it would be a good idea to aggregate them using the first two digits, so that for example, the aggregated level of 40 includes all the postal codes like 40221 (the postal code of my company’s headquarters).

In order to join the shapes of the postal code areas we can use the SQL Server 2008 spatial functions. Therefore I loaded the data provided by the link above into a table post_pl. The following T-SQL script then creates a new table with the name post_pl2 containing the shape data for the aggregated areas:

IF EXISTS(SELECT name FROM sys.tables WHERE name = ‚post_pl2‘) begin drop table post_pl2 end

create table post_pl2 (
PLZ2 nvarchar(2),
geom geometry
)

DECLARE @pl_group nvarchar(2)
DECLARE @t geometry = ‚GEOMETRYCOLLECTION EMPTY‘;

DECLARE my_cursor CURSOR FOR
SELECT distinct left(PLZ99,2) FROM post_pl

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @PL_Group

WHILE @@FETCH_STATUS = 0
BEGIN
set @t=’GEOMETRYCOLLECTION EMPTY‘
select @t= @t.STUnion(geom.MakeValid()) from dbo.post_pl
where PLZ99 like @pl_group+’%‘

    insert into post_pl2(PLZ2,geom) values(@pl_group, @t.BufferWithTolerance(0.001,0.1,0))

    FETCH NEXT FROM my_cursor
INTO @PL_Group
END

CLOSE my_cursor
DEALLOCATE my_cursor

The query takes some time to execute (about a minute on my virtual development environment), so be patient. I’m using BufferWithTolerance here to smooth the results, because the postal code areas do not fit perfectly. The resulting spatial data is now joined together at postal code region level (2 digits):

Spatial data

From here you can get a more detailed view by filtering on a single postal code region (in my example, I’m using the region 40xxx):

Spatial data

Instead of the T-SQL code from above you could also use the .Net stored-procedure from Craig Dunn’s post. This makes it very easy to build the aggregate because it provides a “normal” aggregation function UnionAggregate that is capable of aggregating the data type geometry. If your spatial data is stored in a field of the type geography instead, you will definitely want to take a look at the SQL Server Spatial Tools at Codeplex. Here you can find the corresponding GeographyUnionAggregate for the geography data type.

You could also combine the spatial information from this post with the shapes from my last post to allow a drilldown like Country, German state (Bundesland) and then postal code region and postal code.