SQL query with ST_Geometry relational operators fails when using an SQLite database on Linux

Description

Executing an SQL query with an ST_Geometry spatial operator fails when using an SQLite database on Linux only. The following errors are returned depending on the ArcGIS Pro version of the ST_Geometry library file.

Segmentation fault error

A ‘Segmentation fault’ error is returned when using the ArcGIS Pro 2.9 and earlier versions of the library:

[testuser@XLinuxTest2265 sqlitetools_33822022]$ sqlite3 st_sqlite.sqlite

SQLite version 3.38.2 2022-03-26 13:51:10

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> SELECT load_extension('/tmp/sqlite_stgeom/sqlitetools_33822022/Libraries/ArcGISPro_28_ST_Geometry_SQLite_177726/SQLite/Linux64/libstgeometry_sqlite','SDE_SQL_funcs_init');

 

sqlite> SELECT ca.OBJECTID, ca.CampingAreaName,  count(*) NumCampSites

   ...> FROM Camping_Areas ca, Campsites cst

   ...> WHERE ST_Intersects(ca.Shape, cst.Shape)

   ...> GROUP BY ca.CampingAreaName

   ...> ORDER BY NumCampSites desc;

Segmentation fault

Undefined symbol error

An 'undefined symbol' error is returned when using the ArcGIS Pro 3.0 version of the library:

[testuser@XLinuxTest2265 sqlitetools_33822022]$ sqlite3 st_sqlite.sqlite

SQLite version 3.38.2 2022-03-26 13:51:10

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>  SELECT load_extension('/tmp/sqlite_stgeom/sqlitetools_33822022/Libraries/ArcGISPro_30_ST_Geometry_SQLite_182273/SQLite/Linux64/libstgeometry_sqlite','SDE_SQL_funcs_init');

Error: /tmp/sqlite_stgeom/sqlitetools_33822022/Libraries/ArcGISPro_30_ST_Geometry_SQLite_182273/SQLite/Linux64/libstgeometry_sqlite.so.so: undefined symbol: SES_sde_date_to_ISO8601_date

Cause

-

Workaround

Obtain and load an updated libstgeometry_sqlite.so file from My Esri to resolve this.
This library is part of the ArcGIS Pro ST_Geometry SQLite zip file in the Database Support Files selection of the ArcGIS Pro Download Components in My Esri.

• For ArcGIS Pro 2.8, the updated zip file is named ArcGISPro_28_ST_Geometry_SQLite_184454
• For ArcGIS Pro 2.9, the updated zip file is named ArcGISPro_29_ST_Geometry_SQLite_184455
• For ArcGIS Pro 3.0, obtain the ArcGIS Pro 3.0.3 zip file named  ArcGISPro_30_ST_Geometry_SQLite_184456