Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#6546 closed defect (invalid)

ogrinfo: wrong output using sql with GeoPackage

Reported by: jguelat Owned by: warmerdam
Priority: normal Milestone:
Component: default Version: 2.1.0
Severity: normal Keywords:
Cc: jef

Description

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.

Attachments (1)

geopackage_test.gpkg (46.0 KB ) - added by jguelat 6 years ago.

Download all attachments as: .zip

Change History (6)

by jguelat, 6 years ago

Attachment: geopackage_test.gpkg added

comment:1 by Jukka Rahkonen, 6 years ago

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 sqlite_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 spatialite_version() AS version"
Last edited 6 years ago by Jukka Rahkonen (previous) (diff)

comment:2 by jguelat, 6 years ago

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().

comment:3 by Even Rouault, 6 years ago

Cc: jef added
Resolution: invalid
Status: newclosed

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

comment:4 by Even Rouault, 6 years ago

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

comment:5 by jguelat, 6 years ago

Thanks a lot for investigating! It is working now!

Note: See TracTickets for help on using tickets.