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):
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):
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):
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.
Kommentare (0)