-- Tools / Data:

-- 1. SQL Server Spatial Tools: http://sqlspatialtools.codeplex.com/
-- 2. US State shapefile: http://www2.census.gov/geo/tiger/TIGER2008/tl_2008_us_state.zip
-- 3. Shape2Sql tool by Morten Nielsen: http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Fixing Invalid Geography Data
-- http://www.beginningspatial.com/fixing_invalid_geography_data
-- :::::::::::::::::::::::::::::::::::::::::::::::::::: 
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
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
CREATE TABLE dbo.[State] 
(
    StateShapeId INT IDENTITY(1, 1) NOT NULL,
    StateFips VARCHAR(2) NOT NULL,
    StateAbbrev  VARCHAR(2) NOT NULL, 
    StateName  VARCHAR(50) NOT NULL, 
    BoundaryGeog  GEOGRAPHY NOT NULL,
    MapSetId INT NOT NULL
    CONSTRAINT PK_State PRIMARY KEY (StateShapeId) 
); 


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



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



-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Display
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT  BoundaryGeog,
        StateAbbrev
FROM    dbo.[State]




-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Setup a radius to filter unwanted HI shapes
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT  BoundaryGeog
FROM    dbo.[State]

UNION ALL

SELECT  GEOGRAPHY::Parse('POINT(-107 28)').STBuffer(500000)



-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Filter Northwestern Hawaiian Islands 
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
UPDATE  dbo.[State]
SET     BoundaryGeog = GEOGRAPHY::Parse('POINT(-107 28)').STBuffer(500000).STIntersection(BoundaryGeog)
WHERE   StateAbbrev = 'HI'




-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Display
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT  BoundaryGeog,
        StateAbbrev
FROM    dbo.[State]


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

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Display
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT  BoundaryGeog,
        StateAbbrev
FROM    dbo.[State]


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

