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

  1. 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
  1. 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

#Requires: Esri's ArcPy.da module and PyODBC module
#Note: Search for <VARIABLE> to replace with new values, and update inputs
import arcpy, pyodbc
def fn_ReadPolygons(fc_in,wkid):
#This function loops through a polygon layer and converts geometry to WKT format
#Additional attributes can also be added for insert
#The input layer and WKID of the Geography type is required
for row in arcpy.da.SearchCursor(fc_in, ["SHAPE@WKT"]):
sqlInsertStatement = "INSERT INTO SpatialTable (GeogCol1) VALUES (geography::STMPolyFromText("
sqlInsertStatement += "'" + row[0] + "'," + wkid + "))"
fn_SqlStatement(sqlInsertStatement, "Load Polygon Statement")
#End function - fn_ReadPolygons
def fn_SqlStatement(sqlStatement, sqlPurpose):
#This function connects to SQL Server and runs the SQL commands
#The full SQL Statement is passed to the function, along with a brief description for error checking
try:
cnxn_SQL = pyodbc.connect("DRIVER={SQL Server};SERVER=<VARIABLE>;DATABASE=<VARIABLE>;UID=<VARIABLE>;PWD=<VARIABLE>")
cursor_SQL = cnxn_SQL.cursor()
cursor_SQL.execute(sqlStatement)
cnxn_SQL.commit()
cnxn_SQL.close()
except:
print 'error in fn_SqlStatement: ' + sqlPurpose
#End function - fn_SqlStatement
#MAIN CODE#
#Call function with a File Geodatabase polygon layer in WGS84(4326).
fn_ReadPolygons(r"C:\TestGeodatabase.gdb\PolygonFC","4326")
view raw fGDB2SQL.py hosted with ❤ by GitHub

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

More information