SQLServerCentral Article

Create CNN-style Map in Reporting Services

,

Map is one of the exciting new features introduced in SQL Server 2008 R2. Although the Map Gallery, Map Wizard, and Map Layer Wizard make it very easy to display spatial data on the report, there is no out-of-box template that displays a map often seen in news publications, such as CNN:

In this article, we will demonstrate a process to transform US Census' TIGER/Line shapefile to display CNN-style map in Reporting Services. The following data/tools are used in this article:

The source code and database are attached at the end of the article.

Install SQL Server Spatial

SQL Server Spatial Tools contains a collection of functions and aggregates for spatial data. Specifically, we will use AffineTransform functions to scale and reposition Alaska and Hawaii. The installation process is rather straightforward. All you need to do is to unzip the package, locate the path to the dll file, and run Register.sql. After the installation, you can test it by executing this query:

SELECT AffineTransform::Scale(.25, .25).Apply(GEOMETRY::Parse('POLYGON((0 50, 2 50, 1 51, 0 50))'))

Load the Shapefile

After SQL Server Spatial Tools are installed, we will use Shape2Sql to upload the shapefile in tl_2008_us_state.zip into SQL Server. This process is a actually more complicate than it seems because some states in this shapefile cannot be directly loaded as Geography data type. Fortunately, a great article: Fixing Invalid Geography Data details the steps needed to complete this task. Here is a summary:

  1. Unzip the tl_2008_us_state.zip and SqlSpatialTools.zip files.
  2. Double click on Shape2Sql.exe and upload tl_2008_us_state.shp to SQL server as Geometry data type:

  3. Convert Geometry to Geography

    ALTER TABLE [tl_2008_us_state]
    ADD BoundaryGeog GEOGRAPHY
    UPDATE  tl_2008_us_state
    SET     BoundaryGeog = GEOGRAPHY::STGeomFromWKB(BoundaryGeom.MakeValid().STAsBinary(), 4326)
    WHERE   NAME NOT IN('Massachusetts', 'New Mexico', 'Wyoming', 'Montana', 'Texas', 'North Carolina', 'Arizona', 'Tennessee', 'Utah')
    UPDATE  tl_2008_us_state
    SET     BoundaryGeog = GEOGRAPHY::STGeomFromWKB(BoundaryGeom.STUnion(BoundaryGeom.STStartPoint()).MakeValid().STAsBinary(), 4326)
    WHERE   NAME = 'Massachusetts'
    UPDATE  tl_2008_us_state
    SET     BoundaryGeog = GEOGRAPHY::STGeomFromWKB(BoundaryGeom.STBuffer(0.00001).STBuffer(-0.00001).MakeValid().STAsBinary(), 4326)
    WHERE   NAME IN ('New Mexico', 'Wyoming', 'North Carolina', 'Tennessee')
    UPDATE  tl_2008_us_state
    SET     BoundaryGeog = GEOGRAPHY::STGeomFromWKB(BoundaryGeom.Reduce(0.00001).MakeValid().STAsBinary(), 4326)
    WHERE   NAME IN ('Texas', 'Montana', 'Arizona', 'Utah')
    

Transfer Data from Pre-stage Table to Production Table

Once the Geography field in tl_2008_us_state is fully populated, the table is ready to be transferred to the production table. During this step, the Reduce() function is called with 250 tolerance to simplify boundary polygons, which will in turn improve report performance. The MapSetId flag is needed later in the Reporting Services and will be further investigated.

CREATE TABLE dbo.[State] 
(
    StateFips VARCHAR(2) NOT NULL,
    StateAbbrev  VARCHAR(2) NOT NULL, 
    StateName  VARCHAR(50) NOT NULL, 
    BoundaryGeog  GEOGRAPHY NOT NULL,
    MapSetId INT NOT NULL
    CONSTRAINT departments_pk PRIMARY KEY (StateFips) 
); 
INSERT  dbo.[State] 
SELECT  STATEFP AS StateFips,
        STUSPS AS StateAbbrev,
        NAME AS StateName,
        BoundaryGeog.Reduce(250) AS BoundaryGeog,
        1 AS MapSetId
FROM    dbo.tl_2008_us_state
WHERE   STUSPS NOT IN ('AS', 'MP',  'VI', 'GU', 'PR')
CREATE SPATIAL INDEX SIX_State ON dbo.[State](BoundaryGeog);

Scale and Reposition AK and HI

During this step, we will use AffineTransform functions from SQL Server Spatial Tools to scale and reposition Alaska and Hawaii. Because AffineTransform functions only apply to Geometry data type, we need to convert Geography to Geometry, perform affine transformation, and convert Geometry back to Geography. After some trial and error, the following transformations produce similar shapes to our target map.

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Reposition AK
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
DECLARE @StateFips VARCHAR(10)
DECLARE @BoundaryGeog GEOGRAPHY
DECLARE @BoundaryGeom GEOMETRY
SELECT  @BoundaryGeog = BoundaryGeog
FROM    dbo.[State]
WHERE   StateAbbrev = 'AK'
-- Convert to GEOMETRY
SET @BoundaryGeom = GEOMETRY::STGeomFromWKB(@BoundaryGeog.STAsBinary(), 4326)
-- AffineTransform
SET @BoundaryGeom = AffineTransform::Scale(.15, .25).Apply(@BoundaryGeom)
SET @BoundaryGeom = AffineTransform::Translate(-93, 13).Apply(@BoundaryGeom) 
-- Convert back to GEOGRAPHY
UPDATE  dbo.[State]
SET     BoundaryGeog = GEOGRAPHY::STGeomFromWKB(@BoundaryGeom.MakeValid().STAsBinary(), 4326)
WHERE   StateAbbrev = 'AK'

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Reposition HI
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
DECLARE @StateFips VARCHAR(10)
DECLARE @BoundaryGeog GEOGRAPHY
DECLARE @BoundaryGeom GEOMETRY
SELECT  @BoundaryGeog = BoundaryGeog
FROM    dbo.[State]
WHERE   StateAbbrev = 'HI'
-- Convert to GEOMETRY
SET @BoundaryGeom = GEOMETRY::STGeomFromWKB(@BoundaryGeog.STAsBinary(), 4326)
-- AffineTransform
SET @BoundaryGeom = AffineTransform::Scale(1.25, 1.25).Apply(@BoundaryGeom)
SET @BoundaryGeom = AffineTransform::Translate(89, 2.5).Apply(@BoundaryGeom)
-- Convert back to GEOGRAPHY
UPDATE  dbo.[State]
SET     BoundaryGeog = GEOGRAPHY::STGeomFromWKB(@BoundaryGeom.MakeValid().STAsBinary(), 4326)
WHERE   StateAbbrev = 'HI'

Here is what our map looks like after these steps:

SELECT  BoundaryGeog,
        StateAbbrev
FROM    dbo.[State]

The map looks fairly close to what we want except the small Northwestern Hawaiian Islands are trailing off to the west of California. To filter out those small islands, we will first create a radius that is big enough to cover the main islands, then call STIntersection() to get the intersection of main islands' shapes and the radius' shape. Here is our final transformation:

UPDATE  dbo.[State]
SET     BoundaryGeog = GEOGRAPHY::Parse('POINT(-107 28)').STBuffer(500000).STIntersection(BoundaryGeog)
WHERE   StateAbbrev = 'HI'
SELECT  BoundaryGeog,
        StateAbbrev
FROM    dbo.[State]

Rectangular States

Finally, we need to create 9 rectangular states: VT, NH, MA, CT, RI, NJ, DE, MD, and DC in the Atlantic Ocean:

