GPKG

GPKG is the OGC GeoPackage format for storing vector and raster spatial data within an SQLite database container within a single .gpkg file.   Manifold can import, link, or export to GPKG files.

 

GPKG is not a simple file format like .csv or .shp, but instead is a small DBMS system packaged within a file.   GPKG is implemented as an SQLite database extended with SpatiaLite capabilities, so it requires installation of SQLite software together with SpatiaLite software on any computer on which a GPKG file will be used.   

 

Manifold and Manifold Viewer installation packages automatically include a built-in SQLite implementation that supports GPKG.  Manifold includes support for RTREE indexes, JSON, and GeoPoly, and supports SQLite databases with GPKG-style spatial data.   That makes it effortless to read and write GPKG from Manifold.   If we would like to run native SQLite SQL on a GPKG data source in addition to Manifold SQL, we can do so.  For instructions, see the Notes section at the end of this topic.

 

As an interchange format, GPKG is a well-implemented, capable format that is clearly superior to older formats such as shapefiles.  We can export maps, as well as individual components like drawings,  tables, and images, to GPKG files, saving Style as well.  Manifold can also create Manifold virtual computed fields within GPKG.      Manifold can manipulate GPKG file databases like any other database, including deleting or renaming table fields.

 

We can either import data from GPKG into a Manifold .map project or we can leave the data within the GPKG file and link to it  to enable editing or other use of the data "in place" within the GPKG.   Which we choose will depend on the number of objects involved, the speed of GPKG we can tolerate or not tolerate, and the convenience of keeping data within the GPKG for any interchange requirements.

 

Compared to fast formats, like Manifold .map, GPKG is a slow format for large numbers of objects in drawings and an incredibly slow format for large images.  As a spatial DBMS it is far slower than PostgreSQL.  GPKG is fine as an interchange format for larger numbers of objects but only if we understand that after we import from GPKG we will work with the data either within Manifold (superfast) or save the data within a fast DBMS like PostgreSQL.  

Import

When importing a GPKG file the tables, images and drawings that appear in the Manifold project are Manifold components with no further connection to the GPKG file from which they were imported.  

 

To import from GPKG format:

 

  1. Choose File-Import from the main menu.

  2. In the Import dialog browse to the folder containing data of interest.

  3. Double-click the .gpkg file desired.

  4. Everything found in that .gpkg database will be imported into the project.

 

 

Double-clicking on the desired .gpkg file in the Import dialog as seen above will import into our project everything found in that .gpkg database.  This particular GPKG file is one of the samples published by OGC.

 

 

The .gpkg database contains a drawing and an image.  Those have been imported together with all of the GPKG database infrastructure tables required to organize the DBMS built into the file.   We can double-click on the image or the drawing to open them.

 

 

For a more interesting display, we first create a new data source using a Google street maps imageserver as shown in the Example: An Imageserver Tutorial topic.   We then create a map and drag and drop the Google layer into the map, and then we drag and drop the Veg_DC.geom drawing into the map.    

 

GPKG does a good job of storing and providing coordinate system information so the drawing has the correct coordinate system assigned and appears in the right place in a map along with known good layers such as Google.   The drawing will be re-projected on the fly to match the Pseudo-Mercator projection used by the map.   In the illustration above we have used Style to format the colors of the drawing's areas to provide a more interesting display.

 

 

Zoomed in, the drawing seems to show different vegetation types in Washington, DC, in the United States.

Linking

When linking a GPKG file the tables, images and drawings that appear in that data source in the Manifold project stay resident in the GPKG file.   They are GPKG components even though they may appear in many respects, for the convenience of the user, to be Manifold components.

 

GPKG may be slower than a fast DBMS like PostgreSQL, but it is fast enough to enable native storage of spatial data when there are not too many objects.  In Manifold we can take advantage of that by linking a GPKG file into our project.   The link creates a data source cylinder that indicates the data is stored outside of the project, in the original GPKG file.  When we expand the data source we can see the data within.  This works well for smaller data sets until GPKG gets too slow.

 

To link a GPKG format file:

 

  1. Choose File-Link from the main menu.

  2. In the Link dialog browse to the folder containing data of interest.

  3. Click the .gpkg file desired.

  4. Check or uncheck the Save cache box as desired.

  5. Press Link.  A linked data source will appear in the project.

  6. Press the + icon next to the data source to expand the data source to see the tables, images and drawings it contains.

 

 

The Save cache  button allows setting cache options.

 

 

Most often when linking to a format like GPKG, we will ensure the Save cached box is not checked.  Working with the GPKG will be faster if we check the box, but if we are going to cache data within the project we may as well simply import the GPKG and use full Manifold speed.   We uncheck the box and then we press Link.

 

 

That creates a data source that contains all of the tables and other contents of the .gpkg file's database.  We can double-click on the Veg_DC.geom drawing to open it.

 

 

The drawing opens in default format.    To color it with more appealing fill colors for the areas, we launch Style.

 

 

