How do I use Spatialite400.dll?

792
6
01-10-2021 10:33 AM
KirkKuykendall1
Occasional Contributor III

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.

image.png

Am I doing something wrong?

Thanks, Kirk

 

Tags (2)
0 Kudos
6 Replies
DanPatterson
MVP Esteemed Contributor

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

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


... sort of retired...
RichRuh
Esri Regular Contributor

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

 

0 Kudos
MarcoBoeringa
MVP Regular Contributor

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-...)

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

 

MarcoBoeringa
MVP Regular Contributor

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

MarcoBoeringa_0-1610393293452.png

 

KirkKuykendall1
Occasional Contributor III

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? 

 

MarcoBoeringa
MVP Regular Contributor

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:

MarcoBoeringa_1-1610435892657.png