topic Re: SQLite Database to Feature Class in Data Management Questions

SQLite Database to Feature Class

RyanFortier — Wed, 19 Nov 2014 15:19:23 GMT

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.

Re: SQLite Database to Feature Class

XanderBakker — Sat, 02 May 2015 05:50:26 GMT

Did you look at this Help page? ArcGIS Help (10.2, 10.2.1, and 10.2.2)

Re: SQLite Database to Feature Class

LanceShipman — Tue, 05 May 2015 20:13:10 GMT

What format is the polygon data? SpatiaLite or?

Re: SQLite Database to Feature Class

FilipKrál — Sat, 11 Dec 2021 19:37:56 GMT

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\to\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\to\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.

Re: SQLite Database to Feature Class

FilipKrál — Sat, 11 Dec 2021 19:37:59 GMT

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\to\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\to\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.