Testbed-12 Big Data Database Engineering Report

This OGC® document defines an alternative application of the GeoPackage data model to another database management system, namely PostgreSQL, in order to facilitate the exchange of large tile data stores.

It further evaluates the performance and addresses possible caveats of this transition and discusses alternative approaches of handling large tile data sets.

All questions regarding this document should be directed to the editor or the contributors:

No future work is planned to this document.

Attention is drawn to the possibility that some of the elements of this document may be the subject of patent rights. The Open Geospatial Consortium shall not be held responsible for identifying any or all such patent rights.

Recipients of this document are requested to submit, with their comments, notification of any relevant patent claims or other intellectual property rights of which they may be aware that might be infringed by any implementation of the standard set forth in this document, and to provide supporting documentation.

A platform-independent SQLite database file that contains GeoPackage data and metadata tables with specified definitions, integrity assertions, format limitations and content constraints.

A rectangular pictorial representation of geographic data, often part of a set of such elements, covering a spatially contiguous extent and sharing similar information content and graphical styling, which can be uniquely defined by a pair of indices for the column and row along with an identifier for the tile matrix.

A collection of tiles for a fixed scale.

The software package is named PostgreSQL-GeoPackage and available online at http://github.com/EOX-A/PostgreSQL-GeoPackage [14].

The software package holds scripts used to evaluate the suitability of PostgreSQL to serve as alternative to SQLite for a container of raster tiles as specified in the GeoPackage standard [OGC12-128r11].

Presumably the quickest and easiest way to evaluate the implementation is to use the provided Vagrant configuration. Just follow the Vagrant instructions [15] for a clean environment and connect to it:

Create a PostgreSQL database and load the GeoPackage schema into the PostgreSQL-GeoPackage:

Load a SQLite GeoPackage into the PostgreSQL-GeoPackage, dump it again, and validate the result of the round-trip:

Dump a spatial subset of the PostgreSQL-GeoPackage and validate it by visual comparison to a GDAL generated subset:

Finally, drop the PostgreSQL-GeoPackage from the PostgreSQL database:

The implementation basically is made of four shell scripts, one holding SQL commands and three Python code:

In addition a Vagrant configuration is provided allowing for easy and quick evaluation of the scripts.

The sample SQLite GeoPackage provided with the scripts was created from Sentinel-2 [17] data using GDAL [18].

Legal notice: Contains modified Copernicus Sentinel data [2016] [19].

The scripts are able to load and dump the raster tiles from any SQLite GeoPackage. Typical data are base imagery or raster digital maps i.e., any RGBA coverages.

Tile data stored in PostgreSQL using a table schema as defined by the GeoPackage standard or at least as close to the standard as possible as described above. The GeoPackage standard defines a number of general tables that describe the data like the table gpkg_spatial_ref_sys holding all reference system related information or gpkg_contents describing the data included in the GeoPackage. For tile data the gpkg_contents column of the gpkg_contents table has the value tiles and the table_name column points to the table holding the tile data as binary data. Two additional description tables for tile data are defined which are gpkg_tile_matrix_set and gpkg_tile_matrix defining the tile pyramid extent. The tile data itself is stored in a table of its own for each dataset following again a GeoPackage schema defining the columns id, zoom_level, tile_column, tile_row, and tile_data. The last column holds the binary data. Note that the JPEG or PNG tiles are stored as exactly the same binary data in both, SQLite and PostgreSQL.

The data can be accessed via standard PostgreSQL means as they are configured in the respective system. This might include Python bindings as used by the provided scripts as well as numerous other bindings and direct SQL access.

The data or subsets thereof can also be dumped again as SQLite GeoPackage for further usage via the provided scripts.

The chosen implementation architecture stores the JPEG or PNG tiles as exactly the same binary data in both, SQLite and PostgreSQL. An alternate implementation option that has been evaluated but disregarded for the reasons provided below, is making use of the raster functionality provided by PostGIS.

The fact that software tools like gdal_translate and raster2pgsql are readily available to work with rasters in PostGIS is a big plus. Another benefit would be the support for simple querying and analysis of both rasters and vectors.

