1

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?

6
  • 2
    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
  • 2
    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!
    – bertday
    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, 2023 at 1:26
  • 1
    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, 2023 at 20:31

1 Answer 1

2

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

  1. Install a Homebrew copy of sqlite (the copy bundled with macOS doesn't allow module loading)
  2. brew install libspatialite if you don't have it already (check with brew info libspatialite)
  3. Get the path to libspatialite with brew --prefix libspatialite
  4. Open a new DB session using the Homebrew sqlite CLI: $(brew --prefix sqlite)/bin/sqlite3 my.gpkg
  5. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.