9

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

Improve this question
1
  • 1
    gdal should create a spatial index when creating the geopackage (it's the default) but you could check that it's effectively created
    – J.R
    Commented Oct 11, 2019 at 11:49

1 Answer 1

Reset to default
11

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.

Improve this answer
3
  • 2
    Thank you! I'll test and implement this. BTW i'm the maintainer.
    – Michael
    Commented Oct 11, 2019 at 15:23
  • Nice! Please tell also how ArcGIS behaves once you have tested it.
    – user30184
    Commented Oct 11, 2019 at 16:14
  • 3
    The difference between QGIS and ArcGIS Pro is the way, how the data is (A) read and (B) added to a project or map. For A) By defining the geometry, QGIS reads the data faster than before. ArcGIS reads the data out of a GPKG very quick (with or without defining geometry). Now, both are equally fast. For B) QGIS reads the features one by one, which can take its time but it is visualized on going. ArcGIS Pro reads everything at once. First reading the spatial extend takes its time but visualizing is fast. To sum it up: Now, QGIS and ArcGIS has almost the same speed regarding final displaying data
    – Michael
    Commented Oct 11, 2019 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.