shrug-l: SHP to SQL 2008 via python - Follow-up

Keith Sandell Keith.Sandell at citizensfla.com
Wed Apr 20 10:48:26 EDT 2011


In my haste to share my excitement I failed to mention that I do intend
to rework the solution into something more manageable by the masses, but
it probably will not happen until the June/July timeframe. 

 

As a side note, the project for which this was developed has given me
some good ideas for presentation at the SHRUG conference, if others are
interested:

 

"Reading/writing geometries into SQL 2008 Spatial Types"

 

"Automating SQL workflows with Python"

 

And probably some more as I progress through the project.

 

Keith Sandell, MBA

GIS Manager, Corporate Analytics

Citizens Property Insurance Corporation

-------------------------------------------------------

ofc. 850.521.8341   |   cell 850.727.2897

 

From: shrug-l-bounces at lists.dep.state.fl.us
[mailto:shrug-l-bounces at lists.dep.state.fl.us] On Behalf Of Keith
Sandell
Sent: Sunday, April 17, 2011 6:39 PM
To: shrug-l at lists.dep.state.fl.us
Subject: shrug-l: SHP to SQL 2008 via python

 

Hey Shruggers,

 

Got something that, well from my perspective is just too cool not to
share.

 

I've been working towards a solution that captures the National
Hurricane Center event shapefiles and ultimately commits them to SQL
2008 so that I can leverage the best parts of the ArcGIS Suite and SQL
2008.

 

The biggest obstacle I had was how to get the shapefiles into SQL 2008
as part of an automated workflow. There are a number of tools that have
been created, i.e. Shape2Sql by Morton Nielsen, but I needed more
control over how the features get committed.

 

So I started pounding away with Python and did it. Took me a couple of
days and a lot of trial and mostly error, but attached is a txt file
that contains the python code to read the geometries of a polygon file
that has multipart polygons with interior rings and write the geometries
into a well formatted text string that can be used to insert the feature
into SQL 2008. Doing the same for simple polygons, lines and points
follows the same theme.

 

This script is specifically geared towards the Wind Speed Probability
shapefiles which I would argue have some of the most complex and/or
complicated geometries I have seen. It is also only part of an overall
workflow there is more before and after this script, and I still need to
extend this script a little bit more, but the overall theme is there.

 

This workflow:

1)      For the feature classes in the workspace it selects them based
on expected name characteristics, i.e. the wind speed so that the proper
shapefile is committed to the correct SQL table.

2)      Reads the geometries

3)      Writes the geometries into the well formatted text that SQL
needs

4)      Commits the feature to SQL 2008's spatial type MULTIPOLYGON

 

For those of you interested in following this path keep a couple of
things in mind:

 

1)      I used pyodbc, google to find the download, to create my DB
connection, versions are dependent on the version of python installed.
Make sure you get the right one.

2)      One thing you may notice in the script is that I had to reverse
the order of the vertices that were provided by the arcpy package. This
is because SQL writes its geometries backwards for polygons when
compared to ESRI. Something that I found was confirmed on a blog by
Morton Nielsen.

3)      SQL 2008 will only accept geometries that fit within 1
hemisphere, which typically isn't a problem, but when dealing with wind
speed probability shapefiles that encompass the entire world a problem
is created. Good thing I only care about the Atlantic/Gulf, so I just
clip out the part I want before gobbling up the geometries.

 

I know that the techniques to accomplish the goal are not written in a
very sophisticated manner, as a matter of fact it is pretty low rent
code, and they could definitely benefit from some gains in efficiency,
but it works none the less and demonstrates the technique. To give you
an example of what this script does I have also attached the string for
"1" feature in "1" of the wind speed probability shapefiles. 

 

With this script I was able to load into SQL three wind speed
probability shapefiles that had a total of 1,741 features like the one
attached in 2 minutes 8 seconds. Might sound kind of slow, but when you
consider the complexity and number of features it starts to seem quite
reasonable and maybe even a little speedy, especially if it needs to be
integrated with a workflow.

 

I hope this can help someone.  

 

Keith Sandell, MBA

GIS Manager, Corporate Analytics

Citizens Property Insurance Corporation

2101 Maryland Circle, Tallahassee, Florida 32303

-------------------------------------------------------

ofc. 850.521.8341   |   cell 850.727.2897

 

"If we are to achieve results never before accomplished we must expect
to employ methods never before attempted." - Francis Bacon

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.dep.state.fl.us/pipermail/shrug-l/attachments/20110420/2e81e78c/attachment.htm


More information about the SHRUG-L mailing list