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 >
_______________________________________________ gdal-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/gdal-dev
