GeoPackage rtree leaf order is platform dependent

[visr]

What is the bug?

I am writing GeoPackage files that are tracked in a DVC (data version control) pipeline, which means they are compared by hash. To make them reproducible across time and platforms we need to set OGR_CURRENT_DATE to a fixed date, which is fine, but also currently need SPATIAL_INDEX=NO, which is less ideal, and why I make this issue. As far as I know there is no way to have a cross platform byte identical spatial index since #8596.

Steps to reproduce the issue

I used AI to help me produce a minimum working example, which I ran on different platforms to confirm that it reproduces:
https://github.com/visr/gdal-cross-platform-geopackage

Originally I observed the difference on real data using sqldiff linux.gpkg windows.gpkg, which gave this output:

Details

UPDATE rtree_Node_geom_node SET data=x'0000002400000000000000b547d8fe0047d8fe004902cf804902cf8000000000000000b047dea80047dea800490a1c40490a1c4000000000000000b847e0500047e05000490617f0490617f000000000000000ba47feb00047feb000490384c0490384c000000000000000b4480007004800070049060ec049060ec000000000000000304803a4004803a400491017804910178000000000000000b64806c4004806c4004900bc404900bc400000000000000025480db300480db3004911ff004911ff00000000000000040b480e1700480e17004911ec404911ec40000000000000002d4817f4004817f4004904dc804904dc800000000000000029481b9100481b910049073ac049073ac0000000000000040f481b9100481b91004907348049073480000000000000002b481bc300481bc300490ca640490ca64000000000000000224820410048204100490a0520490a052000000000000004084820640048206400490a09d0490a09d0000000000000002e4823e3004823e300490cd340490cd340000000000000040248277b0048277b00490f6880490f6880000000000000001c4827c6004827c600490f6ec0490f6ec000000000000004094827c6004827c600490572804905728000000000000000234827f8004827f800490559804905598000000000000004034827f8004827f8004908a5404908a540000000000000001d482811004828110049089f0049089f00000000000000004b482ae600482ae60048feee8048feee80000000000000040c482b4a00482b4a0049044cc049044cc00000000000000026482c1200482c120049043a0049043a000000000000000411481b9100481b9100490cb900490cb90000000000000004134817f4004817f4004904d6404904d64000000000000004144823e8004823e800490cd7a0490cd7a000000000000004154803a4004803a40049100b0049100b000000000000000430482b7c00482b7c0048feb00048feb000000000000000049547df480047df4800490a18d0490a18d0000000000000049948009540480095404906776049067760000000000000049a47d8f40047d8f4004902dde04902dde0000000000000049b48078d4048078d404900ead04900ead0000000000000049d47e00f0047e00f004906157049061570000000000000049f47fecb8047fecb804903829049038290000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' WHERE nodeno=2;
UPDATE rtree_Node_geom_node SET data=x'0000002300000000000000ee47959c0047959c0048d2ca8048d2ca8000000000000000dc47c79c0047c79c0048d2e38048d2e38000000000000004c047c8e20047c8e20048d2f68048d2f68000000000000000f447a1308047a1308048d3118048d3118000000000000000ed47b2b78047b2b78048d4318048d4318000000000000000e847c47c0047c47c0048d4350048d4350000000000000004b8479d0800479d080048d45a8048d45a8000000000000000d4479d0200479d020048d47ba048d47ba000000000000004bb47bd4b8047bd4b8048d4b36048d4b36000000000000000d747bd228047bd228048d4d78048d4d78000000000000004bc47c5120047c5120048d5d18048d5d18000000000000000f347d2f00047d2f00048d5de0048d5de0000000000000000da47e09c0047e09c0048d5ea8048d5ea8000000000000000d847c5440047c5440048d5ea8048d5ea8000000000000004be47e09c0047e09c0048d5fde048d5fde000000000000000f047e7a98047e7a98048d6346048d6346000000000000000d947de440047de440048d6808048d6808000000000000004bd47de558047de558048d6858048d6858000000000000000d647c92c0047c92c0048d8428048d8428000000000000004ba47c9900047c9900048d8428048d8428000000000000000db47db880047db880048d8a68048d8a68000000000000004bf47da3e0047da3e0048d8ae0048d8ae0000000000000000f247dde00047dde00048d9df0048d9df0000000000000000c847d4588047d4588048d9e18048d9e18000000000000004ac47d3dc8047d3dc8048d9e9e048d9e9e000000000000000d5479f7f80479f7f8048dabea048dabea0000000000000009f47e74c0047e74c0048dac20048dac200000000000000048447e7548047e7548048dacd2048dacd2000000000000004cc47c44a0047c44a0048d4288048d4288000000000000004d147b2c40047b2c40048d455c048d455c000000000000004d24795d1004795d10048d2e2e048d2e2e000000000000004d447e7328047e7328048d6816048d6816000000000000004d647dddf0047dddf0048d9e1e048d9e1e000000000000004d747d4080047d4080048d645c048d645c000000000000004d847a0ca8047a0ca8048d30c2048d30c20000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' WHERE nodeno=18;
UPDATE rtree_Node_geom_node SET data=x'0000001c0000000000000086482518804825188048d3d02048d3d020000000000000046b482523004825230048d4288048d42880000000000000007e4831de004831de0048d4bdc048d4bdc000000000000004634831ee004831ee0048d4cb0048d4cb000000000000000084481a6cc0481a6cc048d5248048d524800000000000000469481a8b40481a8b4048d540c048d540c000000000000000854827f8004827f80048d5548048d55480000000000000046a4827df004827df0048d59f8048d59f80000000000000046e482d3e00482d3e0048d6358048d635800000000000000089482d2900482d290048d6536048d653600000000000000143482125c0482125c048d7508048d75080000000000000045d482dfcc0482dfcc048d7c9c048d7c9c00000000000000078482e0600482e060048d7de8048d7de80000000000000007f4836bf004836bf0048d7eac048d7eac000000000000004644836e5804836e58048d7f46048d7f460000000000000048648213b0048213b0048d8040048d80400000000000000008a481bd340481bd34048d835c048d835c000000000000000a1482109004821090048d8360048d83600000000000000046f481c0e80481c0e8048d8bd4048d8bd400000000000000459482699404826994048d8ec8048d8ec800000000000000074482977c0482977c048d9814048d981400000000000000098482073004820730048dbba0048dbba00000000000000045a4826e4804826e48048dbd52048dbd520000000000000007548270b0048270b0048dbda6048dbda60000000000000047d4820b3404820b34048dbdf4048dbdf400000000000000097481b5f00481b5f0048de760048de7600000000000000047c481baa00481baa0048de760048de7600000000000000052348219b0048219b0048d7dda048d7dda0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' WHERE nodeno=20;

