Enable Rtree support in SQLite3 python module of ArcGIS Pro 3.3

856
5
06-25-2024 04:30 AM
Marco_Foi
Emerging Contributor

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?

Tags (3)
5 Replies
ChrisCowin_dhs
Regular Contributor

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

0 Kudos
Marco_Foi
Emerging Contributor
"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.


0 Kudos
Shaun-Walbridge
Emerging Contributor

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.

0 Kudos
KevinGabelman
Emerging Contributor

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 

0 Kudos
ShaunWalbridge
Esri Regular Contributor

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

0 Kudos