OGC Testbed-16: GeoPackage Engineering Report

OS MasterMap topography is "the most detailed and accurate view of Great Britain’s landscape – from roads to fields, to buildings and trees, fences, paths and more." This dataset is delivered as a large set of GML files. The data can be used with a set of four freely available style sets also available as SLD. The MasterMap topography is to be displayed only at high scales (1:4000 and above). However, this dataset has a very significant size: 50GB as compressed GML files, 300GB as imported in PostgreSQL, and 200GB as an encoded GeoPackage (after optimizations). See MasterMap Data for more details. This dataset was ideal for tuning write and read performance, as well as experimenting with different content layouts.

OS Zoomstack is a single GeoPackage providing "a single, customisable map of Great Britain to be used at national and local levels." Quoting from the documentation, "OS Open Zoomstack is a comprehensive vector basemap covering Great Britain at a national level, right down to street-level detail." This GeoPackage can be used with a set of six freely available style sets encoded, among other formats, as SLD.

The Zoomstack data set, around 11GB in size (see Space used by table, sorted from larger to smaller tables for details), provided a multi-scale map. Due to the small size of this dataset, the data was suitable for quick tests. As evident in Column level size details, geometries are the major component of space utilization in this dataset, while potentially enumerated fields (type when present) use an amount of space that is one or two orders of magnitude smaller.

In Testbed-16, participants used Zoomstack in scenarios where the huge size of the MasterMap data was cumbersome. Unlike the MasterMap dataset, ZoomStack is a true multi-zoom level dataset, visible from the country level and down to the road level, although not as detailed as the former. The data is also a natural fit for the generalized tables extension.

The GeoPackage Metadata Extension supports hierarchical metadata. Through hierarchical metadata, a GeoPackage client can identify metadata that pertains to the whole GeoPackage file and metadata that is relevant to a particular layer (e.g., feature table). In the Testbed-16 experiment, the metadata elements for the whole GeoPackage file included the WPS request that led to the creation of the GeoPackage. Further, the metadata elements for individual layers included descriptions of the WFS resources that served the data were included. Due to the hierarchical nature of the metadata, when needed a GeoPackage client can navigate from a the layer metadata to the metadata for the whole GeoPackage.

In the OWS Context GeoJSON encoding, the context is represented by a GeoJSON FeatureCollection and individual resources (layers) are represented by GeoJSON Feature instances. In this metadata profile, the FeatureCollection is inserted into gpkg_metadata as a metadata document, but the GeoJSON Feature instances referring to the feature data (not the WPS instance) are removed. The Feature instances are subsequently inserted into gpkg_metadata as their own entries.

As illustrated in Figure 8, gpkg_metadata_reference is populated as follows:

In addition, semantic annotations were used to separate OWS Contexts representing dataset provenance from OWS Contexts used for other purposes.

The Portrayal Extension defines two tables for style information, gpkgext_styles and gpkgext_stylesheets. While this table structure allows a style to have multiple encodings, this feature was not used in Testbed-16.

These tables were populated as follows:

gpkgext_styles

gpkgext_stylesheets

The Portrayal Extension defines three tables for symbol information: gpkgext_symbols, gpkgext_symbol_images, and gpkgext_symbol_content. While this table structure supports multiple encodings for each style and encoding of multiple styles into a single file as sprites, neither of these approaches was used in Testbed-16.

These tables were populated as follows:

gpkgext_symbols

gpkgext_symbol_images

gpkgext_symbol_content

The Portrayal Extension does not define an explicit coupling between layers and styles. (Coupling, in this context, refers to association of styles with layers.) Without any additional design elements, coupling is completely the responsibility of the user and/or client application as illustrated in Figure 10. In many scenarios, having the GeoPackage explicitly declare the coupling between layers and styles simplifies operations for the GeoPackage client operator. In Testbed-16, this coupling was done using semantic annotations.

As illustrated in Figure 11, semantic annotations can be applied to features table and a style (a row in gpkgext_styles). This links the table and the style together.

In Testbed-16, semantic annotations were established for styles in a GeoPackage through the following:

There are a number of scenarios where multiple styles or stylesheets are designed to be used together. In the Testbed-16 scenario, there are four stylesheets (standard, backdrop, light, outdoor) for each of the six feature types. Since a client user may wish to change the style for each layer at once, there needs to be a mechanism to aggregate these styles together. In Testbed-16, this was done through stylable layer sets. As developed in the OGC Vector Tiles Pilot, Phase 2 (VTP2), a stylable layer is created through a semantic annotation and both the styles and layers are tagged with that annotation.

