[gdal-dev] SQLite dialect with GeoPackage

[Even Rouault]

Le lundi 06 octobre 2014 19:41:43, Stefan Ziegler a écrit :
> Hi
> 
> When trying to use SQLite dialect with GeoPackage I get some errors, like:
> 
> stefan at hatschepsut:~/Downloads$ ogrinfo -al -sql 'SELECT
> ST_X(ST_SnapToGrid(geom,1)) FROM bodenbedeckung__bbtext LIMIT 1'
> 2601.gpkgINFO: Open of `2601.gpkg'
>       using driver `GPKG' successful.
> ERROR 1: In ExecuteSQL(): sqlite3_prepare(SELECT
> ST_X(ST_SnapToGrid(geom,1)) FROM bodenbedeckung__bbtext LIMIT 1):
>   no such function: ST_SnapToGrid
> 
> 
> The same query works with Shapefiles. I thought the SQLite dialect can be
> used with any OGR datasource?

Stefan,

You need a very recent Spatialite (4.2 I think) for compatibility with 
GeoPackage. But your request would not work since Spatialite functions can 
only work with Spatialite geometry blobs and not Geopackage geometry blobs. 
Spatialite 4.2 provide explicit conversion methods AsGPB / GeomFromGPB. 

For example :

$ ogrinfo poly.gpkg -sql "select AsGPB(ST_SnapToGrid(GeomFromGPB(geom),1)) 
from poly"

I've also just added in trunk a "INDIRECT_SQLITE" dialect that will cause the 
generic solution used for shapefiles for example to be usable by GeoPackage 
datasources too. But this will be slower than direct SQL due to an extra 
translation layer done by OGR ( GPKG -> OGR feature -> SQLite/Spatialite 
virtual table -> OGR feature ).

$ ogrinfo poly.gpkg -sql "select ST_SnapToGrid(geom,1) from poly" -dialect 
INDIRECT_SQLITE

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com