However, the raster data is stored in PostgreSQL as PostGIS Raster defined WKB [20]. This means that considerably more storage space is used compared to plain JPEG and PNG as used in GeoPackage. Additionally the data loading and dumping is way slower as there is always data conversion processing needed.

The table below shows some evaluation results using the same data and test setup as used for testing and evaluating the actual implementation in the next section.

The disk size needed to store the data in PostGIS is almost 20 times as the original SQLite GeoPackage file size.

As easily seen by comparing the results with the evaluation results table below, PostGIS is using way more disk space and the data loading is way slower although the time is even measured before applying constraints.

Given these evaluation results the PostGIS option has been disregarded and instead the implementation stores the exact same binary data for the JPEG or PNG tiles as the SQLite GeoPackage.

Two different tests have been performed on a number of SQLite GeoPackages.

The first kind of tests was to perform a full round trip with entire SQLite GeoPackages and check that the content of the SQLite GeoPackage did not change. This means running a gpkg-pg_loadpkg.py followed by a gpkg-pg_dump.py without using the -srcwin parameter and comparing the two SQLite GeoPackages.

Depending on the size of the SQLite GeoPackages the contents can either be dumped into text files using the command sqlite3 <SQLite GeoPackage filename> .dump > text and compared using diff. Alternatively or in addition both SQLite GeoPackages can be loaded in QGIS and visually compared.

Secondly the dumping of spatial subsets has been tested. Spatial subsets generated using the gpkg-pg_dump.py are compared to the same subsets generated using gdal_translate. Note that the -srcwin parameter in gdal_translate works in pixel space instead of the tile index one. In this case only the loading in QGIS and visual comparison can be used as gdal_translate regenerates the tiles which are thus not binary equal any more.

For the first kind of tests the commands below have been used to generate test data together with the following GDAL configuration file gdal_wmts.xml.

The test data comprises a number of growing SQLite GeoPackages covering the whole Earth in EPSG:4326 projection starting from two tiles of 256 x 256 pixels and continuously adding a zoom level with double pixels in each axis.

The commands below have been repeatedly run multiple times to get reliable numbers. The duration numbers reported in the table below are average numbers of at least 5 runs.

The evaluation was run in a virtual machine as configured in the Vagrant configuration. As host a standard office laptop (Lenovo W540) with 16GB memory and a 2.80GHz 4 core Intel i7-4810MQ CPU was used. Note that time and other performance measurements were taken on the same platform at least slightly influencing the results. The following table summarizes the evaluation results:

The smaller GeoPackagaes have been compared after being dumped into text files whereas the bigger ones have only been compared by file size and visually using QGIS as described above. Only minor differences resulting from different floating point number calculations as well as different tile ordering have been observed.

This section highlights interesting observations and key findings from the evaluation results as shown in the table above.

The first interesting observation is the quite different increase in size from SQLite file size to PostgreSQL size on disk varying from just 10% to above 80%. The suspected reason is a suboptimal page size of typically 8KB in PostgreSQL. Note that the average tile size drops from 7KB to just 3.5KB for the bigger GeoPackages and the increase drops dramatically when the average tile size drops below 4KB i.e., half the page size.

The throughput of loading tiles into PostgreSQL-GeoPackages is quite constant with about 3.2-3.7 MB/sec or about 950 tiles/sec. The throughput of dumping tiles, however, is quite spread ranging from peak 10.7 MB/sec or well above 2000 tiles/sec to close to 4 MB/sec or around 1200 tiles/sec. This performance degradation is simply explained by I/O performance. Smaller GeoPackages fit entirely into memory whereas bigger ones not only need I/O for writing but also for reading. This can be easily seen by the output of the time command which shows file system inputs and major page faults (no swaps though) for the bigger GeoPackages but not for the smaller ones.

In conclusion, the loading and dumping performance seems to be limited by I/O performance rather than any processing capabilities. The main task simply is moving data around on hard disk.

