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.

Using the semantic Semantic Annotation Extension Stylable Layer Sets were determined and displayed to the user giving them the ability to change the styles of all layers to a common style set or theme. The style sets for OS MasterMap are illustrated in Figure 20.

With the ability to update the map to a defined theme the client dynamically updates the map based on the user selection. Style documents are cached upon use and a cache of style to feature at a particular scale is constructed to improve performance when performing style based filtering in memory. An illustration of OS MasterMap rendering using the outdoor, indoor, light, and backdrop style sets can be seen in Table 14.

Performance and querying improvements for vector data sets were gained by leveraging the scale hints defined in the associated style documents ensuring that the client only requests data when it is appropriate for styling. Using the scale of the map and the MinScaleDenominator and MaxScaleDenomintor as filters a WHERE clause was constructed by converting and combining all the <ogc:Filter> for each applicable style to an appropriate SQL filter definition. Furthermore, layer data will not be queried at all if it does not fall inside of the MinScaleDenominator and MaxScaleDenomintor requirements of the applied stylesheet.

Leveraging the ability to determine stored GeoPackage metadata contextual information the client provides the users the ability to browse and view metadata on the Dataset(GeoPackage) and Table(Layer) levels. Metadata Profiles available in the OS MasterMap and OS Open Zoomstack datasets illustrated in Table 15. All metadata regardless of profile is viewable in the client.

With the new ability to understand the context of the metadata provided using the semantic Semantic Annotation Extension and the Metadata Extension along with the proposed Metadata Profiles concept, new functionality was added depending on the types of profiles in use.

In this section performance metrics are gathered comparing the various techniques used to optimize Large Vector Datasets size and performance. Since we are dealing with large datasets, depending on the scenario, it will be quite common to use mechanical drives. To provide a range of metrics, solid state (SSD) and mechanical drives (HDD) were used. The full hardware details for the desktop machine that the metrics were gathered on include :

Since the OS MasterMap dataset had a defined grid split, it was a perfect candidate to produce a Indexed GeoPackage using the OS MasterMap Segmentation. With the entire dataset being over 200GB it can be beneficial to allow users to supply only the small indexed GeoPackage containing meta-data and styling information, accompanied by only the grids they are interested in. This is most important for mobile users who it many cases will not have this space available on their device or SD card. The client was updated to spatially query the index bounds to select only indexes that intersect the map and to query the appropriate GeoPackage if it exists on disk. Since each GeoPackage has far less features per layer then the full dataset performance gains were expected. 3 grids were chosen with high, medium and low feature density per grid. A comparison between query performance for the full, indexed and geohash sorted datasets can be seen below. During this comparison the solid state drive Samsung SSD 860 was used, allowing for a comparison between performance gains seen on mechanical and solid state drives.

From the above metrics we can infer that the Geohash sorting provides significant perform improvements compared to the standard dataset. Performance improvements increase based on the feature density of the region along with the complexity of the features being queried. The most interesting performance improvements come from the topopgraphicline and topographicarea layers since they have significantly more queried features with varying complexities. The Geohash GeoPackage sees up to 15X improvements in the most dense grid (TQ) while still seeing up to 3X improvements in less the less dense grid (NG)

While the Geohash GeoPackage performance increases with feature density, we see the opposite improvements from the Indexed GeoPackage. In the most dense grid of TQ we see minimal improvements compared to the standard GeoPackage. However, in the medium density grid of SP we begin to see significant improvements of up to 15X. Interestingly, in the lower density grid of NG we see a drop in performance seeing only a 2-3X improvement. The drop in improvement might be attributed to the simplified lines and areas found in this region. It should be noted that as feature density decreased the Indexed GeoPackage begins to perform as well as the Geohash sorted GeoPackage with performance improvements being nearly identical in grid NG.

Comparing the performance gains for the Geohash sorted GeoPackage on the mechanical drive ST1000LX015-1U71 and the solid state drive Samsung SSD 860 for the grid TQ we can see that the user will generally see a further 4-5X increase in performance when all data is stored on a mechanical drive a common scenario when dealing with such large datasets. A full comparison between the Geohash GeoPackage and its original for the the two drives can be seen below in Table 25. The column HDD/SSD represents the added performance gain seen in mechanical drives.

