It is also possible to use GDAL for creating and dropping spatial indexes of the GeoPackage vector layers and recent GDAL version creates spatial indexes by default. However, this is supported only in GDAL versions beginning from 1.11 and 2.0. The following method works for existing GeoPackages which are created without spatial indexes and it can be used directly from the OpenJUMP DB Query plugin.
How to install, test and use libgpkg with OpenJUMP
Libgpkg is used as a SQLite Run-Time Loadable Extension. Windows users can get ready made binaries directly from libgpkg download page. Linux and Mac users must read the project home page and follow the installation instructions. OpenJUMP users must notice that we are leaving the platform independent zone now and also Windows users must select if they are going to run OpenJUMP on 32-bit or 64-bit jre and download the right binaries.
Place the libgpkg extention (on Windows "gpkg.dll") into directory [home-of-OpenJUMP-PLUS]\lib\ext\. If you plan to run both 32-bit and 64-bit jre sometimes you can rename the files into "gpkg_32.dll" and "gpkg_64.dll" and keep them both available in the directory. Now you are ready to start OpenJUMP but read first the following note:
By now the DB Query Plugin comes with SQLite jdbc driver file "sqlite-jdbc-3.7.2.jar". The libgpkg extension will not work with it. Download a new driver "sqlite-jdbc4-3.8.2-SNAPSHOT.jar" from the Xerial downloads and place the new file into directory [home-of-OpenJUMP-PLUS]\lib\ext\jumpdbquery-1.0.0\. Close OpenJUMP, delete the old jdbc driver and start again.
Now we are ready for a test.
Next something useful that we can't do with the standard SQLite functions. Let's build a spatial query for selecting features which are totally or partly inside a 10 km by 10 km selection box. Or to be exact, whose bounding boxes are totally or partly inside the selection box.
Nine seconds to select 1567 polygons out of 1,2 million. It is not hopeless, but thanks to libgpkg we can be make it a lot faster.
One of the libgpkg functions is "CreateSpatialIndex" which, not surprisingly, creates a spatial index. Let's run it and modify our spatial query so that it utilizes this brand new index.
Much better now. Accurate timing is obviously slightly more than zero seconds but for sure less than one second. It may be surprising, but BBOX queries from SQLite are often considerably faster than from the heavy weight spatial databases. What is interesting is that the query that hits the spatial index is using only standard SQLite functions. If GeoPackage database is delivered so that it contains spatial indexes OpenJUMP can do flaming fast spatial BBOX queries without platform dependent extensions. However, libgpkg is needed for creating the spatial indexes because it is the only open source library that can do it at the moment.
Template for making a spatial query from index
Spatial index in GeoPackage, as well as in Spatialite, does not work transparently as it does in PostGIS. For utilizing the spatial index a spatial query must always contain a subquery which selects the IDs of the candidate features from the index which is actually a virtual table that holds the minx, miny, maxx and maxy values for each feature. The logic of the subquery may feel odd at the beginning but the image below hopefully clarifies why it makes sense to compare minimum bounds of features to maximum bounds of the BBOX and vice versa.
Here is a copy-paste template for selecting all features whose bounding boxes are partly or totally inside the BBOX:
select * from tablename
where rowid in
(select id from rtree_tablename_geom
Sqlite-jdbc driver must be updated.
- Typo in the dll name
- Dll file not on the OpenJUMP search path
- Wrong version of dll-file (32-bit/64-bit)
Missing parameter "?spatialite=" for loading the extension