topic Re: Enable Rtree support in SQLite3 python module of ArcGIS Pro 3.3 in Python Questions

Enable Rtree support in SQLite3 python module of ArcGIS Pro 3.3

Marco_Foi — Tue, 25 Jun 2024 11:30:01 GMT

I am developing a Toolbox in ArcGIS Pro 3.3 using the usual Conda Python environment.

I need to manipulate a Geopackage and since ArcPy support for .gpkg is almost non-existing I have to connect to the data source as if it was a plain SQLite DB.

Connection works perfectly.

sqlite_connection = sqlite3.connect("c:\\geopackage.gpkg")

Also executing SQL works. To create a feature-class table I run:

sql = f'''CREATE TABLE "faults" ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geom" LINESTRING, "fault_id" MEDIUMINT);'''
sqlite_connection .execute(sql)

Then I need to create an RTree index as this is one of the requirements for a geompetry layer in a geodatabase. This is done using a virtual table as stated here:
https://www.geopackage.org/guidance/extensions/rtree_spatial_indexes.html
https://sqlite.org/rtree.html

..but running the code:

sql = f'''CREATE VIRTUAL TABLE "rtree_{table_name}_geom" USING rtree("id", "minx", "maxx", "miny", "maxy");'''
sqlite_connection .execute(sql)

will fail into a:

sqlite3.OperationalError: no such module: rtree

Any ideas how to solve this.. ..or an alternative approach for running an arcpy-enabled script?

Re: Enable Rtree support in SQLite3 python module of ArcGIS Pro 3.3

ChrisCowin_dhs — Fri, 28 Jun 2024 21:55:13 GMT

This might be a bug, https://pro.arcgis.com/en/pro-app/latest/help/data/databases/database-requirements-sqlite.htm says rtree is fully supported in 3.3

Re: Enable Rtree support in SQLite3 python module of ArcGIS Pro 3.3

Marco_Foi — Sun, 30 Jun 2024 01:22:48 GMT

"using" an R*Tree just requires making ordinary queries on the special tables the index poputates. There is no special need for leveraging an rtree in read mode.. ..and once it is in place, the rtree triggers will maintain it.

So "Full support" might just mean ArcGis Pro 3.3 can detect and use the special tables of an RTree.

Creating them is a whole other story.

For sure the documentation is quite obscure and deceiving.

Re: Enable Rtree support in SQLite3 python module of ArcGIS Pro 3.3

Shaun-Walbridge — Fri, 07 Mar 2025 05:58:52 GMT

This issue was an oversight in how our core CPython distribution was built, where the expected extensions were not being included. It has been addressed in Pro 3.5, and can be installed into Pro 3.3 and 3.4 as well by upgrading to the upcoming Python 3.11.11 build on our channel.

Re: Enable Rtree support in SQLite3 python module of ArcGIS Pro 3.3

KevinGabelman — Fri, 04 Apr 2025 04:57:24 GMT

So Shaun, where is the “python 3.11.11 build” on your channel? Could you provide some more information about what you’re suggesting? I am running Pro 3.4 which seems to only allow up to 3.11.10. RTree is definitely something I would expect the arcgis python build to include

Re: Enable Rtree support in SQLite3 python module of ArcGIS Pro 3.3

ShaunWalbridge — Sat, 05 Apr 2025 04:00:28 GMT

@KevinGabelmanby coincidence, today is the day we flipped over the Pro 3.5 packages onto the main Esri channel. You should be able to upgrade to it, e.g. from the command line, with `conda upgrade --no-pin python`.