<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" xmlns:Repl="http://schemas.microsoft.com/repl/" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda="http://www.passport.com/NameSpace.xsd" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:444544556;
mso-list-type:hybrid;
mso-list-template-ids:481735282 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level4
{mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level7
{mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1
{mso-list-id:471289759;
mso-list-type:hybrid;
mso-list-template-ids:2064689910 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l1:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level2
{mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level3
{mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level4
{mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level5
{mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level6
{mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level7
{mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level8
{mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level9
{mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='color:#1F497D'>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. <o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>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:<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>“Reading/writing
geometries into SQL 2008 Spatial Types”<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>“Automating SQL workflows
with Python”<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>And probably some more as I
progress through the project.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<p class=MsoNormal><b><span style='font-family:"Times New Roman","serif";
color:#1F497D'>Keith Sandell, MBA<o:p></o:p></span></b></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";
font-variant:small-caps;color:gray'>GIS Manager, Corporate Analytics<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";
font-variant:small-caps;color:gray'>Citizens Property Insurance Corporation<o:p></o:p></span></p>
<p class=MsoNormal style='line-height:5.0pt;mso-line-height-rule:exactly'><span
style='font-size:10.0pt;font-family:"Times New Roman","serif";color:gray'>-------------------------------------------------------<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";
color:gray'>ofc. 850.521.8341 | cell 850.727.2897</span><span
style='color:#1F497D'><o:p></o:p></span></p>
</div>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
shrug-l-bounces@lists.dep.state.fl.us
[mailto:shrug-l-bounces@lists.dep.state.fl.us] <b>On Behalf Of </b>Keith
Sandell<br>
<b>Sent:</b> Sunday, April 17, 2011 6:39 PM<br>
<b>To:</b> shrug-l@lists.dep.state.fl.us<br>
<b>Subject:</b> shrug-l: SHP to SQL 2008 via python<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Hey Shruggers,<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Got something that, well from my perspective is just too
cool not to share.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>This workflow:<o:p></o:p></p>
<p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo2'><![if !supportLists]><span
style='mso-list:Ignore'>1)<span style='font:7.0pt "Times New Roman"'>
</span></span><![endif]>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.<o:p></o:p></p>
<p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo2'><![if !supportLists]><span
style='mso-list:Ignore'>2)<span style='font:7.0pt "Times New Roman"'>
</span></span><![endif]>Reads the geometries<o:p></o:p></p>
<p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo2'><![if !supportLists]><span
style='mso-list:Ignore'>3)<span style='font:7.0pt "Times New Roman"'>
</span></span><![endif]>Writes the geometries into the well formatted text that
SQL needs<o:p></o:p></p>
<p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo2'><![if !supportLists]><span
style='mso-list:Ignore'>4)<span style='font:7.0pt "Times New Roman"'>
</span></span><![endif]>Commits the feature to SQL 2008’s spatial type
MULTIPOLYGON<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>For those of you interested in following this path keep a
couple of things in mind:<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo4'><![if !supportLists]><span
style='mso-list:Ignore'>1)<span style='font:7.0pt "Times New Roman"'>
</span></span><![endif]>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.<o:p></o:p></p>
<p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo4'><![if !supportLists]><span
style='mso-list:Ignore'>2)<span style='font:7.0pt "Times New Roman"'>
</span></span><![endif]>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.<o:p></o:p></p>
<p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo4'><![if !supportLists]><span
style='mso-list:Ignore'>3)<span style='font:7.0pt "Times New Roman"'>
</span></span><![endif]>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.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>I hope this can help someone. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><b><span style='font-family:"Times New Roman","serif"'>Keith
Sandell, MBA<o:p></o:p></span></b></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";
font-variant:small-caps;color:gray'>GIS Manager, Corporate Analytics<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";
font-variant:small-caps;color:gray'>Citizens Property Insurance Corporation<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";
font-variant:small-caps;color:gray'>2101 Maryland Circle, Tallahassee, Florida
32303<o:p></o:p></span></p>
<p class=MsoNormal style='line-height:5.0pt;mso-line-height-rule:exactly'><span
style='font-size:10.0pt;font-family:"Times New Roman","serif";color:gray'>-------------------------------------------------------<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";
color:gray'>ofc. 850.521.8341 | cell 850.727.2897</span><span
style='font-size:10.0pt;color:gray'><o:p></o:p></span></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal align=center style='text-align:center'><i><span
style='font-size:7.5pt;font-family:"Arial","sans-serif"'>"If we are to
achieve results never before accomplished we must expect to employ methods
never before attempted." - Francis Bacon</span></i><o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</body>
</html>