Can’t Query GeoPackage Geometry via SQLite Client – Solutions and Tips

geopackagespatialitesqlite

I have a GeoPackage and I want to select the geometry as text from the SQLite command-line client. Here's what I've done:

  1. Run sqlite3
  2. Load SpatiaLite with .load /path/to/mod_spatialite.dylib (I'm on a Mac)
  3. Run the following query:
sqlite> select astext(geom) from boundary;

There aren't any errors, but the client prints a blank newline as the result.

Am I doing something wrong, or can anyone think of other diagnostics?

I'm new to SpatiaLite/GeoPackage.

The layer loads fine in QGIS! The geometry looks fine. It's one big multipolygon.

Best Answer

Geometries in SpatiaLite are not encoded in the same way than geometries in GeoPackage. The AsText/ST_AsText function needs a SpatiaLite geometry as an input. The function returns NULL for GeoPackage geometries because it does not understand what they are.

However, SpatiaLite has a function "CastAutomagic" that converts GeoPackage geometries into SpatiaLite geometries. Add the function into your query and it will handle the GeoPackage geometries.

SELECT AsText(CastAutomagic(geom)) from boundary;

See the manual of the SpatiaLite functions https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html.

CastAutomagic

This function will indifferently accept on input:

  • a SpatiaLite own BLOB Geometry
  • a GPKG own BLOB Geometry

then returning a SpatiaLite own BLOB geometry.

Related Question