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@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 > <[email protected]> > > wrote: > > This is awesome! Thanks Even. > > > > Stefan > > > > On Mon, Oct 6, 2014 at 8:55 PM, Even Rouault <[email protected]> > > > > 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@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 < > >> > >> [email protected]> > >> > >> > 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@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 _______________________________________________ gdal-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/gdal-dev
