Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

Around 6 months ago, I was desperately looking for some online resources to help me implement some map reports. The shapefile I was dealing with was at a very granular level (which means that it had a lot of data, 2.5 MB in size), and we needed to do some custom aggregations on the spatial data. As the custom aggregations were done at report run time, it turned out to be a major performance bottleneck. That was when this blog by Sean Boon – SQL Server 2008 R2 Map Tips: How To Import Shapefiles Into SQL Server and Aggregate Spatial Data, caught my eye and I got the idea of importing my shapefile into DB and then doing the custom aggregations there itself for solving my performance problems. Armed with the contents from this blog, I started developing my reporting solution but soon stumbled upon a major roadblock – I realized that the blog he had written was for aggregating shapefiles of Geography data type, while the shapefile I had with me was of Geometry data type (read here to find the difference between the two data types). After a couple of days of searching, I finally managed to find the solution and thought of blogging it down at least now.

The major steps involved in the solution are:-

1) Converting Shapefiles into SQL Server Spatial Data : As mentioned in Sean’s blog, we will be using the tool from http://www.sharpgis.net/page/Shape2SQL.aspx called Shape2SQL. Download the software from the site and then click on the exe to see the user interface. If you go to the site, you will find an image of the interface which guides you how to convert the shapefile into SQL Server Spatial data but then that is for a shapefile of Geography data type. So how do you convert a shapefile of Geometry data type into spatial data? For that, make sure that the interface looks like the image given below:-

For some reason, I had to create the table beforehand, the tool didn’t automatically create the table as I thought it would. The table creation script is also given below so that you can have an idea of the columns

CREATE TABLE [dbo].[MapData](
[geom] [geometry] NULL,
[PC4CODE] [nvarchar](max) NULL,
[Sales Rep] [nvarchar](max) NULL)

So as you can see, in my table, I would have all the PC4Code information (which is the postal code) at the end of this exercise. Now I update my table such that I get all the parent information in the [Sales Rep] column (which would have all the list of sales regions). My requirement is that instead of having a map by some 4000 odd postal codes, I would like to see a map by just the 40 odd sales regions that I have. A simple select from the MapData table would look like this now:-

2) Aggregate Polygons in SQL Server – Get assembly : For aggregating polygons of geography data type, we can use the method that Sean has mentioned in his blog. But that particular function would not work in the case of geometry data type. For doing this, please follow the steps mentioned in ConceptDev (Craig Dunn’s blog) quoted below

”DOWNLOAD the source (8k) to try out the two aggregates posted on MSDN, or follow these steps:

1. Download the code from MSDN, or my copy. NOTE: You must do all these steps on a PC with SQL Server 2008 (Katmai) or above installed to get the Microsoft.SqlServer.Types.dll assembly. I copied this assembly into C:assemblies.

2. Compile the C# SQLCLR functions in C:assemblies via the Command Prompt

3. Execute T-SQL to register the aggregate functions in SQL Server 2008
CREATE ASSEMBLY SpatialAggregate FROM ‘c:assembliesAggregate.dll’
go
CREATE AGGREGATE EnvelopeAggregate(@input GEOMETRY) RETURNS GEOMETRY
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.EnvelopeAggregate]
go
CREATE AGGREGATE UnionAggregate(@input GEOMETRY) RETURNS GEOMETRY
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.UnionAggregate]
go

sp_configure ‘clr enabled‘, 1;
go
RECONFIGURE;
go
”

3) Aggregate Polygons in SQL Server – Use function to aggregate data and store in table : Now that you have the aggregate functions registered, you can use it in a query to aggregate it and store it in a table like shown below:-

select WAM=M.[Sales_Rep],
geom=dbo.UnionAggregate(geom.MakeValid())
into NL_Map
from MapData M
group by M.[Sales_Rep]

The UnionAggregate function will aggregate the data and the aggregated data can be viewed by a simple select on the new NL_Map table

Now you are all set to use this table as the source of your reports.

Update (25/03/2012)

SQL 2012 has integrated these features and hence if you are using SQL 2012, you can aggregate both geography and geometry data types using the built in functions Geometry::UnionAggregate(geom) and Geography::UnionAggregate(geog). Refer the blog below http://social.technet.microsoft.com/wiki/contents/articles/4136.aspx

“ UnionAggregate() combines multiple spatial objects into a single spatial object, removing interior boundaries, where applicable.
SELECT Geography::UnionAggregate(geog) FROM Counties
WHERE name = ‘Washington’; “