problems loading spatialite extension

[David_JAnderson]

I am trying to use Spatialite in ArcGIS 10.2.2.  When I try to load the SQLite extension like so

--Load the ST_Geometry library on Windows.
SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init');


which is what is shown on the help page,

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

The python window just crashes.  I try loading the extension in command line SQLite and that fails.  I tried a mix of 32 bit and 64 bit thinking that might be the issue.  Nope.  I tried different locations and PATH variable settings.  Nope.  I tried it with the full path in the command.  Nope.

I was able to do this in 10.2.1 but not now in 10.2.2.

I can get the standard Spatialite 4.2 package to load up. That will give me the Spatialite geometry option.  One thing I want to test is how the st_geometry works with other tools like GDAL/OGR.

[KimOllivier]

The snippets in the help are for the command line inside the sqlite3 interface, not Python. You are expected to start sqlite3 from a windows Command shell and then enter these commands. The point of the examples is to show that the setup of sqlite3 to handle spatial columns can be done outside ArcGIS for third party applications. Inside ArcGIS/Python there is already a (fairly hidden) command arcpy.gp.CreateSqliteDatabase(<path/file>, <spatial_type>). I did have trouble getting the help example to work, but I can't find the note on what worked!

My experience has been that SPATIALITE type is the most mature and have the most external support at the moment. The ST_Geometry works inside Esri tools, (but I would not expect anywhere else) and the Geopackage type is also a bit new, lacking in third party use.

[David_JAnderson]

After the command failed in python, I did try using a SQLite interface, with the same results.

However, it shouldn't make a difference as any command that can be executed at the SQLite command line interface can be executed using the Python sqlite3 module or the APSW module if that is your SQLite preference.  Further, why would there be an arcpy command to create a spatial database yet no functionality to access that database with arcpy once it had been created?

I agree that the Spatialite extension is the more mature of the three.  I have been using it for several years with the extension dll provided on the Spatialite site The Gaia-SINS federated project home-page .  The problem is that those solutions were private and I could not deploy them to others in my organization without leading them through the spatialite extension installation.  With the rollout in 10.2 of a ESRI version of the spatialite extension, I could now do Python scripts that other people could run right out of the box, without any additional installation.

I have a ESRI support ticket in on this issue.  I'll post the answer whenever I get one from ESRI.

[David_JAnderson · Accepted solution]

The answer for ArcGIS 10.2.2 is to upgrade the SQLite dll from the 3.6 version that is shipped with ArcGIS to the latest version.  Which at this time is 3.8.7.4.

This is for stand alone Python (IDLE, Pythonwin).  The version of SQLite in the Python command line bundled into ArcMap/Catalog is 3.7, which works with the stgeometry dll.

The other thing to get in line is to make sure that the bit count aligns, ie use a 32 bit python with a 32 bit SQLite with the 32 version of the stgeometry_SQLite dll.