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
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:
Now the metadata looks like this
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.