How to use ArcGIS Pro with SQLite

LarsLarsen

As far as I can see there is no direct support for SQLLite in Pro. Is that a planned feature ? Can I access SQLite through phyton and import data as layers that way?

KimOllivier

It is true that sqlite is still not supported for interactive editing as at Pro 2.3 and it is also not supported for editing in ArcMap.

But don't let that put you off using sqlite and the spatial extensions combined with Esri tools. It is still very useful for lots of purposes. It can fill in holes in ArcTools and for some operations it is 100 times faster.

The way I use it is to keep the spatial tables (ie featureclasses) and aspatial tables (ie tables) in sqlite (with the spatial extension DLL built into Arc*) and run python scripts with SQL commands. You can do any (non interactive) editing with that method!

After a few seconds of processing I then copy the spatial tables back into a filegeodatabase if I need to use other tools that do not support sqlite. I can see the featureclasses and tables in ArcCatalog and they can usually be a read-only source of data.

The great benefit to exporting data as a sqlite database is that I can send it to a user without a GIS installation and they can easily read it with an ODBC driver.It is a single file database excellent for exchange. There are no size limits. Some of my databases are 6GB. Because they have good indexing they are way faster than Access ever was.  Instead of an awful CSV file you get a proper database table with a full schema, metadata, and the flexibility of a relational database.

Need the equivalent of Access?There are several open source equivalents available

I purchased sqlitepro  http://www.sqliteexpert.com

to test out my sql expressions.

What sort of tools? Consider if you have a many to one relate between owners and parcels. You would like a point layer of owners 'geocoded' by using the parcel  centroid. The obvious solution is to add a spatial column to Owners and populate it with parcel centroids from a relate to the parcels. This typically takes 2 minutes for 5 million records. This is not easy with ArcTools. MakeQueryLayer is not available for filegeodatabases, MakeQueryTable  is slow, does not scale well enough to complete. The only workaround I have found is to create a new empty Owner featureclass, copy all the data across with a cursor, then create a dictionary of geometry objects from the parcels and update the Owner featureclass. This takes some advanced python skills so I may as well write a SQL expression in spatialite (sqlite + mod spatialite)

sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN><SPAN class="string token">"UPDATE {0}
    SET shape = (SELECT shape
        FROM parcel.Parcel_Label B
        WHERE B.par_id = {0}.first_par_id)
        "</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">format</SPAN><SPAN class="punctuation token">(</SPAN>new_title<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

time 0:00:17.682000
count 2149376

This was as expected because it was a port from ARC/INFO and AML which took a similar time.