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

[Jukka Rahkonen]

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-