Multi-dimensional arrays constitute first-class citizens in rasdaman. An array has some dimension d>0, and along each dimension it has an extent given by integer coordinates with a lower and upper bound (geo coordinates, CRSs, and irregular grids are concepts added by a geosemantics layer implementing OGC W*S standards - see Figure 2). At each coordinate, a cell sits which has some value; all cell values in a given array pertain to some cell type which can be atomic or composite. Arrays are typed along their main characteristics: dimension, extent, and cell type. This allows to distinguish, for example, 2-D panchromatic, RGB, and hyperspectral imagery, elevation, and land use data.

On such arrays, expressions allow to retrieve sub-arrays through trimming and slicing (as in OGC WCS), processing of pixels (such as deriving the NDVI), timeseries analysis, and general signal/image processing andstatistics operations. For example, in rasdaman all WMS, WCS, and WCPS requests are internally mapped to array queries.

This demonstrates a major use case for the query language (see Figure 3): "Array SQL" is not meant to be an end-user language (except for experts), but a convenient client/server interface for tools that allow users to remain in their convenience zone, such as Leaflet, QGIS, python, or NASA WorldWind. Further, within the server it can act as an integration framework for comprehensive services, such as enhancing raster capabilities of MapServer through rasdaman.

There is common consensus among Array Database researchers that arrays need to be partitioned for storage. Often, such partitions are of equal size to ease implementation (called "chunking" in SciDB and others). More general is the tiling approach where each partition can have an individual size and shape. A general investigation on multidimensional tiling has been accomplished by Paula Furtado [7]. She classifies partitioning as follows (see Figure 4):

As this universe of options turned out hard to handle by administrators (in particular with 3D+ situations), Furtado developed several strategies which make modeling easier. Among these are (cf. Figure 5): - regular tiling: this can be applied whenever no particular knowledge exists on the access patterns (i.e., the "query workload") - directional tiling: tiles can be stretched along dimensions by some ratio; for example, timeseries access can be optimized this way - area of interest: in this strategy, the user lists a number of areas which need to be accessible particularly fast; for all the rest of the array the system will do "something meaningful".

In rasdaman, these strategies are available as part of the physical database design through a storage layout sublanguage which is attached to the INSERT statement. For example, the following statement inserts an array and, at the same time, fixes layout to some particular structure, index, and storage strategy:

Optionally, tiles can be compressed individually using either lossless or lossy methods; in the example above, zlib is specified.

Some array operations - concretely: "local operations" in Tomlin’s categorization - are pleasingly parallelizable, something which Array DBMSs exploit massively when dispatching tiles across cores or within a cloud. However, there is more to it: graphics processing units (GPUs), if present, can support in certain class of operations, although not in all.

Even more potential has distributed processing of queries (see Figure 6). In this approach, incoming queries are analyzed individually to find out best split points. Next, subqueries as large as possible are generated and shipped to the nodes that hold the particular data item to be accessed and processed. One criterion for determining the split points is where minimal data transport takes place. Single queries have been split successfully over more than 1,000 cloud nodes [6].

Joins between arrays - occurring, for example, in overlays, matrix multiplication, terrain draping - pose particular challenges and require special algorithms [4].

Figure 7 gives an overview on the history of Array Databases. The historically first Array DBMS was rasdaman [1][2]. Several early approaches addressing arrays have been discontinued (such as Paradise). However, meantime an increasing takeup of the concept can be observed with array systems including TerraLib, SciQL, SciDB, Ophidia, EXTASCID, and several more. The figure below illustrates this development.

Array services have reached a degree of maturity meantime which makes them suitable for standardization. On domain-independent level, ISO is enhancing its SQL query language with any-size multi-dimensional arrays as a new attribute type in tables, together with a set of declarative operators, based on the concepts of the rasdaman Array Algebra and query language. This standard, which will go by the official title ISO 9075 Part 15: SQL/MDA (MDA standing for "Multi-Dimensional Arrays") is expected to be a game changer in Big Science Data [11][9]. SQL/MDA can be expected to become the lingua franca for data access in and across data centers worldwide. Currently it is at Committee Draft (CD) status; expectation is that it will get adopted by summer 2017.

