[gdal-dev] Better way to use GPKG from SpatiaLite

[Even Rouault]

Jukka,

From the GPKG driver, it would mean that the user would have to think to 
change its layer names to prefix them with vgpkg_ in his requests and the GPKG 
driver should be modified to accept Spatialite geometries being returned as 
column values.

From the SQLite driver, currently it doesn't recognize well the vgpkg_ tables 
when listing layers (it doesn't recognize the database as being a spatialite 
one or a OGR-style one). When issuing direct SQL, it works better.
If recognizing database/layers was fixed, an extra workaround would have to be 
done since the driver would try to request idx_vgpkg_foo_geometry as the 
spatial index for table foo / vgpkg_foo, whereas the GPKG name is 
rtree_foo_geometry. If manually doing the spatial request (i.e. not using the 
SpatialIndex syntaxic sugar), I guess that should work.

In both cases there's however a cost to pay due to the virtual table 
mechanism, and probably suboptimal/lack of use of attribute indices, etc...

I think the best user friendly, and best performing, solution would be:
- libspatialite: to accept GPKG geometries transparently in all the functions 
that take geometries as argument (and still return a spatialite geometry)
- OGR GPKG driver: to accept spatialite geometries as valid values for results 
of SQL requests.

Even


> Hi,
> 
> I am reading from http://www.gdal.org/drv_geopackage.html the following
> 
> "Starting with GDAL 2.0, SQL SELECT statements passed to ExecuteSQL() are
> also executed directly against the database. If Spatialite is used, a
> recent version (4.2.0) is needed and use of explicit cast operators
> AsGPB(), GeomFromGPB() are required. It is also possible to use with any
> Spatialite version, but in a slower way, by specifying the
> "INDIRECT_SQLITE" dialect. In which case, GeoPackage geometries appear as
> Spatialite geometries after translation by OGR."
> 
> I did also read this
> https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p16gpkg and
> made some experiments and there is another way to query GeoPackage
> databases from SpatiaLite and for me it feels very much more convenient if
> there is a need to do spatial queries.
> 
> How it works is to execute first AutoGPKGStart(). Before that it would be
> good to check if SpatiaLite is recognizing GPKG by executing
> CheckGeoPackageMetaData() and since today in the SpatiaLite trunk the check
> can also be done with CheckSpatialMetaData(). The expected result is "4".
> 
> Successful AutoGPKGStart() creates virtual GPKG tables which can be queried
> transparently without casting to/from GPB. The names of the virtual tables
> have "vgpkg_" added before the main table name.
> 
> I can't say if selecting -dialect SQLite could turn on the AutoGPKGStart
> automatically, of if the GPKG database should be opened with
> SQLite/SpatiaLite driver instead of GPKG driver from the beginning.
> 
> -Jukka Rahkonen-
> 
> 
> 
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev

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