[gdal-dev] SQLite dialect with GeoPackage

[Stefan Ziegler]

Hi Even

thanks! INDIRECT_SQLITE works like a charm. I could not figure out the
syntax to create a new layer in the very same gpkg file:

stefan at hatschepsut:~/Downloads$ ogr2ogr -append -f GPKG 2601.gpkg -nln
my_new_layer -dialect INDIRECT_SQLITE -sql 'SELECT * FROM
bodenbedeckung__bbtext WHERE tid IN (SELECT tid FROM bodenbedeckung__bbtext
GROUP BY ST_X(ST_SnapToGrid(geom, 500)), ST_Y(ST_SnapToGrid(geom, 500)),
nummer_name ORDER BY ST_X(ST_SnapToGrid(geom,500)),
ST_Y(ST_SnapToGrid(geom, 500)), ST_Distance(geom, ST_SnapToGrid(geom,
500)))' 2601.gpkg
ERROR 1: sqlite3_exec(CREATE TABLE "my_new_layer" ( "fid" INTEGER PRIMARY
KEY AUTOINCREMENT, "geom" POINT )) failed: database is locked
ERROR 1: Terminating translation prematurely after failed
translation from sql statement.

Is this even possible?

regards
Stefan


On Mon, Oct 6, 2014 at 8:20 PM, Even Rouault <even.rouault at spatialys.com>
wrote:

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20141006/f7d9e643/attachment-0001.html>