Specifically for spatio-temporal arrays, OGC provides a modular suite of specifications around the notion of coverages [10]. The abstract coverage data model is defined in OGC Abstract Topic 6 (identical to ISO 19123), its concrete, interoperable instantiation is the OGC Coverage Implementation Schema (CIS). Such coverages include spatio-temporal regular and irregular grids, point clouds, and general meshes. The corresponding service model is provided by the OGC Web Coverage Service (WCS) suite which offers functionality ranging from simple subsetting - defined in WCS Core - up to ad-hoc spatio-temporal analytics with OGC Web Coverage Processing Service (WCPS) [3]. All these WCS facets are implemented in rasdaman, and in operational use since several years.

ISO has started adoption of OGC CIS and plans to follow on with OGC WCS. INSPIRE, the European legal framework for a common Spatial Data Infrastructure (SDI), utilizes coverages for orthoimagery, elevation data, as well as ocean and atmospheric data; currently, it is adopting WCS as a so-called "Coverage Download Service", but already seeing the value of WCPS, e.g., for creating visualizations.

In RDA, an Array Database Assessment Working Group (ADA:WG) has been established [12] which is conducting a neutral, thorough hands-on evaluation assessing available Array Database systems and comparable technology …​

The outcome will consist of the ADA:WG report together with the open-source benchmarking software and the services established, will establish a hitherto non-existing overview on the state of the art and best use of Array Databases in science, engineering, and beyond.

Array Databases provide languages for flexible queries on multi-dimensional, spatio-temporal "datacubes", paired with technology which allows for highly effective server-side optimization, parallelization, distributed processing, and use of heterogeneous hardware. After many years of development, they are mature and in operational use on databases exceeding 100 Terabytes and with massive query parallelization.

In short, Array Databases provide the following benefits over ad-hoc crafted silo solutions: * flexibility: any query, anytime * performance and horizontal (cloud!) as well as vertical (GPUs!) scalability * information integration: common query space for data and metadata

In the context of OGC Web services, the "datacube" paradigm of coverages has proven useful for a simple, yet flexible handling of such Big Data. With the open-source rasdaman system, practice-proven technology is available for serving coverage massive datacubes conveniently through OGC standards including WMS, WCS, WCPS, WPS, and others. The rasdaman system is available in open source from http://www.rasdaman.org and can be deployed from source, as RPMs and Debian packages, and as ready-made virtual machines.

Maturity and scalability of the Array Database concept shows in practical use. An online demo [5] showcases WCS and WCPS, provided by rasdaman, in 1-D through 5-D use cases including visual interaction as well as query sandboxes. The EarthServer initiative (http://www.earthserver.eu), a collaboration between Europe, US, and Australia is using rasdaman for establishing a datacube federation on 3-D x/y/t satellite image timeseries and x/y/z/t weather data. Among the participating data centers are European Space Agency (ESA), European Centre for Medium-Range Weather Forecasts (ECMWF), Plymouth Marine Laboratory, and National Computational Infrastructure (NCI) Australia. Currently, rasdaman guarded data holdings exceed 145 Terabytes per database, the Petabyte frontier is planned to be crossed early 2017. All in all, users will get provided with a single common information space on datacubes which can be combined in a "mix and match" style anytime.

The Sensor Web Enablement suite of standards developed by the OGC aims to interoperably integrate sensors and their observation data into web-based (spatial) data infrastructures [14]. The following subsections introduce the core elements of the SWE architecture which comprises specifications for web service interfaces as well as data models and encodings.

While Eventing, Planning and Sensor metadata descriptions do not raise issues for big datasets, the Sensor Observation Service as an possible gateway for a long term observation archive and O&M as an encoding of these observations may impose limitations upon the use of SWE framework components. The following presents question and challenges that need to be answered and resolved in order to deal with large observational data sets in Sensor Web applications.

How can views on big data sets and derived information products be made accessible in the Sensor Web?

How can big observation data sets be processed efficiently and how does the underlying storage structure influence performance?

How can big heterogeneous spatio-temporal datasets be organized, managed, and provided to Sensor Web applications?