다음을 통해 공유

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

  • 아티클

In the previous post we covered items 1 and 2 of the workflow.  In this post, we will cover item 3:

  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

The challenge now is to remove unwanted spatial features which are not relevant to the Reporting Services map we want to create.  Additionally, we will simplify the Alaska and Hawaii spatial features.

Let's define the features that we wish to complete remove:

  • American Samoa
  • Commonwealth of the Northern Mariana Islands
  • Virgin Islands of the United States
  • Guam
  • Puerto Rico

To remove these features, we will use standard T-SQL queries:

DELETE FROM us_states WHERE NAME = 'American Samoa'
DELETE FROM us_states WHERE NAME = 'Commonwealth of the Northern Mariana Islands'
DELETE FROM us_states WHERE NAME = 'Virgin Islands of the United States'
DELETE FROM us_states WHERE NAME = 'Guam'
DELETE FROM us_states WHERE NAME = 'Puerto Rico'
GO

Now that we are done with the mundane, let's look at the interesting problem of taking the Alaska and Hawaii spatial features and simplifying them by removing the of out lying islands in the Aleutian Islands and the Hawaiian archipelago. The image, below, shows the features we wish to remove:

image

This sounds simple enough until we realize that the Alaska and Hawaii spatial features are multipolygons and the features that we want to remove are part of the geometry instances. To make these features accessible, we have to separate the multipolygons into their individual polygon components. To do this we will use FME, both the Workbench and the Universal Viewer.  Here is the workflow:

image

The data source is the us_states database table.  An AttributeFilter transformer is specified to isolate the Hawaii multipolygon based on the STUSPS attribute:

image

The output of the AttributeFilter is then sent to a Deaggregator transformer to separate the Hawaii multipolygon into individual polygons.  The output from the Deaggregator transformer is sent to a Visualizer, below:

image

In the Visualizer, we can identify each unique polygon which makes up the Hawaii spatial feature using the FME attribute, _part_number.  Using this approach, we determined that we want to keep _part_numbers 1,2,3,4,6 and 7.

The Alaska problem is the reverse since there are so many parts to the multipolygon.  In this case we will identify the polygons which we want to remove: _part_numbers 22, 18, 37, 29, 6, 12, 27, 31, 10, 48, 47, 13, 28, 9, 17, 3, 34, 39 and 44.

image

This workflow takes the separated (disaggregated) Hawaii and Alaska multipolygons and applies attribute filter transformers to identify specific component polygon elements.  For the Hawaii sub-flow, the new RangeFilter transformer (FME 2010 beta) is used since polygon parts could be easily identified by two ranges.  For the Alaska sub-flow, the polygon components to be removed are listed in the AttributeFilter transformer.  The <UNFILTERED> channel is then chosen, returning the remaining Alaska polygon components.  For both sub-flows, a final Aggregator transformer is used to reassemble the polygon components into multipolygon instances.  All 3 sub-flows (Alaska, Hawaii and <UNFILTERED>) from the first AttributeFilter are channeled to the output database table, us_states_inset.  This new table with the simplified Alaska and Hawaii spatial features is illustrated below from SQL Server Management Studio:

image

With the spatial features suitably simplified, we are now ready for the next step, "repositioning" the Alaska and Hawaii spatial features as insets.  This will be described in Part 3.

Technorati Tags: SQL Server,2008,Spatial,Feature Manipulation Engine,FME,AttributeFilter,RangeFilter,Disaggregator,Aggregator,Transformer,FME 2010 Beta

Comments