Feature Class export into native SQL Server table

Not too long ago, the only way to store spatial data inside a database was to have a custom/binary format managed in code or by software (such as ArcSDE). There have been huge strides forward in the database world - with many platforms now incorporating native spatial types. Oracle Spatial, SQL Server’s Geometry/Geography, to name just a few. These spatial types might still have some limitations when using in combination with GIS software, but it is now much easier to ETL this data into native formats for other use.

You can have the best of both worlds as well: Using native spatial types and still leveraging Geodatabase objects - however this post deals with extracting data and loading into a native table/spatial type only. You can use ArcCatalog for the Geodatabase stuff.

Scenario

Have a [File|ArcSDE|Access] Geodatabase Feature Class and want to load into a new SQL Server table with the Geography spatial type using Python. In this scenario it will be a polygon layer, but the example can be expanded to allow other feature types as well.

Steps

Create the SQL Server table. In this example I used SQL’s Enterprise Manager to create a table named SpatialTable with 2 fields, one for a unique ID and one to store the Geography:

CREATE TABLE SpatialTable ( id int IDENTITY (1,1), GeogCol1 geography );  
GO

Read the features and load into SQL Server using python. A python script leveraging ArcPy’s data access module is used to cursor through the features while exposing the shape/geometry in the well-known text (WKT) representation for OGC geometry (usable by SQL Server). This is one line of code using ArcPy! Next, to load into SQL Server, a Python module called pyodbc is used to connect to the corresponding database.

Note: SHAPE@WKT tokens were made available at ArcGIS 10.1 Service Pack 1

You now have your data stored in a native table using the Geography format.