31.05.2010

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)
  7. DECLARE @t geometry = 'GEOMETRYCOLLECTION EMPTY';
  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
  12. WHILE @@FETCH_STATUS = 0
  13. BEGIN
  14. set @t='GEOMETRYCOLLECTION EMPTY'
  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.

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