Wednesday, February 19, 2014

OpenJUMP PLUS reads OGC Geopackages

OGC GeoPackage is a new international standard which defines an open, non-proprietary, platform-independent SQLite container for distribution and direct use of geospatial data, including vector features and tile matrix sets. It was adopted by Open Geospatial Consortium (OGC®) as an international standard on 13 February 2014.

Thanks to Larry Reeder, OpenJUMP PLUS has already a fast, native, platform-independent support for reading vector data from OGC GeoPackage data files.


Create GeoPackage with GDAL from a shapefile or OpenStreetMap data


First we need a GeoPackage with some vector data. An excellent tool for creating such is GDAL, which has had an GPKG driver since end of January, 2014. The GPKG driver will be included in GDAL version 1.11 but by now the GeoPackage support is available only in GDAL development versions.  The following instruction shows hot to create GeoPackeges on Windows but GDAL works in a similar way also on Linux and Mac. Just remember that before GDAL 1.11 is released a development version from February 2014 or later is needed.

GDAL-dev versions can be downloaded from http://gisinternals.com/sdk/. A direct link to Windows 64-bit zipfile package is http://gisinternals.com/sdk/Download.aspx?file=release-1600-x64-gdal-mapserver.zip. No installation is needed, just unzip the file to some directory on your disk. After unzipping find the batch file "SDKShell.bat" from the new GDAL main directory.  Double click it and GDAL command window opens.

A shapefile "test.shp" is converted into GeoPackage "test.gpkg" with the following command:

ogr2ogr -f GPKG test.gpkg test.shp

This command converts an OpenStreetMap data extract downloaded from Geofabrik into GeoPackage:

ogr2ogr -f GPKG --config OSM_COMPRESS_NODES YES germany.gpkg germany.osm.pbf -progress

Note!  Beginning from GDAL v. 1.11 and 2.0 the GeoPackage driver creates also spatial indexes for all created vector layers by default.

Read data from GeoPackage with OpenJUMP PLUS



The recent OpenJUMP PLUS snaphots (download from http://sourceforge.net/projects/jump-pilot/files/OpenJUMP_snapshots/) come with DB Query plugin which adds a new menu item into File menu.


 DB Query Plugin is extremely simple and robust. On the other hand, it lacks some fancy features. It does not have a file browser to search where your SQLite/Spatialite/GeoPackage file is. It does not read any metadata tables for building drop down menus. This is the feature that makes DB Query so robust. Missing or faulty metadata don't make it fail - the database query that is sent to the database is exactly what you see in the SQL window.

Without bells and whistles, the DB Query plugin does the job. It sends a query to the database and tries to find geometry BLOBs from the response. The main jar file which takes care of this is merely 47 kB in size and it recognizes geometry blobs in PostGIS, Oracle, MySQL, FDO, Spatialite and GeoPackage formats.


At the moment the GDAL GPKG driver does not support creating spatial or even attribute indexes. However, SQLite database, which is what GeoPackage essentially is, can filter quite fast even without indexes. In this example, walking through 1.2 million polygons and selecting a subset of 3121 features without index took 4 seconds.



DB Query supports almost all that can be done with SQL As Understood By SQLite.  Main exception is that query must not return more than one geometry.

If a record has no geometry at all the plugin creates a null geometry as
"GEOMETRYCOLLECTION EMPTY" and new layer with all the records is added to the OpenJUMP map anyhow.

 User with some knowledge of SQL can utilise free queries as workarounds. For example, run a query and check the table list of the GeoPackage DB from the attributes of a new map layer.


Limitations


The DB Query Plugin is shipped with basic SQLite3 binaries and, as mentioned, that is enough for using SQL As Understood By SQLite. However, the OGC GeoPackage standard introduces some additional SQL functions and those cannot be used with standard DB Query installation. Using the additional functions with OpenJUMP is possible by using the libpgkg SQLite extension by Luciad but that makes installation and running a bit more difficult and platform dependent. Obviously that is worth another OpenJUMP blog post.

Remember also that OpenJUMP is a totally memory bound Java program. For reading big datasets a 64-bit jre and lots of RAM is needed. Even then, reading the 6.1 million OpenStreetMap highways of Germany takes about 5.1 GB of committed memory and makes OpenJUMP to react somewhat slow even on a powerful computer.

More reading:

OpenJUMP DB Query Plugin: http://sourceforge.net/p/jumpdbqplugin/wiki/Documentation/
The page is not up to date
Source code: http://sourceforge.net/projects/jumpdbqplugin/files/jumpdbqplugin/jumpdbqplugin-1.0.0/
OGC GeoPackage standard: http://www.opengeospatial.org/standards/geopackage
GDAL GeoPackage driver: http://www.gdal.org/ogr/drv_geopackage.html
GDAL OpenStreetMap driver: http://gdal.org/ogr/drv_osm.html
GDAL ogr2ogr utility: http://gdal.org/ogr2ogr.html
Luciad libgpkg site: https://bitbucket.org/luciad/libgpkg/overview