In Testbed-16, semantic annotations were established for stylable layer sets in a GeoPackage through the following:

Many attributes from the source OS data originally stored as strings can be expressed as enumerations (code lists). The GeoPackage schema extension allows encoding these enumerations as integers or single characters. Their descriptions are found in the gpkg_data_column_constraints table. Following are some examples of how GeoPackage data is encoded without and with enumerations.

The complete set of enumerations used can be found in Enumerations. Using this approach reduced the MasterMap GeoPackage size from 245GB to 206GB, an improvement of roughly 20%.

MasterMap is meant to be displayed at scale down to 1:4000, but not lower. As a result, when displaying maps, most of the time is spent locating the tiny portion of data required. The grid split could then be leveraged as a way to reduce data access. This provides an index GeoPackage that simply spatially indexes the single GeoPackages in the grid, while also providing all the styling and metadata information required to present the full dataset. Under these conditions, rendering a map should require opening and processing the spatial indexes over a small number of files. In particular, the common case will be to read data from a single GeoPackage, while the worst case will be to open at most four, for any conceivable map display at 1:4000.

In response, the large MasterMap Topography dataset was split into smaller GeoPackages as described in Segmentation. These GeoPackages are available in the GeoServer osmm workspace, which is password protected (please ask for credentials). Each GeoPackage generates 6 layers in the GeoServer services. For example, in TQ.gpkg we have:

This amounts to a grand total of 336 layers. There are also layer groups for the TQ zone, one for each possible styling of the six layers, for a grand total of 230 layer groups, for example:

To take advantage of this segmentation, Testbed-16 participants produced the GeoPackage Index extension which supports an Index GeoPackage containing an index table. A client of the GeoPackage Index extension should be able to find all the metadata information about a feature entry in the main GeoPackage, including attribute information, geometry, schema, proper metadata, styles, and the overall bounding box of the layer. With that information, clients are able to locate which files contain records for a given feature entry and bounding box. If a particular geometry spans multiple GeoPackages, it is encoded in all of them. A key column named fid was used to identify the duplicates.

Each feature entry should have separate information for the following reasons:

