[Geopackage] Indexing large tile tables

Even Rouault even.rouault at spatialys.com
Wed Sep 19 10:00:54 EDT 2018


Andreas,

do you have a UNIQUE (zoom_level, tile_column, tile_row) constraint in your raster table as suggested by 
http://www.geopackage.org/spec/#_sample_tile_pyramid_informative ?

If so that results in the creation of an automatic index on those columns.

sqlite> explain query plan select * from byte where zoom_level = 0 and tile_column between 0 and 1 and tile_row between 0 and 1;
0|0|0|SEARCH TABLE byte USING INDEX sqlite_autoindex_byte_1 (zoom_level=? AND tile_column>? AND tile_column<?)

If I manually create an index, I get the same query plan (using the explicit index)

sqlite> create index myindex on byte (zoom_level, tile_column, tile_row);
sqlite> explain query plan select * from byte where zoom_level = 0 and tile_column between 0 and 1 and tile_row between 0 and 1;
0|0|0|SEARCH TABLE byte USING INDEX myindex (zoom_level=? AND tile_column>? AND tile_column<?)

Even


> Hi,
> I've been working on a large raster geopackage, roughly 550GB worth of
> tiles covering the entire planet
> down to zoom level 13.
> 
> At zoom level 13 there are over 130 million tiles, and simply locating the
> tiles to render takes time
> (up to a couple of seconds in my tests), with what looks like a reasonable
> query, something like:
> 
> select * from {tilesTable} where zoom_level = 13 and tile_row between {rl}
> and {rh} and tile_column between {cl} and {ch}
> 
> The reason is simple, on a spec compliant geopackage it results in a
> sequential scan (no indexes, checked
> the spec, does not seem to mandate one).
> 
> I was wondering if it was a good idea to suggest creation of a multi-column
> index on zoom_level, tile_row and tile_column
> so that performance is uniformly good no matter what the zoom level is,
> even on large geopackages.
> It's a little extra effort compared to the creation of the whole package,
> and would change perception of
> performance on large raster GeoPackages.
> Of course, one can create the index like I did and enjoy the benefits, but
> it may well be that people out
> there already tried, the queries were not visible to them, or were not
> familiar with SQL, and just
> ended up dismissing the format for large deliveries.
> 
> Cheers
> Andrea
> 
> ==
> 
> GeoServer Professional Services from the experts! Visit http://goo.gl/it488V
> for more information. == Ing. Andrea Aime @geowolf Technical Lead
> GeoSolutions S.A.S. Via di Montramito 3/A 55054 Massarosa (LU) phone: +39
> 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549
> http://www.geo-solutions.it http://twitter.com/geosolutions_it
> ------------------------------------------------------- *Con riferimento
> alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 -
> Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni
> circostanza inerente alla presente email (il suo contenuto, gli eventuali
> allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i
> destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per
> errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le
> sarei comunque grato se potesse darmene notizia. This email is intended
> only for the person or entity to which it is addressed and may contain
> information that is privileged, confidential or otherwise protected from
> disclosure. We remind that - as provided by European Regulation 2016/679
> “GDPR” - copying, dissemination or use of this e-mail or the information
> herein by anyone other than the intended recipient is prohibited. If you
> have received this email by mistake, please notify us immediately by
> telephone or e-mail.*


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


More information about the Geopackage mailing list