SQLite Tables to File Geodatabase Feature Classes

Question

I have received an SQLite database that is a bit different than what I am used to. It seems that the data is in tables rather that "feature classes", see image below...

I was expecting more like the below for example...

At a guess, it seems that the data is in the first table and the the other two help define geometry and srs for that table.

My usage of SQLite is limited. Has anyone experienced the above and have a solution for me for how to extract as a feature class?

Cheers,

Glen

Clubdebambos · Answer

Thank you all for your replies.

I ended up using ArcPy. There is a field in the table called geom that is a "blob" type. I extracted the blob to bytes and used ArcPy to convert FromWKB which produces a Geometry object. I could then reconstruct the Feature Class in a File Geodatabase.

import arcpy

## path to SQLite Table
sqlite_fc = "path/to/sqlite/fc_name"

## Path to pre-prepared Feature Class
out_fc = "path/to/output_gdb/fc_name"

## open up an InsertCursor for the out_fc
with arcpy.da.InsertCursor(out_fc, ["name", "SHAPE@"]) as i_cursor:
    ## use the SearchCursor on the sqlite_fc
    with arcpy.da.SearchCursor(sqlite_fc, ["name", "geom"]) as s_cursor:
        ## for each record
        for row in s_cursor:
            ## get the blob and convert to Geometry object
            geom = arcpy.FromWKB(row[1].tobytes())
            ## insert into the out_fc
            i_cursor.insertRow([row[0], geom])

JoshuaBixby · Answer

If you are able to post any of the data, that would help community members provide more in-depth responses.  If you can't share any of the data, can you truncate the tables and at least share an empty SQLite package that has the schema?

Luke_Pinner · Answer

That SQLite db looks like a spatialite DB which is something GDAL/OGR can read.

The arcgispro-py3 env includes the GDAL/OGR (osgeo.gdal / osgeo.ogr) library, so may have the GDAL commandline tools (I'm not at my work computer, so can't check).

If you have og2ogr available, you could convert the SQLite DB to a File GeoDatabase with something like (untested):

ogr2ogr -of OpenFileGDB output.gdb input.sqlite