Census 2010 Blocks in SQL Server 2008 Geography Spatial Type

Loading census data into SQL Server’s Geography Spatial Type should have been very straightforward.  Simply download the shapefiles from the census ftp site in batch mode, unzip, then import to a SDE feature class with the geometry storage type set as “GEOGRAPHY”. 

Unfortunately, I was not that lucky.  During the import process with ArcCatalog, the 8+ million blocks failed after loading around 2.5 million records with a variation of the following error:

Msg 6522, Level 16, State 1, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

System.ArgumentException: 24200: The specified input does not represent a valid geography instance.

System.ArgumentException:

   at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)

   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

.

Translation: There was some feature in the data that had a valid shape according to esri, but not Microsoft. 

There are some differences between how esri and Microsoft want the polygon shapes to be drawn, specifically with the order in which the vertices are drawn (clockwise vs counterclockwise) for the exterior (outer boundary) ring(s) and interior (donut hole) ring(s). The first attempt was to create a button in ArcMap which read the geometry from the SDEBINARY geodatabase, created the Well Known Text and then geography for each feature (accounting for the correct draw orientation), and then pushed the results into a SQL Server table using ADO.net.   

The geometry creation code looks something like this:

Dim geo As New SqlGeometry()

geo = Microsoft.SqlServer.Types.SqlGeometry.STGeomFromText(New SqlChars(New SqlString(myshapecalc)), 4326).MakeValid

mynewrow("Shape") = geo.MakeValid

I was fairly happy with my first results, until I remembered I wasn’t dealing with multipart geometry, empty geometry, multiple interior rings, and other geo quirks.  After some quick modifications, the code was done and the results were populated, however I still hadnt solved the problem of getting my data into GEOGRAPHY.  When I modified the code to pump GEOGRAPHY not GEOMETRY, I had the same failure.  Did a great job of solving the wrong problem – at least now I have a esri to SQL Geometry table loader. 

I was definitely surprised to realize that you can in fact push unprojected data into the geometry data type, using the SRID from a geographic coordinate system.  I am not really sure why this is not an acceptable approach, other than geography is typically for Lat / Long coordinates, and geometry is for feet / meters units.  There are definitely stronger limitations on what can be pushed into each – geography being much more stringent.  One we realized this, I pushed all the polygons (WGS 84 projection) into a geometry dataset using ArcCatalog, then tried to convert the data to geography from there. 

One recommendation we found was to use the geometry STUnion and MakeValid functions to try to fix the shape’s validity from a geography perspective, which can be seen here:

geography::STGeomFromText(shape.MakeValid().STUnion(shape.STStartPoint()).STAsText(),4326)

This is taking the source polygon, running the MakeValid() operation, then unioning the polygon with it’s first vertex, and finally converting the result to geography.  Doing so re-orders the vertices so that they are drawn in the correct order. 

Using this query, I was able to start pumping Geography records, unfortunately we still found that the conversion failed. 

At this point, I needed to isolate the record or records that were causing the process to fail, so I created this query:

declare @objectid int

Declare rowcursor CURSOR FAST_FORWARD FOR

select objectid

from dbo.CENSUS_2010_TABBLOCK_GEOMETRY_IMPORT2

order by objectid

OPEN rowcursor

FETCH NEXT FROM rowcursor

INTO @objectid

WHILE @@FETCH_STATUS = 0

BEGIN

declare @sqlinsert varchar(max)

set @sqlinsert = '

insert into

dbo.CENSUS_2010_TABBLOCK_GEOGRAPHY

select objectid, blkidfp00,

geography::STGeomFromText(shape.MakeValid().STUnion(shape.STStartPoint()).STAsText(),4326)

from

dbo.CENSUS_2010_TABBLOCK_GEOMETRY_IMPORT2

where objectid = ' + convert(varchar(255),@objectid)

begin try

exec(@sqlinsert)

end try

begin catch

      insert into geography_failures

      select @objectid

end catch

FETCH NEXT FROM rowcursor

       INTO @objectid

END

CLOSE rowcursor

DEALLOCATE rowcursor

Here, I am taking all input records, iteration thru each, attempting to push the input row into a geography table, and where that doesn’t work, dumping the result into an error table.  This was very slow to run (1 day 21 hours), but it ended up showing me the feature that was causing the problem:

POLYGON ((-73.539727170912329 41.134362625800748, -73.534610028565623 41.142645381690613, -73.538288255665464 41.136691869856634, -73.539808109894977 41.1357116088447, -73.540023947182021 41.134416585122494, -73.53999696752112 41.134416585122494, -73.539727170912329 41.134362625800748))

On a map, a pretty standard polygon with a pretty ugly and unneeded set of vertices shooting off in one direction. 

Unfortunately, there is not really a clean way of handling this shape, as far as I can determine, other than to manipulate the shape coordinates, and manually load this single feature into the table.  

In the end, we have learned there are some issues with the census 2010 geometry, including gaps, overlaps, and funky topological discrepancies.  We also learned that it can be a major hassle with significant roadblocks to try to ditch esri formats when performing spatial operations inside the SQL database.  Finally, we re-learned during QA that esri handles cluster tolerance and spatial resolution, whereas SQL does not.  In this instance, esri was giving bad results along some polygon borders, whereas SQL was giving correct polygon association every time. 

But, this effort is definitely worth it.  With proper spatial indexing and other logic, I was able to assign census blocks to 128 million input records in about 6 hours, which is way faster than any other esri options.  And we have some very handy reusable code for the future. 

SQL Server Database Notifications

Oftentimes I am stuck wondering if a database query has completed, or a certain amount of progress on a multi-step query has been made.  I can always backtrack after the query has completed by adding print statements to my lengthy batch files to see how each section performs:

print 'Step 1 complete... ' + convert(varchar(255),getdate())

… however, the downside is that the messages don’t appear until the query has fully completed, which doesn’t help that much. 

I have found two workable options for getting notification of a SQL Server Process Status, both during a process and upon completion. 

Growl for Windows

The first option is using Growl for Windows+ xp_cmdshell

From the Growl site:

Put simply, Growl lets you know when things happen. Files finished downloading, friends came online, new email has arrived - Growl can let you know when any event occurs with a subtle notification.

The version for Mac appears to be much more complete in terms of hooks to COTS software, but with some simple configuration work I can get what I want. 

After installing the main software package, download the command line program as well:

GrowlNotify Command Line Script

Now, all we have to do is create a command line string and execute it (just as easily with command prompt, shell in VB etc.).  The query below contains the full syntax for wrapping a Growl notify command for use in xp_cmdshell, which is a great way to run command line statements from SQL Server Management Studio.  

Once xp_cmdshell is enabled, simply copy and paste the code below into a query window:

declare @myalert varchar(max)

set @myalert = 'D:\workspace\code_checkouts\growl\growlnotify /t:Hey_There /s:true /host:localhost /p:2 "Query Complete\n ' + convert(varchar(255),getdate())

declare @mysql varchar(max)

set @mysql = 'EXEC xp_cmdshell ''' +@myalert + ''''

exec (@mysql)

And you will get a notification window popup:

This is useful when I am at my workstation, but I also want to receive an email if I am away to save me the energy of logging in every couple hours to check progress. 

Configuring Growl to use email is fairly easy once you know the settings.  For Gmail account settings:

Then enable the email notification for growlnotify:

Now, in addition to a popup, I get an email in my inbox:

SQL Server Database Mail

Another, probably better, option is using the built in SQL Server 2008 functionality called Database Mail. 

This excellent walkthrough explains the whole concept:

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

I used this query to enable the functionality in SSMS:

GO

RECONFIGURE with OVERRIDE

GO

sp_CONFIGURE 'Database Mail XPs', 1

GO

RECONFIGURE with OVERRIDE

GO

These are the Gmail settings for Database Mail:

Once the settings are configured, you need to know how to construct the command - this is detailed syntax page from MSDN:

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Finally, I used this to send the email:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'RoyJackson',

    @recipients = 'roy.jackson@gdr.com',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Automated Success Message' ;

   

And this to see if it worked – if the message failed you will see an error row and some details as to the status:

select * from msdb.dbo.sysmail_allitems

Next stop is using Growl within some other programs such as ArcGIS / python…

Hope this is useful!