Testbed-16 participants proposed a change request to the GeoPackage Encoding Standard to allow JSON arrays to be encoded into string columns and to allow this to be reflected in the schema. In Testbed-16 this was done by specifying a constraint_type column value of application/json in addition to the previously allowed values of enum, glob, and range. Another recommendation is for more explicit media type that would both specifically identify the data as an array (not just a JSON object) and indicate the data type of the contents. This could be done using media type parameters, e.g., application/json;type=array;items=<itemType>. In this case, itemType would be one of the allowed GeoPackage data types as per GeoPackage Data Types. The proposed updated extension is presented in GeoPackage Schema Extension. This approach was not implemented in Testbed-16 due to lack of time.

Testbed-16 participants proposed a change request to the GeoPackage Encoding Standard to allow Metadata Profiles to be encoded in the gpkg_extensions table as described in Metadata Profiles. This would make the metadata profiles in use discoverable by GeoPackage Clients. As part of this change, a recommendation is that the metadata_standard_uri in gpkg_metadata point to a metadata profile when possible. This change is not believed to pose any interoperability risks, but it would improve the utility of the metadata stored in a GeoPackage. As part of this change, the informative examples currently in Annex F.8 could be converted into profiles.

Testbed-16 participants noted that the style information in an OWS Context follows a different pattern than other content types. There is no notion of "operations" in a style element, even though styles may be published through a service interface as was done in VTP2. The Testbed-16 participants request a change to OWS Context to treat styles like other content types.

Testbed-16 participants used the Semantic Annotations and Portrayal GeoPackage extensions that were developed during prior initiatives. One participant expressed reservations regarding the approach used in Testbed 16 for using semantic annotations to link layers and styles as described in Layer-to-Style Links. The participant noted the following concerns:

The participant provided a counter-proposal for a simple join table called gpkgext_style_link with the following schema:

This would be a very direct way to link the concepts while maintaining referential integrity. An ON DELETE CASCADE clause on the foreign key constraints would allow clients to remove tables or styles without hitting unexpected foreign key violations. However, how this approach would support tiled vector data (aka vector tiles) is not clear. As of the completion of Testbed-16, the proposed extension for tiled vector data calls for a gpkgext_vt_layers table that describes the layers within a tileset of vector data. There are a number of approaches that could work.

Should they decide to move forward in standardizing the Portrayal Extension, the GeoPackage SWG members will have to consider the benefits and drawbacks of the two approaches, .

A generalized table is a feature table that is designed for faster display at lower scales than the base table. A generalized table contains a filtered set of features which can be used without potentially expensive join operations. This table also contains simplified geometries. This approach is designed to improve client performance at the expense of a small increase in GeoPackage size. This extension defines the relationship between base tables and generalized tables. See GeoPackage Generalized Tables Extension.

Since the performance of GeoPackage features data degrades with GeoPackage size, splitting GeoPackages into well-defined partitions is prudent. This approach is useful in scenarios that require opening and processing features data over a small geographic area. The purpose of this extension is to allow for the creation of an index GeoPackage that can point to a set of segment GeoPackages, each containing a partition. A client of the GeoPackage Index extension should be able to inspect the index GeoPackage 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. Then the client can determine which GeoPackages contain features of a particular feature type and bounding box. See GeoPackage Index extension.

In Vector Tiles Pilot (VTP) one and two, participants developed a GeoPackage extension to relationalize information that Mapbox stores in its MBTiles JSON format. Since developers already familiar with the Mapbox ecosystem are accustomed to working with these JSON files, treating these JSON files as another type of GeoPackage metadata (i.e. a new metadata profile) may be prudent. Staying with architectures that people already have code for may ease the path to adoption. This approach could work for both Mapbox and non-Mapbox (e.g., GeoJSON) vector tiles.

The approach proposed in Common Operational Pictures for representing a Common Operational Picture (COP) is fairly complex. A client would have to be able to parse a full OWS Context JSON document and support for that format is not widespread at this time. If the COP is homogeneous, in other words all of the relevant content is stored in GeoPackage, then concepts like offerings and operations that are part of OWS Context are not necessary. Producing a simple GeoPackage extension specifically designed for homogeneous COPs may be prident.

This approach would require two tables:

The full OWS Context could still be used for heterogeneous architectures where some information is in the GeoPackage and some is not.