SQLite Database to Feature Class

Question

Hello,

I am somewhat new to programming and the SQLite world. I am looking for a way to take data from and SQLite DB and populate feature classes. The feature classes are all polygons and the intent is to get their shape from the Well Known Text field within the SQLite DB. I also need to populate two other fields in the feature class where the data is coming from the SQLite DB.

Any help would be appreciated.

FilipKrál · Answer

Oh, today it turned out that my suggestion above actually does not work because the pure sqlite3 package does not understand the AsText function used in the sql query. One would have to use the pyspatialite package instead. Like this:

import arcpy, os
from pyspatialite import dbapi2 as db
from contextlib import closing  
  
# create spatial reference from well known ID  
wkid = 4326 # you need to know the well known ID  
sr = arcpy.SpatialReference(wkid)  
  
# create feature class and add columns  
fc= r'c:\path	o\file.gdb\featureclass'  
fc = arcpy.management.CreateFeatureClass(os.path.dirname(fc), os.path.basename(fc), "POLYGON", spatial_reference=sr).getOutput(0)  
arcpy.management.AddField(fc, "col1", "LONG")  
arcpy.management.AddField(fc, "col2", "TEXT", field_length=100)  
  
# insert rows from sqlite to the feature class  
with arcpy.da.InsertCursor(fc, ["SHAPE@", "col1", "col2"]) as ic:  
    with closing(db.connect(r'c:\path	o\database.sqlite')) as conn:  
      cr = conn.cursor()  
      for row in cr.execute("select AsText(geom), col1, col2 from mytable;"):  
              shape = arcpy.FromWKT(row[0], wkid)  
              ic.insertRow([shape] + row[1:])  
      del cr  
      del ic

Filip.

FilipKrál · Answer

Hi Ryan,

Xander Bakker​ points to a good place where it is also explained that you can connect to sqlite database directly from ArcGIS (from about 10.2 above). Once you establish connection I imagine you could simply use the Copy Features tool.

If for some reason you want to do it in a different way, something like arcpy.FromWKT​ should help. Say you have an SQLite table with a polygon column 'geom' and other integer column 'col1' and varchar column 'col2'. I would try something like this:

import arcpy, sqlite3, os
from contextlib import closing

# create spatial reference from well known ID
wkid = 4326 # you need to know the well known ID
sr = arcpy.SpatialReference(wkid)

# create feature class and add columns
fc= r'c:\path	o\file.gdb\featureclass'
fc = arcpy.management.CreateFeatureClass(os.path.dirname(fc), os.path.basename(fc), "POLYGON", spatial_reference=sr).getOutput(0)
arcpy.management.AddField(fc, "col1", "LONG")
arcpy.management.AddField(fc, "col2", "TEXT", field_length=100)

# insert rows from sqlite to the feature class
with arcpy.da.InsertCursor(fc, ["SHAPE@", "col1", "col2"]) as ic:
    with closing(sqlite3.connect(r'c:\path	o\database.db')) as conn:
      cr = conn.cursor()
      for row in cr.execute("select AsText(geom), col1, col2 from mytable;"):
              shape = arcpy.FromWKT(row[0], wkid)
              ic.insertRow([shape] + row[1:])
      del cr
      del ic

You'll probably need to tweak this because I haven't tested this really but I hope it gives you some pointers.

Let us know how you get on. I'd quite like to know what works best for you in the end.

Filip.

LanceShipman · Answer

What format is the polygon data? SpatiaLite or?