ogrinfo: wrong output using sql with GeoPackage

[jguelat]

According to ​http://www.gdal.org/drv_geopackage.html, it should be possible to use Spatialite SQL functions (such as ST_Area, ST_Buffer, etc.) with GeoPackage tables.

Unfortunately these queries don't seem to work. See for example the following one (dataset attached to the ticket):

ogrinfo geopackage_test.gpkg -sql "SELECT id, ST_Area(geom) AS area FROM geopackage_test"

I'm getting NULL values for the area. The same happens with other queries using the geometry column (e.g., ST_Buffer produces a NULL geometry).

I'm using GDAL 2.1.0 with Spatialite 4.3.0. Both were installed using the 64 bits version of the OsGeo4W installer. The GeoPackage was produced by QGIS 2.14.3.

[Jukka Rahkonen]

Did you try the older syntax SELECT id, ST_Area(castautomagic(geom)) AS area FROM geopackage_test?

I am using a gdal-dev version from gisinternals and for me is seems that Spatialite is not available for the GPKG driver. This returns the SQLite version

ogrinfo geopackage_test.gpkg -sql "SELECT spatialite_version() AS version"

I guess that this should return the Spatialite version but it does not me - how about you?

ogrinfo geopackage_test.gpkg sqlite -sql "SELECT sqlite_version() AS version"

[jguelat]

Thanks for your help!

I've already tried the older syntax but it doesn't work (error: no such function CastAutomagic).

Spatialite seems to be available here, ogrinfo returns 4.3.0 for spatialite_version().

[Even Rouault]

I could reproduce. I've investigated and thinks this is an issue with how libspatialite has been built in OSGeo4W (without GeoPackage support).

Related ticket : ​https://trac.osgeo.org/osgeo4w/ticket/512

Closing as not a GDAL bug

[Even Rouault]

Note: as a workaround, you can add "-dialect indirect_sqlite" at the expense of speed.

[jguelat]

Thanks a lot for investigating! It is working now!