Understanding Spatial Indexes in OGC Geopackage files

Wherelytics
Sep 4, 2018 · 2 min read

In our earlier blog we had a look at OGC GeoPackage file. This blog provides an overview of spatial indexes in GeoPackage.

Introduction

GeoPackage uses spatial indexes to improve spatial query performance on vector layers. Through a spatial index you can quickly find all the geometries within an area of interest without having to read through all the rows in the table.

The RTree Spatial Indexes Extension is such an extension that adds capability to index geometry columns using the rtree implementation provided by the SQLite R*Tree Module extension. The spatial index is established by creating a virtual table and a set of triggers.

Creating a spatial index

Using GDAL 2.0 you can easily add a spatial index (RTree) on a table using the specified geometry column through the following function

CreateSpatialIndex('table_name', 'geom_column_name')

ogrinfo -SQL "CreateSpatialIndex('countries', 'geom')" countries.gpkg

Similarly if you are using QGIS3 it is possible to create the spatial index from the Layer Properties Dialog

Query utilizing spatial index

The below query uses plain SQL syntax to query the countries table using the r-tree spatial index.

SELECT id FROM rtree_countries_geom r WHERE r.maxx >= bbox_minx AND r.minx <= bbox_maxx AND r.maxy >= bbox_miny AND r.miny <= bbox_maxy;

So we saw how you can create and query spatial indexes in GeoPackage to support fast access to spatial data. Follow Wherelytics for more articles on GeoPackage.

Wherelytics

Written by

Location Intelligence API platform for developers.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade