SQLite Database to Feature Class

5293
4
11-19-2014 07:19 AM
Highlighted
New Contributor III

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.

Tags (1)
Reply
0 Kudos
4 Replies
Highlighted
Esri Esteemed Contributor

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

Reply
0 Kudos
Highlighted
Regular Contributor

What format is the polygon data? SpatiaLite or?

Reply
0 Kudos
Highlighted
Occasional Contributor III

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.

Reply
0 Kudos
Highlighted
Occasional Contributor III

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.