Pages

Saturday, September 4, 2010

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

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.

Map report

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:-

 Shape2SQL for geometry shapefile conversion

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:-

NL Map - Detailed

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

c:\windows\microsoft.net\framework\v3.5\csc /t:library /r:Microsoft.SqlServer.Types.dll C:\assemblies\aggregate.cs

3. Execute T-SQL to register the aggregate functions in SQL Server 2008
CREATE ASSEMBLY SpatialAggregate FROM 'c:\assemblies\Aggregate.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

NL Map - Aggregated to sales region

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';

6 comments:

  1. very nice post, thank you for sharing

    http://intosql.blogspot.com

    ReplyDelete
  2. Hi Jason,

    Sean Boon's blog above describes an option where polygons can be aggregated in SSRS using the union function and data/ group by setting of the polygon. For some reason this approach does not work for me - are there situation where this appoach is not viable?

    ReplyDelete
    Replies
    1. Can you give more details on what you are doing? Because I am able to achieve the same on my reports.

      Delete
    2. Sure, I am trying to build a custom sales territory for NC and SC based on state level shape file converted into SQL spacial and stored on the server. Here is the dataset query:

      select NAME, geom, TerritoryName = 'Carolinas'
      from [dbo].[shp_state]
      where STUSPS in ('SC','NC')

      In SSRS I am doing a union on the geom.value and then in group on TerritoryName (from polygon menu in SSRS).

      Delete
    3. Van you check section V of this post - http://www.sqljason.com/2013/09/how-to-make-map-reports-in-ssrs.html If you are still not able to get it, feel free to forward me the rdl at jason143@gmail.com and I can take a look at it!

      Delete
    4. It worked like a charm this time! Apparently, it is critical to place the union statement in the correct place. Sean's blog directs the user to the Data Layer properties of the polygon, while the union statement should go in the vector data line, under Spatial Data in the Polygon Layer properties. Thank you, Jason, for looking into this puzzle!

      Delete