"Cartographic Adjustment" of Spatial Data for SQL Server Reporting Services, Part 1

I was recently presented with the problem of taking a US States dataset, in geographic coordinates, and creating a database table whose spatial column would render in a similar fashion to the map, below, with the Alaska and Hawaii insets.

The ultimate goal was to use the resulting table in the new map control of the upcoming Report Builder in SQL Server Reporting Services (SSRS).

APPROACH

To illustrate how do this, I choose to use Safe Software's Feature Manipulation Engine (FME 2010 Beta) and SQL Server 2008.  Safe Software aficionados will point out that this could have been done completely in FME. Likewise, this also could have been done completely in SQL Server 2008, though it would have been much more difficult. The use of both tools to solve this problem represents a reasonable real-world approach.

WORKFLOW

Here is the basic workflow:

1. Locate data source and download
2. Load the data into SQL Server
3. Remove unwanted features, simplify Alaska and Hawaii spatial features 
4. "Reposition" Alaska and Hawaii cartographically
5. Use the data in the new SQL Server Report Builder

In this post we will cover workflow items 1 and 2.  In subsequent post we will address the other workflow items.

DATA SOURCE

The original data came from the US Census Bureau's collection of geographic data:

https://www2.census.gov/geo/tiger/TIGER2008/

Specifically, the data was the Tiger Line 2008 US State ShapeFile (tl_2008_us_state) found at:

https://www2.census.gov/geo/tiger/TIGER2008/tl_2008_us_state.zip

Here is how the input Shapefile appears in the FME Universal Viewer:

 

DATA LOADING

Data loading was accomplished using the FME Workbench, using the following procedure:

1. Identify the tl_2008_us_state.shp Shapefile as the input data source
2. Project the spatial data into an Albers Equal Area projection suitable for the conterminous United States
3. Writing the data to a SQL Server 2008 table using the geometry data type

Add a data source (Source Data), specifying the format as ESRI Shape, the dataset as tl_2008_us_state.shp and the coordinate system as EPSG: 4269 (NAD 83).

For visual appeal, an Albers Equal Area projection is chosen using the ESRIReprojector transformer.  I wanted to use the Reprojector transformer but, to date, have not figured out how to locate an existing projection for the North America (-96 central meridian, etc.).

No we get to the important part - SQL Server Spatial, our format for the Destination Dataset.  Choose settings, next.

The setting menu panel allows you to setup the database connection and specify the output spatial column and name:

Selecting OK bring you back to the Add Destination Dataset where you can select OK to move on...

I always find this next part in FME a wee bit confusing, but before we get into that, answer "Yes" to this menu.

 

  When FME asks, in a database context, if you would like to add a new feature type to the data set, what is really trying to say is, "what would like to name the new database table?".  In this case our new table table (FME Feature Type Name) will be set to us_states.

Hit "OK" and we now can see the (almost) completed FME workspace.  Note that the destination end point is not yet "connected" to the Shapefile attribute data.  You can tell this by the yellow connector on the ESRIReprojector transformer and the fact that no attributes are list below destination endpoint:

Right-clicking on the destination endpoint brings up a contextual menu which provides a number of choices.  You want the "Copy Attributes From Feature Type" option:

In this case, you have only one choice and it's the correct one (what a coincidence  ;-):

The workflow is now complete and the data load sequence is ready to run:

To run the workflow, select the green triangle "run" button from the FME menu:

During the run process, FME writes out all actions to a log file and displays the log to the screen.  In this case, the log noted the following:

MS SQL Server (Spatial) Writer: Failed to write SRID because the feature's coordinate system could not be converted to an EPSG number

Let's take a look at the new table, us_states, in SQL Server Management Studio, to see how the SRID value is treated.  Here is how the spatial data displays using the "Spatial results" tab after a SELECT * FROM us_states query:

To check the SRID values of each geometry instance we can use the following query:

SELECT geom.STSrid AS SRID FROM us_states

From this we can ascertain that when FME cannot figure out the proper EPSG-based SRID to assign to geometry instances, it does nothing and allows SQL Server to use its default SRID value of 0.

Technorati Tags: SQL Server,2008,spatial,Safe Software,Feature Manipulation Engine,FME,Universal Viewer,data loading,Shapefile,shape file,SRID,EPSG

Comments

• Anonymous
  May 09, 2009
  PingBack from http://www.anith.com/?p=36365

• Anonymous
  May 10, 2009
  Thank you for submitting this cool story - Trackback from DotNetShoutout

• Anonymous
  May 12, 2009
  "....The ultimate goal was to use the resulting table in the new map control of the upcoming Report Builder in SQL Server Reporting Services (SSRS)...." Oooh! Maps in SSRS - having used the Dundas Map Control since SSRS2005 (and unable to upgrade to SSRS2008 since it's not supported), I've been waiting on this for ages. Any indication when normal folks will be able to get to play with this new feature? Any beta-testing opportunities?

• Anonymous
  May 13, 2009
  The Report Builder supporting "maps" will be available in the Kilimanjaro (SQL10.5) CTP2.  In Part 4, I'll give the details on when we can expect this CTP to be released.  

• Anonymous
  January 04, 2010
  trying to make a deal - properties