a variety of products <\/a> for SQL Server, including a low-priced option that more than suffices for loading shapefiles. But what if you can't afford even Safe's low-priced option (you're doing it for proof-of-concept, for example). I'd then recommend Shape2SQL, a free tool that's been around since SQL Server Spatial's inception back in SQL Server 2008.\n<\/p>\n\nThe first time I'd loaded these shapefiles let's use the cntry00 (countries) file as an example, I'd run into some problems loading them as geography data type and had to bypass some records in the shapefile, leaving me with a map that was missing some countries. I'd always assumed that this was a ring-order problem that produced geographies large than a hemisphere. SQL Server Spatial in 2012 fixed the "larger than a hemisphere" limitation, so I thought I'd try loading as a geography again. Unfortunately I got the same errors. So I decided to reload the data as geometry, cleanse the data and convert to geography. What follows is a series of steps to do just that. It relies on some SQL Server 2012-specific functionality.\n<\/p>\n
\nAfter trying to summarize this on Twitter as 140-character messages (I always refer to Twitter as "my 140-character lobotomy") someone suggested this would be a good blog entry. So here it is.\n<\/p>\n
\nSo, acquire the shapefile data. What you're looking for is a .shp file (input into Shape2SQL) and possibly the .prj file to determine the SRID to use. In this case, I'll go with SRID 4326 eventually for geography. You can use an SRID with geometry too, but it doesn't effect the spatial library's computation like it does with geography.\n<\/p>\n
\n1. Unzip the zipfile
\n2. Pointed Shape2SQL at the .shp file choosing the following options:
\n a. Planar Geometry
\n b. Set SRID 4326
\n c. Create spatial index – no (checkbox cleared)
\n d. spatial column name – geom (default)\n<\/p>\n
\nNow all the data will load. So let's run some queries to clean things up and convert to geography.\n<\/p>\n
\n— First, look for Invalid geometries and get the reasons why they are invalid
\n— IsValidDetailed is SQL Server 2012-specific
\n— BTW, it wasn't a ring order problem after all.\n<\/p>\n
\n— Error: 24411
\nselect geom.IsValidDetailed(), * from cntry00
\nwhere geom.STIsValid() = 0;\n<\/p>\n
\n— Make them valid, noting the row ids for your reference
\n— rows 21, 42, 252
\nupdate cntry00
\nset geom = geom.MakeValid()
\noutput inserted.id
\nwhere geom.STIsValid() = 0;\n<\/p>\n
\n— add geography column
\nalter table cntry00 add geog geography;\n<\/p>\n
\n— vacuous conversion to geography
\nupdate cntry00
\nset geog = geography::STGeomFromWKB(geom.STAsBinary(), 4326);\n<\/p>\n
\n— see if any invalid now… and the reason
\n— 24413, 24414 near the poles (3 rows)
\nselect geog.IsValidDetailed(), * from cntry00
\nwhere geog.STIsValid() = 0;\n<\/p>\n
\n— validate again
\n— rows 1,181,215
\nupdate cntry00
\nset geog = geog.MakeValid()
\noutput inserted.id
\nwhere geog.STIsValid() = 0;\n<\/p>\n
\n— check for more than a hemisphere, none found
\nselect * from cntry00
\nwhere geog.EnvelopeAngle() > 90;\n<\/p>\n
\n— if there are geographies greater than a hemisphere
\n— because of ring order problems fix 'em
\n— SQL Server 2012-specific
\nupdate cntry00
\n set geog = geog.ReorientObject()
\n output inserted.id
\n where geog.EnvelopeAngle() > 90;\n<\/p>\n
\nAnd there you have it. You might think that you can use the spatial results tab in SSMS to compare your data before and after validation to see if validation has left artifacts or changed the shape. Don't bother, though; the spatial results tab called MakeValid before displaying the data.\n<\/p>\n
\nHope this helps,
\nBob\n<\/p>\n
\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"
Someone asked, on Twitter, if it were possible to get a set of spatial data that included cities, counties (administrative regions) and countries in database table format for SQL Server spatial. I'd come across just these datasets (and more) as shapefiles at the CDC website and pointed them out. Then came the question about how […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,36],"tags":[],"class_list":["post-463","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sql-server-spatial"],"yoast_head":"\n
Loading spatial data and cleansing-converting with SQL Server 2012 - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Loading spatial data and cleansing-converting with SQL Server 2012 - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"Someone asked, on Twitter, if it were possible to get a set of spatial data that included cities, counties (administrative regions) and countries in database table format for SQL Server spatial. I'd come across just these datasets (and more) as shapefiles at the CDC website and pointed them out. Then came the question about how […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-11-29T14:27:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-12-30T21:03:44+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/\",\"name\":\"Loading spatial data and cleansing-converting with SQL Server 2012 - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-11-29T14:27:00+00:00\",\"dateModified\":\"2014-12-30T21:03:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2012\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Loading spatial data and cleansing-converting with SQL Server 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Loading spatial data and cleansing-converting with SQL Server 2012 - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/","og_locale":"en_US","og_type":"article","og_title":"Loading spatial data and cleansing-converting with SQL Server 2012 - Bob Beauchemin","og_description":"Someone asked, on Twitter, if it were possible to get a set of spatial data that included cities, counties (administrative regions) and countries in database table format for SQL Server spatial. I'd come across just these datasets (and more) as shapefiles at the CDC website and pointed them out. Then came the question about how […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-11-29T14:27:00+00:00","article_modified_time":"2014-12-30T21:03:44+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/","name":"Loading spatial data and cleansing-converting with SQL Server 2012 - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-11-29T14:27:00+00:00","dateModified":"2014-12-30T21:03:44+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-spatial-data-and-cleansing-converting-with-sql-server-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2012","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/"},{"@type":"ListItem","position":3,"name":"Loading spatial data and cleansing-converting with SQL Server 2012"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/463","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=463"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/463\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}