2

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.

2 Answers 2

5

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.

0

When connecting to a GeoPackage file, in addition to loading the mod_spatialite sqlite extension an additional initialisation step, EnableGpkgMode is needed as the geometry data and metadata is stored a bit different than with a classic Spatialite file. So, the initialisation can be done e.g. like this:

SELECT load_extension('mod_spatialite');
SELECT EnableGpkgMode();

After running this, you can simply use (most) Spatialite functions. CastAutomagic was needed with spatialite 4.3, which was released 8 years ago and so is very outdated.

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.