Can't query GeoPackage geometry via SQLite client

Question

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.

Answer 1

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.