German map spatial data for PLZ (postal code) regions

Technical Value

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:

  1. IF EXISTS(SELECT name FROM sys.tables WHERE name = 'post_pl2') begin drop table post_pl2 end
  2. create table post_pl2 (
  3. PLZ2 nvarchar(2),
  4. geom geometry
  5. )
  6. DECLARE @pl_group nvarchar(2)
  8. DECLARE my_cursor CURSOR FOR
  9. SELECT distinct left(PLZ99,2) FROM post_pl
  10. OPEN my_cursor
  11. FETCH NEXT FROM my_cursor INTO @PL_Group
  13. BEGIN
  15. select @t= @t.STUnion(geom.MakeValid()) from dbo.post_pl
  16. where PLZ99 like @pl_group+'%'
  17.     insert into post_pl2(PLZ2,geom) values(@pl_group, @t.BufferWithTolerance(0.001,0.1,0))
  18.     FETCH NEXT FROM my_cursor
  19. INTO @PL_Group
  20. END
  21. CLOSE my_cursor
  22. 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.


So, 06.06.2010 - 16:46

[...] 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() [...]

Di, 31.01.2012 - 12:14

Hello there,

is the aggregated data (PLZ 2 digits) available for download somewhere?
I am interested in polygons/shapefile information, from which I can create SVG files for the PLZ2 regions (in order to color code those...).


Hilmar Buchta
Mi, 01.02.2012 - 11:47

We don't have a ready-to-download aggregation of these polygons. However, you can follow the instructions given here (download location of the ESRI file and the SQL function provided here) to create the shapes at any aggregate you would like to have.

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