[gdal-dev] Attach database and GeoPackage

[Even Rouault]

Le vendredi 04 novembre 2016 07:39:27, Rahkonen Jukka (MML) a écrit :
> Hi,
> 
> I have been thinking that it could be nice to be able to open GeoPackage
> with attached database https://www.sqlite.org/lang_attach.html. Attaching
> database that contains spatial tables would probably give too much
> troubles because reading the spatial metadata of the attached db through
> the master db so that GeoPackage clients understands what happens feels
> difficult.  However, attaching tables with pure attribute data (aspatial
> tables) might make some sense by allowing to keep the heavy but stable
> geometries in one db and more frequently updated attributes in another.
> Wondering if there could be some open option like -oo
> attach_database=path_to_another_sqlite_db. I do not have any clear use
> case in my mind yet, just thinking.

Hi Jukka,

You can already attach another sqlite (not necessarily GPKG by the way) to a GPKG
by using the ExecuteSQL() interface

See the below python script:

from osgeo import ogr
ds = ogr.Open('poly.gpkg')
ds.ExecuteSQL("ATTACH DATABASE 'idlink.gpkg' AS aux")
sql_lyr = ds.ExecuteSQL("SELECT * FROM poly p JOIN aux.idlink AS id ON p.eas_id = id.eas_id")
f = sql_lyr.GetNextFeature()
f.DumpReadable()
ds.ReleaseResultSet(sql_lyr)


Output:

OGRFeature(SELECT):1
  AREA (Real) = 215229.266
  EAS_ID (Integer64) = 168
  PRFEDEA (String) = 35043411
  NAME (String) = _168_
  POLYGON ((479819.84375 4765180.5,479690.1875 4765259.5,479647.0 4765369.5,479730.375 4765400.5,480039.03125 4765539.5,480035.34375 4765558.5,480159.78125 
4765610.5,480202.28125 4765482.0,480365.0 4765015.5,480389.6875 4764950.0,480133.96875 4764856.5,480080.28125 4764979.5,480082.96875 4765049.5,480088.8125 
4765139.5,480059.90625 4765239.5,480019.71875 4765319.5,479980.21875 4765409.5,479909.875 4765370.0,479859.875 4765270.0,479819.84375 4765180.5))


Actually you can even use the SQL SQLite dialect and its ability to open another OGR source as a joint layer.
The trick when the main OGR datasource is already a GPKG is to specify the "indirect_sqlite" dialect. But performance will be
slower than with the attach method due to GPKG/SQLite->OGR->SQLite->OGR round-trips.

ogrinfo poly.gpkg -sql "select * from poly p join \"idlink.gpkg\".idlink id  ON p.eas_id = id.eas_id" -dialect indirect_sqlite

Even

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