ST_Intersects support in GeoPackage

Question

I generated a new GeoPackage with GDAL:

ogr2ogr -f GPKG my.gpkg my.geojson

I can open the GPKG in QGIS and all of the data is there, but when I open a SQLite client and run a spatial query, for instance

select count(*) from my_table m join another_table a where st_intersects(m.geom, a.geom)

I get an error:

no such function: ST_Intersects

Does GeoPackage not support SpatiaLite-style query functions?

Spatialite does support it: gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html#p12 However GeoPackage natively doesn't "hold" these functions - its just a bunch of tables. That is, sqlite doesn't support spatial functions without some extension provider, like spatialite. Possibly you haven't loaded spatialite (or other provider) correctly, or it was built without GEOS. Can you update the question to show exactly what you did before the query? — BradHards, Dec 31, 2022 at 0:26
Did you load the spatialite extension? e.g. for linux gis.stackexchange.com/questions/419577/… — user2856, Dec 31, 2022 at 1:04
I did not load mod_spatialite. I didn't realize it wasn't loaded by default in a new GeoPackage. Thank you for the helpful pointers! — serverpunk, Dec 31, 2022 at 22:01
You don't need to use spatialite (although IMO its a great solution, disclaimer: I may have had some involvement in implementation). There are other tools you can use. — BradHards, Jan 1 at 1:26
If you have another question, ask it. This site is a Q&A approach, not a forum, and the original question has already been answered. — BradHards, Jan 2 at 20:31

serverpunk · Accepted Answer · 2022-12-31 23:04:00Z

Per the helpful comments above, I hadn't loaded the SpatiaLite extension. On macOS with Homebrew, you need to:

Install a Homebrew copy of sqlite (the copy bundled with macOS doesn't allow module loading)
brew install libspatialite if you don't have it already (check with brew info libspatialite)
Get the path to libspatialite with brew --prefix libspatialite
Open a new DB session using the Homebrew sqlite CLI: $(brew --prefix sqlite)/bin/sqlite3 my.gpkg
Load the module with .load <path to libspatialite from step 3>/lib/mod_spatialite.dylib

Then I could run the query from my original question.

Summarized and adapted from this article.

1 Answer 1