SELECT  'VT' AS StateAbbrev, 1 AS Ordinal INTO #RectState
INSERT  #RectState SELECT 'NH', 2
INSERT  #RectState SELECT 'MA', 3
INSERT  #RectState SELECT 'CT', 4
INSERT  #RectState SELECT 'RI', 5
INSERT  #RectState SELECT 'NJ', 6
INSERT  #RectState SELECT 'DE', 7
INSERT  #RectState SELECT 'MD', 8
INSERT  #RectState SELECT 'DC', 9
DECLARE @RectSpacing FLOAT
DECLARE @RectHeight FLOAT
DECLARE @Lat1 FLOAT
DECLARE @Lat2 FLOAT
DECLARE @Lon1 FLOAT
DECLARE @Lon2 FLOAT
DECLARE @iRectState INT
DECLARE @Wkt VARCHAR(1000)
DECLARE @Geog GEOGRAPHY 
DECLARE @Geom GEOMETRY
SET @RectSpacing = 1
SET @RectHeight = 2.5
SET @Lat1 = 46.5
SET @Lat2 = @Lat1 - @RectHeight
SET @Lon1 = -66
SET @Lon2 = -62
SET @RectSpacing = 1.0
SET @iRectState = 1
WHILE @iRectState < 10
BEGIN   
    SET @Wkt = 'POLYGON((' +
        CAST(@Lon1 AS VARCHAR(10)) + ' ' + CAST(@Lat1 AS VARCHAR(10))  + ',' +
        CAST(@Lon1 AS VARCHAR(10)) + ' ' + CAST(@Lat2 AS VARCHAR(10))  + ',' +
        CAST(@Lon2 AS VARCHAR(10)) + ' ' + CAST(@Lat2 AS VARCHAR(10))  + ',' +
        CAST(@Lon2 AS VARCHAR(10)) + ' ' + CAST(@Lat1 AS VARCHAR(10))  + ',' +
        CAST(@Lon1 AS VARCHAR(10)) + ' ' + CAST(@Lat1 AS VARCHAR(10))  +  '))'
    -- Wkt -> Geog
    SET @Geog = GEOGRAPHY::STPolyFromText(@Wkt, 4326)
    -- Geog -> Geom
    SET @Geom = GEOMETRY::STGeomFromWKB(@Geog.STAsBinary(), @Geog.STSrid)
    -- Calculate bounding box
    SET @Geom = @Geom.STEnvelope().MakeValid()
    -- Geom -> Geog and Insert
    INSERT  dbo.[State]
    SELECT  S.StateFips,
            S.StateAbbrev,
            S.StateName,
            GEOGRAPHY::STGeomFromText(@Wkt, 4326) AS BoundaryGeog,
            2 AS MapSetId
    FROM    dbo.[State] S
            INNER JOIN #RectState B ON S.StateAbbrev = B.StateAbbrev AND B.Ordinal = @iRectState
    SET @iRectState = @iRectState + 1
    SET @Lat1 = @Lat1 - @RectHeight - @RectSpacing
    SET @Lat2 = @Lat2 - @RectHeight - @RectSpacing
END

Note that the 9 rectangular states' MapSetId are set to 2. The reason for putting them in different set from the Census' state shapes (whose MapSetIds were set to 1) is because we will use Bonne projection and planar projection respectively for MapSetId=1 and MapSetId=2 in Reporting Services. The following figure shows shapes in different projections.

Finally, let’s input some data to stylize the shapes. For the demonstration purpose, we will style the map similar to the CNN map we displayed in the Introduction section.

CREATE TABLE dbo.StateData
(
    StateFips VARCHAR(2) NOT NULL,
    FillColor VARCHAR(20) NOT NULL,
    FillPattern VARCHAR(20) NOT NULL,
    BorderColor VARCHAR(20) NOT NULL,
    TextColor VARCHAR(20) NOT NULL
)
INSERT  dbo.StateData
SELECT  StateFips,
        '#185aad' AS FillColor,
        'None' AS FillPattern,
        'White' AS BorderColor,
        'White' AS TextColor
FROM    dbo.[State]
WHERE   StateAbbrev IN ('WA', 'OR', 'CA', 'MN', 'WI', 'IL', 'MI', 'PA', 'NY', 'ME', 'HI') OR MapSetId = 2
INSERT  dbo.StateData
SELECT  StateFips,
        '#185aad' AS FillColor,
        'BackwardDiagonal' AS FillPattern,
        'White' AS BorderColor,
        'White' AS TextColor
