[gdal-dev] SQLite dialect with GeoPackage

[Stefan Ziegler]

Ah, still one - hopefully last - question: how can I overwrite a layer in
the gpkg?

stefan at hatschepsut:~/Downloads$ ogr2ogr -append  -f GPKG 2601.gpkg -nln
my_new_layer -lco OVERWRITE=YES -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
WARNING: Layer creation options ignored since an existing layer is
         being appended to.

-append is needed since the layer should be added to an existing file
-co OVERWRITE=YES is unknown: "FAILURE: Unknown option name '-co'"
-lco OVERWRITE=YES is ignored.

Stefan




On Mon, Oct 6, 2014 at 9:01 PM, Stefan Ziegler <stefan.ziegler.de at gmail.com>
wrote:

> 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/95f8a1df/attachment.html>