We use the fid field to automatically color fill color for the areas using the settings above.   We have pressed the Full Range button to calculate the full range of values before pressing the Tally button to generate 16 breaks for applying the palette chosen, the Color Brewer CB Spectral palette.  Press Update Style.

 

 

That colors areas in the drawing using the fid field.  This has zero logical utility: it is just a means of coloring the drawing in a distinctive manner so later on we can see that the styling we have applied is indeed saved within the GPKG file.

 

 

For a more interesting display, we first create a new data source using a Bing street maps imageserver as shown in the Example: An Imageserver Tutorial topic.   We then create a map and drag and drop the Bing layer into the map, and then we drag and drop the drawing from the data source into the map.    The drawing will be re-projected on the fly to match the Pseudo-Mercator coordinate system used by the map.

 

To continue on with this workflow to save the new styling, see the Example: Link GPKG and Save Style topic.

Export

We can export Manifold components to GPKG format, including exporting tables, drawings, and images.    Exporting a drawing or an image automatically exports the drawing's or the image's table, with metadata being written into the GPKG file to support subsequent use of that data within Manifold in the form it was exported, should the exported GPKG file ever be imported or linked back into Manifold.

 

To export to GPKG format:

 

  1. In the Project pane, right-click on the component to export.

  2. Choose Export from the context menu.

  3. Specify a different name if desired.

  4. Choose GPKG Files (*.gpkg) in the Save as type box.

  5. Press Export.

 

When exporting drawings, Manifold will save metadata about the styles in use to the GPKG file.  Other applications will not use that style information, but if the exported GPKG is ever imported or linked into a Manifold project the styles used will be preserved and restored.

Exporting Tables with Multiple Geometry Fields

Manifold tables can have multiple geometry fields, but tables in a GPKG file can have at most one geometry field.   When exporting a table that has multiple geometry fields to GPKG, Manifold will export one such geometry field in the table into GPKG using GPKG's geometry data type while exporting all the other geometry fields into the GPKG file using GPKG's binary data type, and also writing metadata into the GPKG file that marks such binary fields as containing geometry data.    

 

If such a GPKG file is later read or linked into Manifold, Manifold automatically, on the fly will reconstitute the binary fields marked as geometry into Manifold geometry fields, so that the reconstituted table will again contain multiple geometry fields, just like the original table that was exported.   Other software, however, will just see one geometry field and additional binary data fields.  

 

Working with ESRI ST_Geometry within SQLite and GPKG Files

ESRI provides an optional extension library, stgeometry_sqlite.dll, that allows loading ESRI geodatabase tables, that is, ST_Geometry tables, into SQLite and GPKG. That is somewhat of a bizarre idea instead of using ESRI's own file GDB geodatabase, but what the heck, ESRI does it and Manifold supports it as well.  

 

If ESRI's optional extension library is installed into an SQLite or GPKG file database, Manifold supports using ST_Geometry for all operations.   See ESRI's page on adding ST_Geometry to SQLite and GPKG for ESRI information on using ESRI's extension.

Notes

SRID of -1 -  When Manifold links or imports an SQLITE / GPKG database file, the Spatialite SRID of -1 used for unqualified coordinate systems is automatically recognized.

 

Running native SQLite SQL on a GPKG or an SQLite data source - Manifold and Manifold Viewer installation packages automatically include a built-in SQLite implementation that supports GPKG as well as straight SQLite database files.   Built-in support for SQLite allows running either native SQLite SQL or Manifold SQL against an SQLite data source.   See the Example: Switching between Manifold and Native Query Engines topic for an example of using a native SQL in addition to Manifold SQL.

 

RTREE Indexes must Use the Primary Key - Creating an RTREE index in a GPKG data source fails if the index references a field other than the primary key field (such an index referencing a non-primary key field violates the GPKG spec).

 

Coordinate Systems - When copying and pasting a drawing into a GPKG database or when exporting a drawing to GPKG, Manifold writes the coordinate system of the drawing in WKT format into the GPKG system table, where it can be accessed by third-party applications.

 

Virtual Table for Images in GPKG - Linking a GPKG file that contains images exposes an additional virtual table for each image. The virtual table has a fixed structure and includes both a BTREE index on x-y and an RTREE index on x-y-tile. This allows both (a) rendering intermediate levels stored in the database and (b) Alt-clicking into image tiles to see pixel values. The virtual table is read-only. The physical table storing image data is still accessible and writable.

 

Automatic generation of intermediate levels when pasting into tables - Pasting an image table with an RTREE index on the x-y-tile field automatically generates data for intermediate levels.

 

UUID fields - MySQL, SQLite, Oracle, and DB/2 databases support UUID fields as a fixed-length string type.

 

See Also

SQLITE

 

Example: Import from GPKG and Modify Geometry - This topic provides a complete example that shows a simple, but typical, task involving spatial data.  We import a country-sized data set from GeoPackage (GPKG) format, change all areas in the data to the boundary lines for those areas, and then save those boundary lines as a new drawing and table.

 

Example: Link GPKG and Save Style - A companion topic to the GPKG topic.   How to link a GPKG, open a drawing, Style it and then save so the styling is retained within the GPKG file.