FROM    dbo.[State]
WHERE   StateAbbrev IN ('NV', 'CO', 'NM', 'IA', 'VA')
INSERT  dbo.StateData
SELECT  StateFips,
        '#185aad' AS FillColor,
        'BackwardDiagonal' AS FillPattern,
        '#f79404' AS BorderColor,
        '#f79404' AS TextColor
FROM    dbo.[State]
WHERE   StateAbbrev IN ('IN', 'OH', 'FL')
INSERT  dbo.StateData
SELECT  S.StateFips,
        CASE WHEN S.StateAbbrev IN ('MO', 'NC') THEN '#7b7a79' ELSE '#bc0b16' END AS FillColor,
        CASE WHEN S.StateAbbrev IN ('MO', 'NC') THEN 'DottedDiamond' ELSE 'None' END AS FillPattern,
        CASE WHEN S.StateAbbrev IN ('MO', 'NC', 'MT', 'ND') THEN '#f79404' ELSE 'White' END AS BorderColor,
        CASE WHEN S.StateAbbrev IN ('MO', 'NC', 'MT', 'ND') THEN '#f79404' ELSE 'White' END AS TextColor
FROM    dbo.[State] S
        LEFT OUTER JOIN dbo.StateData D ON S.StateFips = D.StateFips
WHERE   D.StateFips IS NULL

Creating Map Report

In Reporting Services, we will start by creating a Data Source to the database and a DataSet for the map data:

SELECT  S.StateAbbrev,
        BoundaryGeog,
        S.MapSetId,
        D.*
FROM    dbo.[State] S
        INNER JOIN dbo.StateData D ON S.StateFips = D.StateFips

Then drag and drop a Map report item onto the canvas. As discussed in the section above, we will need two Map items: one displays Census’ state shapes in Bonne projection and another one displays 9 rectangular state shapes in planar projection. Let’s start with the first one.

Follow the wizard to select MapData as the dataset:

Accept the default value and click on the Next button for the next two steps:

Check Display labels and select StateAbbrev as the data field:

After the wizard, clean up the map by deleting unwanted components, such as title, color scale, distance scale, and legend. The map should look like:

Now, we need to limit the map to display only Census’ state shapes. Right click on the polygon layer, select Layer Data, set a filter on MapSetId = 1.

Next, configure the shading by changing the polygon properties. Right click on the polygon layer, select Polygon Properties, change font color to white and fill color and border color to use data fields:

Finally, go to Viewport Properties and change Projection from Mercator to Bonne.

The map now looks like:

Now, let’s proceed to create the second map for the 9 rectangular state shapes. The steps are basically the same as the first map except the filter is set to MapSetId = 2 and projection is set to Planar (X, Y). After both maps are created, change map views’ center and zoom to nicely position the two maps side-by-side. Also hide maps’ border and change background color to white. After these cosmetic touch ups, the report will look like:

Conclusion

SQL Server 2008 R2 Reporting Services can display spatial data from three different sources: Map Gallery (RDL file with embedded spatial data), ESRI shapefile, or SQL Server Spatial Query. In this article, we choose to use the third option and store spatial data in SQL server (as opposed to RDL or shapefile) because it allows us to easily manipulate spatial data. For example, if your organization groups Alaska, Hawaii, and other western states into the same sales region, the sales region map can be easily created by applying the GeographyUnionAggregate() aggregate to the dbo.[State] table we created:

SELECT  'Western Sales Region' AS Label,
        dbo.GeographyUnionAggregate(BoundaryGeog) AS BoundaryGeog
FROM    dbo.[State]
WHERE   StateAbbrev IN ('AK', 'HI', 'CA', 'AZ', 'UT', 'WA', 'NV', 'ID', 'OR')

Resources

Rate

4.92 (51)

You rated this post out of 5. Change rating

Share

Share

Rate

4.92 (51)

You rated this post out of 5. Change rating