[gdal-dev] OSM driver with z_order values

[Jukka Rahkonen]

Even Rouault <even.rouault <at> spatialys.com> writes:


> Jeff,
> 
> I've implemented the above idea in latest trunk. I've translated the
osm2pgsql 
> rules (*) for the lines layer. Let me know if that works OK. The 
> implementation of the mechanism is rather generic, so it could potentially be 
> used to do many other things.


Hi,

I made some quick test for seeing the effects of disabling insert triggers
and the creation of the new derived z_order field.

Environment: Windows 7 64-bit, Intel Core i7-3770 3.4 GHz, rotational disks
Test data: Finland-latest.osm.pbf 2014-11-10 (184 MB)
Command:
ogr2ogr -f sqlite -dsco spatialite=yes finland.sqlite finland-latest.osm.pbf
--config OGR_SQLITE_SYNCHRONOUS OFF --config OSM_COMPRESS_NODES YES -lco
SPATIAL_INDEX=NO -progress

GDAL before r27936: 99 seconds
GDAL with r27936: 74 seconds
GDAL with r27936 + create z_order: 86 seconds
Same as above + SPATIAL_INDEX=YES: 197 seconds

Extra test:
GDAL with r27936, no z_order, OSM_MAX_TMPFILE_SIZE 2500 MB: 66 seconds

Heap memory usage with default OSM_MAX_TMPFILE_SIZE: 280 MB
Heap CPU usage: task manager showed 12 % for 8 threads but the i7
"hyperthreads" seemed to be all idle. Still only 25% CPU load if counted by
the 4 real cores.

Conclusions:

- Disabling the insert triggers was a good thing to do
- Creating z_order for linestrings is pretty fast
- 56% of the total time with Spatialite is spent for creating spatial
indexes it they are needed (as they usually are)
- Is is hard to believe in huge improvements in the speed of
PBF->SQLite/Spatialite/GeoPackage conversion with GDAL any more because 
SQLite begins to be the slowest part.
- Compared to the speed of what GDAL is doing the creation of spatial index
inside Spatialite db starts to feel, if not sluggish, but still like
something that could perhaps be faster. However, indexes are created only
once and spending 10 seconds more or less time for that is not of big
importance.

Ideas:

- It is irritating to know that CPU runs 75% idle. I must try to find a
computer with SSD for some further tests.
- For saving a few more seconds when there is free RAM available, could it
be possible to tell GDAL to use SQLite memory db as a target and move it on
disk as the very last step after running the slow CreateSpatialIndex
requests which probably need to do both reads and writes from disk? I could
save 8 seconds by increasing the size of the internal SQLite db from the
default 100 MB to 2500 MB and I somehow feel that there is more to save from
the 111 seconds which are now spent for creating spatial indexes.

-Jukka Rahkonen-