[gdal-dev] SQLite dialect with GeoPackage

[Stefan Ziegler]

This is awesome! Thanks Even.

Stefan

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

> Le lundi 06 octobre 2014 20:48:02, Stefan Ziegler a écrit :
> > 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?
>
> Ah, good point. I've just added a particular case in ogr2ogr, like done for
> sqlite, when input dataset = output dataset, to open the database just
> once to
> avoid that locking issue.
>
> >
> > 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
>
> --
> 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/055d1617/attachment.html>