The idea is borrowed from [MapServer’s ogrtindex tool](https://mapserver.org/optimization/tileindex.html).

Dates in GeoPackage are expressed as ISO-8601 strings. The OS MasterMap database contains a number of dates, making them significant from the point of use of space usage. Participants considered two candidate approaches for reducing the storage space required for these data.

Participants ultimately chose not to pursue either approach and to accept the status quo.

As illustrated by the Zoomstack column sizes, geometries generated the largest columns. The following alternatives were considered and discarded:

The GeoTools gt-gpkg module was able to read and write both vector and raster GeoPackages and had already received performance tuning for reading vector data. The only supported extension was the R-Tree spatial index.

The GeoServer gs-gpkg community module provided GeoPackage output formats for the WMS, WFS and WPS endpoints, in particular:

For the Testbed 16 activity, OS MasterMap topography was delivered as a set of 1694 gzip-compressed GML files with a volume of over 50GB. The first challenge was to translate these files into a GeoPackage. There are two open source tools dedicated to the import of MasterMap GML files:

The import was performed using OS Translator II to load the files into PostGIS. Then a dedicated tool was written to generate GeoPackages from the PostGIS content. Rationale:

A major portion of the GeoSolutions activity involved writing large GeoPackages, ranging from a few GB for a ZoomStack subset to the 200GB required to store the entire MasterMap topography dataset in a single GeoPackage. A number of improvements to the GeoPackage writing code allowed for speeding up the WPS export of GeoPackages, making the export process up to 4 times faster compared to the original code. The improvements were:

As part of the GeoTools core functionality, the GeoPackage module initially supported only the R-Tree spatial indexing extension. The Testbed required the implementation of a variety of extensions, some well known, as part of the core GeoPackage standard. In addition, new extensions were developed during the Testbed to experiment with new functionality. This work required a new extension point in the GeoTools library that would allow for a variety of extensions to be implemented while allowing them to be plugged in. This kept the core module small.

Thus, a new base class GeoPkgExtension was added to the GeoPackage module, providing the basic functionality shared by all extensions registered in the GeoPackage. These include:

The GeoPackage class can look up extensions using the extension identifier. The extensions can be registered as plugins using the Java Service Provider Interface. This allowed placing the extensions in different module based on their state of evolution:

Ultimately the gs-gpkg module is the sole user of all the above extensions, accessing and using them to generate the GeoPackages as required.

The metadata and semantic annotation extension support was used to include the following information in the GeoPackages:

GeoServer layers cannot contain metadata by themselves. However, they can link to existing online metadata.

When a layer links to metadata, the GeoPackage generator downloads the link contents, and places them in the metadata table, making it available for offline consumption. The entry is then associated with the pertinent layer using the metadata reference.

In a similar way, the WPS request gets recorded, stored in the metadata table, and associated to the entire GeoPackage as "Dataset provenance" via semantic annotation. In order to allow update of the GeoPackage contents, one metadata entry is also associated to each layer, providing back-links to a WFS service offering the layer.

The portrayal extension is used to store relevant styles and their resources (e.g. icons) in the GeoPackage. Each style associated to the layer in the GeoServer configuration is included in the GeoPackage:

Finally, a layer might be included in a "Layer group" which is the definition of a set of layers and their stacking order and style to be used. If the layers included in the GeoPackage are part of one or more layer groups, the definition of the group is translated into a OWC operational picture document. This provides the list of layers, their stacking order, and style to be used. In the case of MasterMap Topography, this results in 4 entries in the GeoPackage: One for each of the four style set shared by Ordnance Survey.

When datasets are large, it is common to serve them in a multi-scale fashion, where detailed information is only available at high scales and lower scales depict the most important features. Introduced as part of this Testbed, the generalized tables extension sets up parallel tables with generalized geometries and reduced record contents. These are meant to be displayed at lower scales, providing the client with faster access path to the information needed for map rendering. When properly set up, this leads to a significant difference in read speed.

Following is an excerpt of the WPS request creating two generalized tables for the woodland layer:

The woodland_g1 table is meant to be used in place of the base woodland table starting at a scale of 1:80.000. This table only contains National and Regional woodlands. The woodland_g2 table is meant to be used starting at a scale of 1:320.000 and only contains National woodlands.

For performance reasons, each generalized table is generated from the previous one. This leverages the progressive filtering work already done including the geometry generalization. For more information refer to the generalized tables extension. Full examples of generalized tables WPS requests can be found in the appendix.

To implement the strategy described in Controlling records layout via sorting, the GeoPackage process was extended to sort data before insertion into SQLite, according to one or more sort keys. This is important because SQLite generates the primary key of the table based on insertion order and preserves such order in the final file layout. In particular, GeoPackages with 3 layouts were generated:

The following example shows a WPS request excerpt, sorting the "topographicline" layer by geometry, in other words, by GeoHash:

The following instead sorts the topographicline layer by fid (also known as TID in MasterMap Topography):

GeoSolutions prepared an example of a GeoPackage index and the associated GeoPackage parts. The split of the main GeoPackage has been performed along the 100KM UK national grid, depicted in the following image.

While the squares have the same area, their contents vary significantly, from the smallest, SC.gpkg, weighting only 272KB, to the largest, TQ.gpkg, using 12GB of disk space.

A index GeoPackage has then been created, with the following characteristics:

Building from the work done for portrayal during the Vector Tiles Pilot, the client was updated to support the updated (draft) Portrayal Extension for GeoPackage. Using the proposed Semantic Annotation Extension style references are looked up for each layer using the gpkgext_sa_reference and gpkgext_semantic_annotations tables. The client retrieves the style metadata and content from the gpkgext_styles and gpkgext_stylesheets allowing the client to view and select a style of there choosing. The selection of OS MasterMap and OS Open Zoomstack styles is illustrated in Table 12.

The client allows a user to specify the style to use for each layer and dynamically update the map on selection. SLD style documents were provided for both OS MasterMap and OS Open Zoomstack layers. SLD documents can provide symbol content as an online resource for online scenarios or use symbol references which the client uses to retrieve symbol information using the gpkgext_symbols, gpkext_symbol_content and gpkgext_symbol_content tables. Since most supplied symbols were of the format image/svg+xml and were to drawn based on scale of the map the client caches scaled versions of the image at distinct scale steps to ensure new images were not created for each scale change without significantly degrading size approximation. A demonstration of changing the style of topographicarea for the OS MasterMap dataset is illustrated in Table 13.