Versions and provenance

GDAL 3.13.0 "Iowa City", released 2026/05/04
From Conda-Forge, on win-64 and linux-64.

Additional context

No response

[jratike80]

Have you tried to create a spatial index afterwards with SQL https://gdal.org/en/stable/drivers/vector/gpkg.html#sql-functions

CreateSpatialIndex(table_name String, geom_column_name String): creates a spatial index (RTree) on the specified table/geometry column

Otherwise having a cross platform byte identical spatial index feels like a feature that is important for less people than being able to create the index fast. Is it even sure that all platforms are creating byte identical results on all platforms when the 32-bit floating point values are calculated https://sqlite.org/rtree.html? But if it is guaranteed that all platforms create identical rtree, and the negative effect of the suggested change means nanoseconds, so why not.

[visr]

Have you tried to create a spatial index afterwards with SQL

Yes, I have. This is tricky to build into such a pipeline though, as you need to keep track of separate versions for different purposes.

feels like a feature that is important for less people than being able to create the index fast

I agree, but I don't think this will negatively affect speed much. The fix seems like a small adjustment of #8596.

Is it even sure that all platforms are creating byte identical results on all platforms

I verified that the results are byte identical without a spatial index, or with a spatial index on GDAL 3.7, so before #8596, which I added as a case to the MWE repo in visr/gdal-cross-platform-geopackage@7fbd601.

[jratike80]

There may be more platforms than those two which you compared, but I am not a programmer so I can't really say.