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

Article
05/16/2009

In the previous posts we have covered items 1,2 and 3 of the workflow. In this post we address item 4.

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

SQL SERVER SPATIAL TOOLS ON CODEPLEX

Now that we have our spatial data suitably prepared, we need to move the Alaska and Hawaii spatial features to new locations. In order to do this, we need the help of a new function which we have published on the SQL Server Spatial Tools site at CodePlex.com. This function, AffineTransform, is one of several new functions, aggregates and data types which we have made available with source code. It is beyond the scope of this post to describe SQL Server Spatial Tools, but if you are not familiar with it, you should take a look.

REGISTERING THE SQL SPATIAL TOOLS ASSEMBLY

SQL Spatial Tools is delivered as an assembly (SQLSpatialTools.dll) which must be registered on the database you wish to use. The registration process is delivered as a register.sql file which needs two modifications (in red) before it is run:

-- Insert your database
use [Your_Database]
go

-- Enable CLR
sp_configure 'clr enabled', 1
reconfigure
go

-- Insert the path to the SQLSpatialTools assesmbly here
create assembly SQLSpatialTools from 'C:\Apps\SQLSpatialTools.dll'
go

...

REPOSITIONING ALASKA AND HAWAII, PASS 1

With SQL Server Spatial Tools registered to your database, the AffineTransform function is now available for use in T-SQL. The AffineTransform is a static function with 3 parameters: Translate, Scale and Rotate. Setting the initial values for these parameters, especially scale and rotate is based on an educated guess. The Scale and Translate parameters are addressable via independent X and Y component values. The Rotate parameter has a single value, with positive numbers rotating the object in a counter-clockwise direction, negative numbers in a clockwise direction. Displaying the grid lines (Show grid lines in Management Studio) can be used to assist in setting the X and Y Translate values.

Here is the T-SQL for the first pass at repositioning Alaska and Hawaii:

USE [Your_Database]
GO

----------------------------------------------------------------
-- Reposition Alaska
----------------------------------------------------------------
DECLARE @a GEOMETRY
SELECT @a = GEOM FROM us_states_inset2 WHERE STUSPS = 'AK'
SET @a = AffineTransform::Translate(-1500000, -2500000).Apply(@a)
SET @a = AffineTransform::Scale(.75, .75).Apply(@a)
SET @a = AffineTransform::Rotate(15).Apply(@a)

----------------------------------------------------------------
-- Reposition Hawaii
----------------------------------------------------------------
DECLARE @h GEOMETRY
SELECT @h = GEOM FROM us_states_inset2 WHERE STUSPS = 'HI'
SET @h = AffineTransform::Translate(2500000, -400000).Apply(@h)
SET @h = AffineTransform::Scale(2, 2).Apply(@h)
SET @h = AffineTransform::Rotate(15).Apply(@h)

----------------------------------------------------------------
-- Display Original and Repositioned Features
----------------------------------------------------------------
SELECT @a -- Repositioned Alaska
UNION ALL
SELECT @h -- Repositioned Hawaii
UNION ALL
SELECT GEOM FROM us_states_inset2 -- Original Feature Positions
GO

The red arrows show the displacement of the Alaska and Hawaii features, after the first pass, below:

REPOSITIONING ALASKA AND HAWAII, PASS "n"

I'd like to say that I was able to achieve final positioning of Alaska and Hawaii in 2 passes but it did take a few more iterations. Here is the T-SQL for my final pass, achieving the desired positioning of Alaska and Hawaii:

USE [Your_Database]
GO

----------------------------------------------------------------
-- Reposition Alaska
----------------------------------------------------------------
DECLARE @a GEOMETRY
SELECT @a = GEOM FROM us_states_inset WHERE STUSPS = 'AK'
SET @a = AffineTransform::Translate(-1500000, -4500000).Apply(@a)
SET @a = AffineTransform::Scale(.5, .5).Apply(@a)
SET @a = AffineTransform::Rotate(27).Apply(@a)

----------------------------------------------------------------
-- Reposition Hawaii
----------------------------------------------------------------
DECLARE @h GEOMETRY
SELECT @h = GEOM FROM us_states_inset WHERE STUSPS = 'HI'
SET @h = AffineTransform::Translate(5000000, -400000).Apply(@h)
SET @h = AffineTransform::Scale(1.5, 1.5).Apply(@h)
SET @h = AffineTransform::Rotate(30).Apply(@h)

The original location and the final repositioned location of Alaska and Hawaii are shown below:

UPDATE FINAL POSITIONS IN THE TABLE

With Alaska and Hawaii in final position, the last step is to update the geometry columns (GEOM) for the Alaska and Hawaii rows with the new geometry values:

----------------------------------------------------------------
-- Reposition Alaska
----------------------------------------------------------------
DECLARE @a GEOMETRY
SELECT @a = GEOM FROM us_states_inset WHERE STUSPS = 'AK'
SET @a = AffineTransform::Translate(-1500000, -4500000).Apply(@a)
SET @a = AffineTransform::Scale(.5, .5).Apply(@a)
SET @a = AffineTransform::Rotate(27).Apply(@a)

----------------------------------------------------------------
-- Reposition Hawaii
----------------------------------------------------------------
DECLARE @h GEOMETRY
SELECT @h = GEOM FROM us_states_inset WHERE STUSPS = 'HI'
SET @h = AffineTransform::Translate(5000000, -400000).Apply(@h)
SET @h = AffineTransform::Scale(1.5, 1.5).Apply(@h)
SET @h = AffineTransform::Rotate(30).Apply(@h)

----------------------------------------------------------------
-- Update Alaska Geometry Instance With New Position
----------------------------------------------------------------
UPDATE us_states_inset
SET GEOM = @a
WHERE STUSPS = 'AK'

----------------------------------------------------------------
-- Update Hawaii Geometry Instance With New Position
----------------------------------------------------------------
UPDATE us_states_inset
SET GEOM = @h
WHERE STUSPS = 'HI'

----------------------------------------------------------------
-- Display Table
----------------------------------------------------------------
SELECT * FROM us_states_inset

And there you have it - Alaska and Hawaii neatly tucked under the southwest states, ready for use in the new mapping function in SQL Server Reporting Services.

Technorati Tags: SQL Server,2008,Spatial,CodePlex,SQL Sever Spatial Tools,Affine,Transformation,Transform