[gdal-dev] SQLite dialect with GeoPackage

[Even Rouault]

Le lundi 06 octobre 2014 21:13:04, Stefan Ziegler a écrit :
> 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.

Hum, using the same name as input and output dataset causes a lot of 
interesting issues. I've fixed the above issue.

Works if you use the -overwrite flag ( -lco OVERWRITE=YES will not work in that 
context )

ogr2ogr -overwrite poly.gpkg poly.gpkg -sql "select * from poly" -nln poly3

If you had the errors above, you may need to clean manually the database 
before, so as to put it back into a consistant state :

ogrinfo 2601.gpkg  -sql "drop table my_new_layer"
ogrinfo 2601.gpkg  -sql "drop table rtree_my_new_layer_geom"

> 
> 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

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