3

I downloaded an OpenStreetMap extract for Europe, to a file latest.osm.pbf:

curl https://ftp5.gwdg.de/pub/misc/openstreetmap/download.geofabrik.de/europe-latest.osm.pbf \
-o latest.osm.pbf

I would like to extract all the waterways into a GPKG file. First, I perform the extraction using osmium tags-filter to a file waterways.osm.pbf:

osmium tags-filter -o waterways.osm.pbf latest.osm.pbf waterway

Then, I attempt to perform the .osm.pbf -> .gpkg conversion using ogr2ogr:

ogr2ogr waterway_lines.gpkg waterways.osm.pbf lines

This line produces a file, but the output indicates that something went wrong:

0...10...20...30...40...50...60...70..ERROR 1: failed to prepare SQL: INSERT INTO my_rtree VALUES (?,?,?,?,?)
.80...90...100 - done.

The produced waterway_lines.gpkg does show up in QGIS at low zoom levels:

enter image description here

But it disappears if I zoom in even slightly more.

ogr2ogr --version returns GDAL 3.8.5, released 2024/04/02

How do I fix failed to prepare SQL: INSERT INTO my_rtree VALUES (?,?,?,?,?) error when using ogr2ogr?

2
  • 2
    It seems something goes wrong while creating the spatial index. In a GDAL test I encounter something like that for MacOS ARM64, do you happen to work on that platform? A workaround is probably to drop and recreate the spatial index.
    – Pieter
    Apr 14 at 14:03
  • Yes, I am on MacOS ARM64 :| Thanks for the info!
    – zabop
    Apr 14 at 16:19

1 Answer 1

4

The error is related to this improvement that makes the creation of the spatial index much faster https://github.com/OSGeo/gdal/pull/8596. The idea behind that commit is to create the rtree tables with a custom method into another SQLite in-memory database, and as a final step copy the contents of the temporary tables into the main GeoPackage database. Something seems to fail in this process.

String "my_rtree" appears in a few places in the source code file https://github.com/OSGeo/gdal/blob/95579fe331493a2357062110d97e72be62b5c626/ogr/ogrsf_frmts/gpkg/ogrgeopackagetablelayer.cpp

The error comes probably after row #2933.

A workaround that should work is to create the GeoPackage table without spatial index by using layer creation option -LCO SPATIAL_INDEX=NO. Adding spatial index afterwards with ogrinfo should select another code path that does not use an in-memory database:

ogrinfo -sql "select CreateSpatialIndex(table_name,column_name)" my_gpkg

Consider sending mail to the gdal-dev mailing list and ask what might go wrong. Your dataset is rather large. I see that there is this comment in the source code:

"Too large in-memory RTree. "
"Flushing it and using memory friendly "
"algorithm for the rest"

Maybe the switch into another algorithm does not go fluently and triggers the error. You can test this hypothesis by making a test with a smaller OSM data extract.

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.