{"id":480,"date":"2012-06-21T13:50:00","date_gmt":"2012-06-21T13:50:00","guid":{"rendered":"\/blogs\/bobb\/post\/Loading-ShapeFiles-into-SQL-Server-2008-and-2012.aspx"},"modified":"2014-12-30T13:03:41","modified_gmt":"2014-12-30T21:03:41","slug":"loading-shapefiles-into-sql-server-2008-and-2012","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/","title":{"rendered":"Loading ShapeFiles into SQL Server 2008 and 2012"},"content":{"rendered":"

\nSince I find myself "in a spatial mood", I thought I'd write one more today. About importing data from shapefiles. According to Wikipedia<\/a> "The Esri shapefile or simply a shapefile is a popular geospatial vector data format for geographic information systems software". And the question always goes something like this: "Does SQL Server have anything built-in to import shapefiles?"\n<\/p>\n

\nShort answer is "no, not built in". There's some vendor products, like SAFE Software's FME series<\/a>, including an inexpensive consumer version called "Microsoft SQL Server Data Loader" as well as their well-known FME Desktop. And if you have ESRI software, there's likely a shapefile-to-SQL loader in there. But folks just starting out are likely to have heard of a free tool called Shape2SQL. It's not the only free tool out there, but its quite easy to use.\n<\/p>\n

\nOne problem that arises reasonably frequently when importing shapefiles into the GEOGRAPHY data-type is that SQL Server's GEOGRAPHY is sensitive to ring order. Many shapefile authors are not. This manifests itself as an error in Shape2SQL (you can skip the offending row(s) IIRC) because a GEOGRAPHY with the wrong ring order will be almost always look to SQL Server like its greater than a hemisphere. That's an error in SQL Server 2008\/2008 R2, but not in SQL Server 2012, where GEOGRAPHY instances of greater than a hemisphere are supported. You won't get the error (unless he's explicitly checking for larger than hemisphere, but I don't think that's the case) but you will have the wrong shape (e.g. instead of Iceland, you'll have everything that ISN'T Iceland!).\n<\/p>\n

\nIf you're sure that you shouldn't have any GEOGRAPHY instances of more than a hemisphere, here's an easy fix for SQL Server 2012.\n<\/p>\n

\n— assumes you are using SRID 4326 (default) and have a column named "Geog" in table named "spatialtable"
\ndeclare @h geography = 'FULLGLOBE';  — SRID 4326 is default\n<\/p>\n

\nupdate spatialtable
\nset Geog = Geog.ReorientObject()
\nwhere Geog.STArea() > @h.STArea() \/ 2;\n<\/p>\n

\nBTW, you can also use this method if you're doing a conversion from GEOMETRY (which doesn't care about ring order) to GEOGRAPHY and might have ring order problems. If you might<\/strong> have instances greater than a hemisphere, then you will have to "eyeball-check" (meaning, check the instance in the SSMS spatial results tab for reasonableness) instances on both sides of the hemisphere cutoff. If you're on SQL Server 2008, SAFE Software can automatically correct geographies greater than a hemisphere.\n<\/p>\n

\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

Since I find myself "in a spatial mood", I thought I'd write one more today. About importing data from shapefiles. According to Wikipedia "The Esri shapefile or simply a shapefile is a popular geospatial vector data format for geographic information systems software". And the question always goes something like this: "Does SQL Server have anything […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,31,36],"tags":[],"class_list":["post-480","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-2012","category-sql-server-spatial"],"yoast_head":"\nLoading ShapeFiles into SQL Server 2008 and 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-shapefiles-into-sql-server-2008-and-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Loading ShapeFiles into SQL Server 2008 and 2012 - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"Since I find myself "in a spatial mood", I thought I'd write one more today. About importing data from shapefiles. According to Wikipedia "The Esri shapefile or simply a shapefile is a popular geospatial vector data format for geographic information systems software". And the question always goes something like this: "Does SQL Server have anything […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-06-21T13:50:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-12-30T21:03:41+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=\"2 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-shapefiles-into-sql-server-2008-and-2012\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/\",\"name\":\"Loading ShapeFiles into SQL Server 2008 and 2012 - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-06-21T13:50:00+00:00\",\"dateModified\":\"2014-12-30T21:03:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2008\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Loading ShapeFiles into SQL Server 2008 and 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 ShapeFiles into SQL Server 2008 and 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-shapefiles-into-sql-server-2008-and-2012\/","og_locale":"en_US","og_type":"article","og_title":"Loading ShapeFiles into SQL Server 2008 and 2012 - Bob Beauchemin","og_description":"Since I find myself "in a spatial mood", I thought I'd write one more today. About importing data from shapefiles. According to Wikipedia "The Esri shapefile or simply a shapefile is a popular geospatial vector data format for geographic information systems software". And the question always goes something like this: "Does SQL Server have anything […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-06-21T13:50:00+00:00","article_modified_time":"2014-12-30T21:03:41+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/","name":"Loading ShapeFiles into SQL Server 2008 and 2012 - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-06-21T13:50:00+00:00","dateModified":"2014-12-30T21:03:41+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/loading-shapefiles-into-sql-server-2008-and-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2008","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/"},{"@type":"ListItem","position":3,"name":"Loading ShapeFiles into SQL Server 2008 and 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\/480","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=480"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/480\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=480"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}