topic How do I use Spatialite400.dll? in ArcGIS Pro SDK Questions

How do I use Spatialite400.dll?

KirkKuykendall1 — Sun, 10 Jan 2021 18:33:16 GMT

Hi -

I was pleased to find "C:\Program Files\ArcGIS\Pro\bin\spatialite400x.dll". (I wasn't expecting it since there's no mention of spatialite in Esri's 3rd Party Acknowledgements doc.)

I'm able to load it as an extension in Db Browser; however, the spatialite polygon queries all seem to return null.

Am I doing something wrong?

Thanks, Kirk

Re: How do I use Spatialite400.dll?

DanPatterson — Sun, 10 Jan 2021 22:13:42 GMT

some historical discussion exists in the community... For example

SQLite as future-proof backend for Personal Geodab... - GeoNet, The Esri Community

Re: How do I use Spatialite400.dll?

RichRuh — Mon, 11 Jan 2021 18:04:24 GMT

Hi Kirk,

We have this working in-house, so it might be something with your environment or your database file. You might want to open a tech support issue so that we can take a closer look.

(SpatialLite should be in the 3rd Party Acknowledgement doc)

--Rich

Re: How do I use Spatialite400.dll?

MarcoBoeringa — Tue, 12 Jan 2021 17:11:48 GMT

Hi Kirk,

As you yourself already found out, the Mobile Geodatabase uses ESRI's own ST_Geometry implementation (the link and all sublinks you found in the ArcMap Help that hasn't yet been ported to the Pro Help: https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/what-is-the-st-geometry-storage-type.htm)

I have recently been experimenting with SQLite and ArcGIS Pro too, but using a different path. I use Python and arcpy to write out geometries to a SQLite database created with the Create SQLite Database geoprocessing tool (also see this link). The data involved is coming from OpenStreetMap, and is stored in a PostgreSQL / PostGIS database, that is NOT ESRI Enterprise Geodatabase enabled, just an ordinary spatial (PostGIS) database.

Note that the process described below DOES NOT work for the Mobile Geodatabase, just a plain SQLite database as created with the geoprocessing tool mentioned above, there appear to be additional dependencies that regulate data type validation that I haven't been able to figure out yet.

In order for the SQLite INSERT to work, I need to:

- Create a SQLite database using the Create SQLite Database geoprocessing tool.

- Load the stgeometry_sqlite.dll library that ESRI created and made available as a separate download on the My ESRI website (unfortunately, this library appears not part of the standard Pro installation) in Python using the sqlite3 Python package that is part of the default install of Pro's Python environment (you need to use enable_load_extension and load_extension for that in the sqlite3 Python module).

- Create a table with CREATE TABLE using a sqlite3 connection/cursor

- Add a geometry column to this table using the ESRI ST_Geometry library's AddGeometryColumn function and sqlite3 cursor.

- To use PostGIS ST_AsBinary with a psycopg2 cursor to read the PostGIS database and extract the features as WKB.

- Insert the geometries using a sqlite3 database connection / cursor using ST_GeomFromWKB. Note that ESRI's ST_Geometry library supports other options like ST_MPolyFromWKB as well for dedicated geometry types, but I haven't succeeded in using these properly, and the ST_GeomFromWKB "just works".

- Add a spatial index using the Add Spatial Index geoprocessing tool.

Note that implementing all of this was particular tough, this is certainly not for the faint of heart! It took me the better part of two weeks to get it running.

However, on the bright side, I am now capable of exporting geometries to a SQLite database at a rate of just over 100M records / hour! (yes, my current OpenStreetMap database is that big...). This is probably about 5x the speed or more of using an arcpy.da.SearchCursor and arcpy.da.InsertCursor that could do the same thing. This speed difference was crucial for me. This database can subsequently be read by ArcGIS Pro again.

By the way, as alternative to psycopg2, there is pyodbc, which supports a far wider range of databases besides PostgreSQL (e.g. SQL Server, Oracle), but lacks for example server side cursors. Both Python DB API adapters have their strengths and weaknesses...

Re: How do I use Spatialite400.dll?

MarcoBoeringa — Mon, 11 Jan 2021 19:31:26 GMT

Thought you might like this as well, ArcGIS Pro's Pairwise Dissolve tool dissolving a 379 M(!) record dataset of building geometries...

Re: How do I use Spatialite400.dll?

KirkKuykendall1 — Mon, 11 Jan 2021 20:18:25 GMT

Wow.

I fear the "lite" in Sqlite may have led people to think it can't do any heavy lifting.

How many cores is Pairwise_Dissolve using?

Re: How do I use Spatialite400.dll?

MarcoBoeringa — Tue, 12 Jan 2021 07:18:26 GMT

The Pairwise Dissolve tool is actually just running on a modest 4C/8T Core i7 desktop (32GB RAM though). And I only set the geoprocessing environment to allow 75% 3C/6T usage of the processor, so as to not completely consume this limited CPU.

The database is running on a HP Z840 workstation though, a refurbished one that I recently acquired, and has dual Xeon E5-2680 v4 with 14C/28T each, so 28C/56T total.

This is some custom Python multi-threaded code I developed (unrelated to the SQLite stuff), that goes flat out at 100% usage on the database server generalizing geometries in PostGIS: