GDAL and GeoPackage – Overcoming Slow Reading of Large Feature Classes

gdalgeopackageogr2ogr

I'm running a project which provides OpenStreetMap data on a global scale as GeoPackages. The data is created/transferred from a PostGIS database to GeoPackage by ogr2ogr.

Issue: Initially reading of a GeoPackage takes several minutes in QGIS, ArcGIS, or other software. For comparison, the same data can be read in under a second when stored as a File Geodatabase.

Is there a way to improve the initial read speed of feature classes stored in a GeoPackage (created by ogr2ogr, if that is relevant)?

Example data can be downloaded here: https://download.osmdata.xyz

Best Answer

Those GeoPackages do have spatial indexes ready but there is a small glitch in how the GeoPackage databases are created. That can be seen by reading the contents of the gpkg_geometry_columns table

SELECT ROWID, "table_name", "column_name", "geometry_type_name", "srs_id", "z", "m" FROM "gpkg_geometry_columns" ORDER BY ROWID

enter image description here

As you can see, all three tables (point, line, polygon) are registered into geometry_columns to include general geometries. QGIS, that can only handle one kind of geometries on the same layer, wants to know what geometries the source data contains and because it can't get that information from the metadata it must read all the geometries and check them one by one and that's slow.

You can create a GeoPackage that suits better for QGIS with these commands:

ogr2ogr -f gpkg test.gpkg railway_EPSG4326.gpkg railway_EPSG4326_point -nlt point

ogr2ogr -f gpkg -append -update test.gpkg railway_EPSG4326.gpkg railway_EPSG4326_line -nlt multilinestring

ogr2ogr -f gpkg -append -update test.gpkg railway_EPSG4326.gpkg railway_EPSG4326_polygon -nlt multipolygon

Now the metadata looks like this

enter image description here

and QGIS can make a connection and add data into the project in seconds while it used to take about one minute with my computer.

With just this database it would be enough to update the GEOMETRY_TYPE_NAME column to have values POINT, LINESTRING and POLYGON but generally that is not a safe thing to do.

Consider to report these findings to the maintainers of the osmdata.xyz site.

Related Question