Skip to content

Instantly share code, notes, and snippets.

@AlexArcPy
Created December 17, 2016 14:19
Show Gist options
  • Save AlexArcPy/2f9ac003e3623a6ccf33560a99bd28bc to your computer and use it in GitHub Desktop.
Save AlexArcPy/2f9ac003e3623a6ccf33560a99bd28bc to your computer and use it in GitHub Desktop.
Load Esri file geodatabase feature classes into SQLite spatial table
from __future__ import print_function
import sys
print(sys.version)
print(sys.executable)
import os
import sqlite3
import arcpy
from time import strftime
import time
from functools import wraps
def report_time(func):
'''Decorator reporting the execution time'''
@wraps(func)
def wrapper(*args, **kwargs):
start = time.time()
result = func(*args, **kwargs)
end = time.time()
print(func.__name__, round(end-start,3))
return result
return wrapper
#----------------------------------------------------------------------
def enable_spatial(conn):
#allow loading extensions
conn.enable_load_extension(True)
#loading the ST_Geometry dll to support Esri ST_Geometry geometry type
dll_path = r'c:\Program Files (x86)\ArcGIS\Desktop10.4\DatabaseSupport\SQLite\Windows32\stgeometry_sqlite.dll'
run('''SELECT load_extension('{dll_path}','SDE_SQL_funcs_init');'''.format(dll_path=dll_path))
run('''SELECT CreateOGCTables();''')
conn.commit()
return conn
#----------------------------------------------------------------------
def run(SQL=None):
return conn.execute(SQL).fetchall()
@report_time
#----------------------------------------------------------------------
def fc_to_sql(conn,source_fc):
'''load a gdb feature class into a sqlite table with ST_Geometry type'''
conn = enable_spatial(conn)
#getting info about the source feature class
source_epsg = arcpy.Describe(source_fc).spatialReference.factoryCode
source_basename = arcpy.Describe(source_fc).baseName
source_shapetype = arcpy.Describe(source_fc).shapeType
source_oid = arcpy.Describe(source_fc).OIDFieldName
#map field types from arcpy.ListFields() to sqlite, based on
#http://desktop.arcgis.com/en/arcmap/10.3/manage-data/databases/dbms-data-types-supported.htm#ESRI_SECTION1_E1310ADFB340464485BA2D2D167C9AE4
arcgis_sqlite_types_mappings = {'Date':'realdate','Double':'float64','Single':'float64',
'Integer':'int32','SmallInteger':'int16','String':'text',
'OID':'int32'}
arcgis_sqlite_geometry_mappings = {'Polyline':'MultiLinestring','Point':'Point',
'Multipoint':'Multipoint','Polygon':'MultiPolygon'}
geometry_columns = ('shape','shape_area','shape_length')
#use SQL to create a table
columns = ['{} {}'.format(field.name,arcgis_sqlite_types_mappings[field.type])
for field in arcpy.ListFields(source_fc) if field.name.lower() not in
geometry_columns]
#creating the table (with all columns except the geometry column)
run('''CREATE TABLE {table} ({columns});'''.format(table=source_basename,
columns=','.join(columns)))
#adding the Shape column
shape_type = arcgis_sqlite_geometry_mappings[source_shapetype]
sql_add_geom_col = '''SELECT AddGeometryColumn(null,'{table}','Shape',{epsg_code},'{shape_type}','xy','null');'''
run(sql_add_geom_col.format(table=source_basename,
epsg_code=source_epsg,
shape_type=shape_type))
#getting a list of column names to store the data
data_columns = [str(field.name) for field in arcpy.ListFields(source_fc)
if field.name.lower() not in geometry_columns]
#creating a list of data rows (all attributes plus WKT of Shape)
rows = (r for r in arcpy.da.SearchCursor(source_fc,data_columns+['SHAPE@WKT']))
#insert attribute values into the SQL table
shape_type='st_{}'.format(arcgis_sqlite_geometry_mappings[source_shapetype])
insert_values = ','.join(['?' for i in xrange(len(data_columns))] +
['{shape_type}(?,{epsg})'.format(shape_type=shape_type,
epsg=source_epsg)])
sql_insert_rows = '''Insert into {table_name} ({columns}) values ({insert_values})'''
conn.executemany(sql_insert_rows.format(table_name=source_basename,
columns=','.join(data_columns+['Shape']),
insert_values=insert_values),rows)
conn.commit()
return conn
#----------------------------------------------------------------------
#create new sqlite database file
conn = sqlite3.connect(r'C:\GIS\_sqlites\SpatialDb{}.sqlite'.format(strftime("%Y%m%d_%H%M%S")))
#run loading data on feature classes of various size
test_fcs = [r'C:\GIS\Data.gdb\FeaturesCount1K',
r'C:\GIS\Data.gdb\FeaturesCount10K',
r'C:\GIS\Data.gdb\FeaturesCount100K',
r'C:\GIS\Data.gdb\FeaturesCount1000K']
for fc in test_fcs:
#load the fc into sqlite
print(fc.split('\\')[-1])
conn = fc_to_sql(conn, fc)
#get some spatial data from tables
print(run('''select {OID},ST_ASTEXT(SHAPE) from {table} LIMIT 1'''.format(OID=arcpy.Describe(fc).OIDFieldName,
table=arcpy.Describe(fc).baseName)))
#FeaturesCount1K
#fc_to_sql 3.387
#[(1, u'POINT ( 25.698913574 174.868286133)')]
#FeaturesCount10K
#fc_to_sql 1.239
#[(1, u'POINT ( 160.473083496 199.991882324)')]
#FeaturesCount100K
#fc_to_sql 4.116
#[(1, u'POINT ( 20.019287109 194.078674316)')]
#FeaturesCount1000K
#fc_to_sql 32.519
#[(1, u'POINT ( 136.532104492 70.4584960940001)')]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment