Pull to refresh

«Divide and Conquer» for OpenStreetMap world inside PostgreSQL

Level of difficulty Medium
Reading time 28 min
Views 1.6K
Original author: Igor Suhorukov

I will continue the story "How to put the whole world in a regular laptop: PostgreSQL and OpenStreetMap" with secrets about OpenStreetMap geodata, on which many companies have built their business, but not everyone shares the details... Well, today we will open crucial details.

The OSM database in PosgreSQL after loading from the dump takes up more than 587 GB. This is already a large database by the standards of a DBMS, and one huge table for each type of object will not work. For manageability, such data must be partitioned, it's good that PostgreSQL supports declarative data partitioning. It remains only to figure out how to split geographical data. After searching and comparing, the H3 hierarchical hexagonal geospatial indexing system came to rescue. All this was implemented in my openstreetmap_h3 project for fast processing and loading of the world dump into the PostGIS database.

I considered following options from geopartitioning systems:

  • division by administrative borders/countries. The option is computationally intensive at the stage of data preparation and is very conditional, since the political situation and boundaries change. In some regions, internal borders are conditional, such as in the EU.

  • meshing with a rectangular grid. Doesn't work very well due to distortions in the projection of spherical coordinates onto the plane, although the most computationally simple!

  • hierarchical system S2, once popular thanks to the mother company. It uses square cells and is worse than a hexagonal cell in data aggregation and routing tasks.

  • hierarchical H3. And the choice was made in favor of the latter, since this hierarchical H3 indexing system has advantages in preserving distances (approximately the same segment radius anywhere in the world), great for routing tasks, libraries for working with H3 are well supported and constantly evolving and available for PostgreSQL/JVM.

The planet dump is officially published by the OSM organization in XML and PBF formats. The XML format is too big and verbose for planet geodata, and PBF is a protobuf-based binary format with sections (usually compressed) where only one type of object is stored in each individual section: node, way, relation sorted in the order of their ID. In addition, strings are stored in each section of the StringTable in order of frequency of use, and are referenced by tags by number. All this allows OSM to compactly store geometry, metadata and get good block data compression.

Even if we ideally partition all OpenStreetMap data with one of the algorithms, there will still be data and format features that are better taken into account right now. The first is that the lines are stored as references to points, not real coordinate values, so you need to collect ways from nodes objects at the stage of preparing scripts and not load nodes into a table without tags or such nodes that are not referenced by relations objects. I initially did this in my java code, but later I left the processing of the original PBF to the osmium add-locations-to-ways --keep-member-nodes --output-format pbf,pbf_compression=none utility. The second is that the largest amount of data after assembly falls on "ways" object, and I would like to know the heuristics from the data. What do we need to work with the most?

osmworld=# select count(*), 
                  count(tags->'building')*100.0/count(*) as "building_%", 
                  count(tags->'highway')*100.0/count(*) as "highways_%"
            from ways;
   count   |     building_%      |     highways_%      
-----------+---------------------+---------------------
 871619356 | 59.3549956685450202 | 23.4983743293557607
(1 row)

Time: 218694,965 ms (03:38,695)

59% are buildings, 24% are roads and trails of the total number of lines in OpenStreetMap. So let's extract the type attributes into separate columns, and if necessary later, we can put this data into subpartitions. If buildings and roads are not needed for analytics tasks, then their import into the database can be disabled by setting the skip_buildings and skip_highway utility parameters, respectively.

Parsing of the PBF format will be left to the org.openstreetmap.osmosis:osmosis-pbf2 library, but with sections a little later we will tweak it to increase the parallelism of parsing and data processing. For example, the planet-220704.osm.pbf dump contains 1255 sections. The way osmosis-pbf2 works does not suit me and I wrote my own implementation of access to data sections in the program (Splitter, PbfBlobOffsets). This allows you to parallelize parsing and data processing in proportion to the number of sections.

These heuristics helped me split ways, nodes, multipolygon into hundreds of partitions...
         Table          | Rows  |     Total Size     |     Table Size     |  Index(es) Size  |     TOAST Size     
------------------------+-------+--------------------+--------------------+------------------+--------------------
 *** TOTAL ***          | ~1B   | 587 GB (100.00%)   | 501 GB (100.00%)   | 54 GB (100.00%)  | 32 GB (100.00%)
                        |       |                    |                    |                  | 
 ways_051               | ~21M  | 12 GB (2.02%)      | 11 GB (2.14%)      | 1070 MB (1.94%)  | 73 MB (0.22%)
 relation_members       | ~113M | 9523 MB (1.58%)    | 6136 MB (1.20%)    | 3386 MB (6.15%)  | 8192 bytes (0.00%)
 ways_043               | ~14M  | 8102 MB (1.35%)    | 7334 MB (1.43%)    | 737 MB (1.34%)   | 31 MB (0.09%)
 ways_052               | ~14M  | 8013 MB (1.33%)    | 7200 MB (1.40%)    | 729 MB (1.32%)   | 84 MB (0.26%)
 ways_002               | ~17M  | 7923 MB (1.32%)    | 7013 MB (1.37%)    | 870 MB (1.58%)   | 40 MB (0.12%)
 ways_001               | ~15M  | 7360 MB (1.22%)    | 6485 MB (1.26%)    | 788 MB (1.43%)   | 87 MB (0.26%)
 ways_081               | ~14M  | 6963 MB (1.16%)    | 6127 MB (1.19%)    | 716 MB (1.30%)   | 120 MB (0.37%)
 ways_004               | ~13M  | 6886 MB (1.15%)    | 6108 MB (1.19%)    | 691 MB (1.25%)   | 87 MB (0.27%)
 ways_029               | ~11M  | 6824 MB (1.14%)    | 6197 MB (1.21%)    | 567 MB (1.03%)   | 60 MB (0.18%)
 ways_047               | ~11M  | 6750 MB (1.12%)    | 6101 MB (1.19%)    | 577 MB (1.05%)   | 71 MB (0.22%)
 ways_093               | ~13M  | 6522 MB (1.09%)    | 5749 MB (1.12%)    | 644 MB (1.17%)   | 128 MB (0.39%)
 ways_095               | ~13M  | 6451 MB (1.07%)    | 5689 MB (1.11%)    | 671 MB (1.22%)   | 91 MB (0.28%)
 ways_090               | ~12M  | 6405 MB (1.07%)    | 5708 MB (1.11%)    | 620 MB (1.13%)   | 77 MB (0.23%)
 ways_003               | ~12M  | 6401 MB (1.07%)    | 5553 MB (1.08%)    | 641 MB (1.16%)   | 206 MB (0.63%)
 multipolygon_32767     | ~187k | 6366 MB (1.06%)    | 338 MB (0.07%)     | 27 MB (0.05%)    | 6002 MB (18.32%)
 ways_005               | ~12M  | 6317 MB (1.05%)    | 5590 MB (1.09%)    | 601 MB (1.09%)   | 126 MB (0.38%)
 ways_092               | ~12M  | 6227 MB (1.04%)    | 5511 MB (1.07%)    | 610 MB (1.11%)   | 107 MB (0.33%)
 ways_074               | ~11M  | 6208 MB (1.03%)    | 5507 MB (1.07%)    | 587 MB (1.07%)   | 114 MB (0.35%)
 ways_091               | ~11M  | 6158 MB (1.02%)    | 5425 MB (1.06%)    | 578 MB (1.05%)   | 156 MB (0.47%)
 ways_098               | ~12M  | 6154 MB (1.02%)    | 5421 MB (1.06%)    | 595 MB (1.08%)   | 138 MB (0.42%)
 ways_089               | ~11M  | 6128 MB (1.02%)    | 5418 MB (1.06%)    | 576 MB (1.05%)   | 134 MB (0.41%)
 ways_080               | ~11M  | 5973 MB (0.99%)    | 5206 MB (1.01%)    | 549 MB (1.00%)   | 217 MB (0.66%)
 ways_097               | ~11M  | 5940 MB (0.99%)    | 5298 MB (1.03%)    | 547 MB (0.99%)   | 95 MB (0.29%)
 ways_045               | ~11M  | 5877 MB (0.98%)    | 5261 MB (1.03%)    | 546 MB (0.99%)   | 70 MB (0.21%)
 ways_053               | ~9M   | 5810 MB (0.97%)    | 5289 MB (1.03%)    | 487 MB (0.88%)   | 34 MB (0.10%)
 ways_019               | ~10M  | 5794 MB (0.96%)    | 5124 MB (1.00%)    | 517 MB (0.94%)   | 153 MB (0.47%)
 ways_006               | ~10M  | 5774 MB (0.96%)    | 5033 MB (0.98%)    | 510 MB (0.93%)   | 231 MB (0.70%)
 ways_046               | ~10M  | 5702 MB (0.95%)    | 5094 MB (0.99%)    | 505 MB (0.92%)   | 103 MB (0.31%)
 ways_042               | ~10M  | 5683 MB (0.95%)    | 5075 MB (0.99%)    | 496 MB (0.90%)   | 112 MB (0.34%)
 ways_049               | ~9M   | 5663 MB (0.94%)    | 5118 MB (1.00%)    | 456 MB (0.83%)   | 89 MB (0.27%)
 ways_037               | ~9M   | 5634 MB (0.94%)    | 5040 MB (0.98%)    | 447 MB (0.81%)   | 147 MB (0.45%)
 ways_054               | ~9M   | 5627 MB (0.94%)    | 4856 MB (0.95%)    | 485 MB (0.88%)   | 286 MB (0.87%)
 ways_017               | ~10M  | 5605 MB (0.93%)    | 4950 MB (0.97%)    | 507 MB (0.92%)   | 148 MB (0.45%)
 ways_075               | ~9M   | 5587 MB (0.93%)    | 4940 MB (0.96%)    | 488 MB (0.89%)   | 159 MB (0.49%)
 ways_026               | ~9M   | 5585 MB (0.93%)    | 5064 MB (0.99%)    | 482 MB (0.88%)   | 39 MB (0.12%)
 ways_048               | ~9M   | 5584 MB (0.93%)    | 4985 MB (0.97%)    | 484 MB (0.88%)   | 114 MB (0.35%)
 ways_082               | ~10M  | 5548 MB (0.92%)    | 4890 MB (0.95%)    | 500 MB (0.91%)   | 158 MB (0.48%)
 ways_000               | ~9M   | 5460 MB (0.91%)    | 4750 MB (0.93%)    | 461 MB (0.84%)   | 248 MB (0.76%)
 ways_094               | ~10M  | 5457 MB (0.91%)    | 4737 MB (0.92%)    | 493 MB (0.89%)   | 227 MB (0.69%)
 ways_033               | ~8M   | 5454 MB (0.91%)    | 4808 MB (0.94%)    | 403 MB (0.73%)   | 243 MB (0.74%)
 ways_087               | ~9M   | 5445 MB (0.91%)    | 4808 MB (0.94%)    | 463 MB (0.84%)   | 173 MB (0.53%)
 ways_010               | ~9M   | 5357 MB (0.89%)    | 4774 MB (0.93%)    | 440 MB (0.80%)   | 143 MB (0.44%)
 ways_040               | ~9M   | 5348 MB (0.89%)    | 4722 MB (0.92%)    | 467 MB (0.85%)   | 159 MB (0.48%)
 ways_088               | ~9M   | 5262 MB (0.88%)    | 4662 MB (0.91%)    | 455 MB (0.83%)   | 145 MB (0.44%)
 ways_060               | ~8M   | 5243 MB (0.87%)    | 4673 MB (0.91%)    | 420 MB (0.76%)   | 150 MB (0.46%)
 ways_096               | ~10M  | 5207 MB (0.87%)    | 4613 MB (0.90%)    | 495 MB (0.90%)   | 99 MB (0.30%)
 ways_024               | ~8M   | 5205 MB (0.87%)    | 4722 MB (0.92%)    | 431 MB (0.78%)   | 52 MB (0.16%)
 ways_071               | ~9M   | 5198 MB (0.87%)    | 4625 MB (0.90%)    | 469 MB (0.85%)   | 104 MB (0.32%)
 ways_038               | ~9M   | 5193 MB (0.86%)    | 4613 MB (0.90%)    | 463 MB (0.84%)   | 117 MB (0.36%)
 ways_057               | ~8M   | 5161 MB (0.86%)    | 4619 MB (0.90%)    | 415 MB (0.75%)   | 126 MB (0.39%)
 ways_070               | ~8M   | 5124 MB (0.85%)    | 4432 MB (0.86%)    | 424 MB (0.77%)   | 268 MB (0.82%)
 ways_031               | ~8M   | 5124 MB (0.85%)    | 4618 MB (0.90%)    | 435 MB (0.79%)   | 71 MB (0.22%)
 ways_013               | ~8M   | 5098 MB (0.85%)    | 4487 MB (0.87%)    | 396 MB (0.72%)   | 215 MB (0.66%)
 ways_073               | ~9M   | 5091 MB (0.85%)    | 4529 MB (0.88%)    | 479 MB (0.87%)   | 84 MB (0.25%)
 ways_079               | ~8M   | 5088 MB (0.85%)    | 4587 MB (0.89%)    | 404 MB (0.73%)   | 97 MB (0.30%)
 ways_083               | ~8M   | 5038 MB (0.84%)    | 4389 MB (0.86%)    | 402 MB (0.73%)   | 247 MB (0.75%)
 ways_044               | ~9M   | 5029 MB (0.84%)    | 4532 MB (0.88%)    | 461 MB (0.84%)   | 37 MB (0.11%)
 ways_018               | ~8M   | 5020 MB (0.84%)    | 4390 MB (0.86%)    | 415 MB (0.75%)   | 214 MB (0.65%)
 ways_021               | ~8M   | 5003 MB (0.83%)    | 4415 MB (0.86%)    | 414 MB (0.75%)   | 174 MB (0.53%)
 ways_077               | ~8M   | 4991 MB (0.83%)    | 4451 MB (0.87%)    | 416 MB (0.76%)   | 124 MB (0.38%)
 ways_067               | ~8M   | 4990 MB (0.83%)    | 4493 MB (0.88%)    | 425 MB (0.77%)   | 73 MB (0.22%)
 ways_099               | ~9M   | 4983 MB (0.83%)    | 4446 MB (0.87%)    | 475 MB (0.86%)   | 62 MB (0.19%)
 ways_009               | ~8M   | 4935 MB (0.82%)    | 4265 MB (0.83%)    | 390 MB (0.71%)   | 279 MB (0.85%)
 ways_055               | ~7M   | 4933 MB (0.82%)    | 4363 MB (0.85%)    | 380 MB (0.69%)   | 190 MB (0.58%)
 ways_028               | ~9M   | 4915 MB (0.82%)    | 4424 MB (0.86%)    | 445 MB (0.81%)   | 45 MB (0.14%)
 ways_069               | ~7M   | 4913 MB (0.82%)    | 4294 MB (0.84%)    | 378 MB (0.69%)   | 240 MB (0.73%)
 ways_086               | ~8M   | 4900 MB (0.82%)    | 4390 MB (0.86%)    | 393 MB (0.71%)   | 118 MB (0.36%)
 ways_007               | ~7M   | 4889 MB (0.81%)    | 4282 MB (0.83%)    | 375 MB (0.68%)   | 232 MB (0.71%)
 ways_015               | ~7M   | 4847 MB (0.81%)    | 4167 MB (0.81%)    | 354 MB (0.64%)   | 325 MB (0.99%)
 ways_041               | ~8M   | 4836 MB (0.80%)    | 4237 MB (0.83%)    | 392 MB (0.71%)   | 207 MB (0.63%)
 ways_066               | ~7M   | 4832 MB (0.80%)    | 4335 MB (0.85%)    | 376 MB (0.68%)   | 121 MB (0.37%)
 ways_084               | ~8M   | 4823 MB (0.80%)    | 4227 MB (0.82%)    | 411 MB (0.75%)   | 185 MB (0.56%)
 ways_056               | ~7M   | 4819 MB (0.80%)    | 4346 MB (0.85%)    | 379 MB (0.69%)   | 94 MB (0.29%)
 ways_064               | ~8M   | 4764 MB (0.79%)    | 4314 MB (0.84%)    | 404 MB (0.73%)   | 46 MB (0.14%)
 ways_035               | ~8M   | 4709 MB (0.78%)    | 4244 MB (0.83%)    | 421 MB (0.76%)   | 44 MB (0.13%)
 ways_011               | ~7M   | 4703 MB (0.78%)    | 3954 MB (0.77%)    | 348 MB (0.63%)   | 402 MB (1.23%)
 ways_078               | ~7M   | 4687 MB (0.78%)    | 4232 MB (0.82%)    | 377 MB (0.68%)   | 79 MB (0.24%)
 ways_062               | ~7M   | 4670 MB (0.78%)    | 4291 MB (0.84%)    | 358 MB (0.65%)   | 21 MB (0.06%)
 ways_050               | ~8M   | 4660 MB (0.78%)    | 4093 MB (0.80%)    | 388 MB (0.70%)   | 179 MB (0.55%)
 ways_008               | ~6M   | 4553 MB (0.76%)    | 3826 MB (0.75%)    | 311 MB (0.56%)   | 416 MB (1.27%)
 ways_058               | ~6M   | 4518 MB (0.75%)    | 3921 MB (0.76%)    | 321 MB (0.58%)   | 276 MB (0.84%)
 ways_085               | ~7M   | 4515 MB (0.75%)    | 4018 MB (0.78%)    | 368 MB (0.67%)   | 129 MB (0.39%)
 ways_072               | ~8M   | 4493 MB (0.75%)    | 3981 MB (0.78%)    | 419 MB (0.76%)   | 94 MB (0.29%)
 ways_076               | ~7M   | 4490 MB (0.75%)    | 3956 MB (0.77%)    | 365 MB (0.66%)   | 170 MB (0.52%)
 ways_020               | ~6M   | 4452 MB (0.74%)    | 3779 MB (0.74%)    | 290 MB (0.53%)   | 383 MB (1.17%)
 ways_059               | ~6M   | 4363 MB (0.73%)    | 3846 MB (0.75%)    | 318 MB (0.58%)   | 199 MB (0.61%)
 ways_036               | ~8M   | 4360 MB (0.73%)    | 3909 MB (0.76%)    | 398 MB (0.72%)   | 52 MB (0.16%)
 ways_016               | ~6M   | 4333 MB (0.72%)    | 3647 MB (0.71%)    | 292 MB (0.53%)   | 394 MB (1.20%)
 ways_32767             | ~2M   | 4321 MB (0.72%)    | 2618 MB (0.51%)    | 89 MB (0.16%)    | 1615 MB (4.93%)
 ways_068               | ~6M   | 4075 MB (0.68%)    | 3566 MB (0.70%)    | 296 MB (0.54%)   | 214 MB (0.65%)
 ways_025               | ~7M   | 4062 MB (0.68%)    | 3620 MB (0.71%)    | 362 MB (0.66%)   | 81 MB (0.25%)
 ways_039               | ~7M   | 4042 MB (0.67%)    | 3581 MB (0.70%)    | 354 MB (0.64%)   | 107 MB (0.33%)
 ways_012               | ~5M   | 3962 MB (0.66%)    | 3418 MB (0.67%)    | 244 MB (0.44%)   | 301 MB (0.92%)
 ways_065               | ~6M   | 3928 MB (0.65%)    | 3481 MB (0.68%)    | 317 MB (0.57%)   | 130 MB (0.40%)
 ways_023               | ~6M   | 3773 MB (0.63%)    | 3429 MB (0.67%)    | 315 MB (0.57%)   | 29 MB (0.09%)
 ways_014               | ~4M   | 3411 MB (0.57%)    | 3009 MB (0.59%)    | 230 MB (0.42%)   | 172 MB (0.53%)
 ways_061               | ~4M   | 2920 MB (0.49%)    | 2602 MB (0.51%)    | 225 MB (0.41%)   | 93 MB (0.28%)
 osm_file_block_content | ~32M  | 2834 MB (0.47%)    | 2834 MB (0.55%)    | 0 bytes (0.00%)  | 
 ways_030               | ~4M   | 2378 MB (0.40%)    | 2108 MB (0.41%)    | 190 MB (0.34%)   | 81 MB (0.25%)
 ways_022               | ~3M   | 1986 MB (0.33%)    | 1787 MB (0.35%)    | 159 MB (0.29%)   | 40 MB (0.12%)
 nodes_029              | ~8M   | 1959 MB (0.33%)    | 1725 MB (0.34%)    | 234 MB (0.42%)   | 64 kB (0.00%)
 ways_027               | ~3M   | 1863 MB (0.31%)    | 1676 MB (0.33%)    | 170 MB (0.31%)   | 18 MB (0.06%)
 ways_063               | ~3M   | 1782 MB (0.30%)    | 1593 MB (0.31%)    | 135 MB (0.25%)   | 53 MB (0.16%)
 relations              | ~10M  | 1480 MB (0.25%)    | 1477 MB (0.29%)    | 0 bytes (0.00%)  | 3360 kB (0.01%)
 nodes_082              | ~4M   | 1459 MB (0.24%)    | 1342 MB (0.26%)    | 117 MB (0.21%)   | 88 kB (0.00%)
 ways_034               | ~3M   | 1399 MB (0.23%)    | 1247 MB (0.24%)    | 130 MB (0.24%)   | 22 MB (0.07%)
 ways_032               | ~2M   | 1273 MB (0.21%)    | 1109 MB (0.22%)    | 106 MB (0.19%)   | 57 MB (0.18%)
 nodes_051              | ~6M   | 1174 MB (0.20%)    | 1007 MB (0.20%)    | 166 MB (0.30%)   | 168 kB (0.00%)
 nodes_011              | ~5M   | 1105 MB (0.18%)    | 946 MB (0.18%)     | 158 MB (0.29%)   | 96 kB (0.00%)
 nodes_045              | ~4M   | 986 MB (0.16%)     | 874 MB (0.17%)     | 112 MB (0.20%)   | 88 kB (0.00%)
 nodes_069              | ~4M   | 957 MB (0.16%)     | 829 MB (0.16%)     | 128 MB (0.23%)   | 8192 bytes (0.00%)
 multipolygon_012       | ~292k | 914 MB (0.15%)     | 385 MB (0.08%)     | 43 MB (0.08%)    | 486 MB (1.48%)
 nodes_043              | ~4M   | 831 MB (0.14%)     | 710 MB (0.14%)     | 120 MB (0.22%)   | 56 kB (0.00%)
 nodes_037              | ~4M   | 821 MB (0.14%)     | 712 MB (0.14%)     | 109 MB (0.20%)   | 56 kB (0.00%)
 nodes_042              | ~4M   | 800 MB (0.13%)     | 694 MB (0.14%)     | 106 MB (0.19%)   | 64 kB (0.00%)
 nodes_047              | ~4M   | 753 MB (0.13%)     | 633 MB (0.12%)     | 120 MB (0.22%)   | 56 kB (0.00%)
 nodes_064              | ~3M   | 739 MB (0.12%)     | 637 MB (0.12%)     | 102 MB (0.18%)   | 8192 bytes (0.00%)
 nodes_089              | ~3M   | 721 MB (0.12%)     | 634 MB (0.12%)     | 87 MB (0.16%)    | 200 kB (0.00%)
 multipolygon_014       | ~226k | 714 MB (0.12%)     | 301 MB (0.06%)     | 33 MB (0.06%)    | 380 MB (1.16%)
 nodes_053              | ~4M   | 701 MB (0.12%)     | 595 MB (0.12%)     | 106 MB (0.19%)   | 56 kB (0.00%)
 nodes_040              | ~4M   | 700 MB (0.12%)     | 590 MB (0.11%)     | 110 MB (0.20%)   | 64 kB (0.00%)
 nodes_031              | ~3M   | 675 MB (0.11%)     | 585 MB (0.11%)     | 90 MB (0.16%)    | 72 kB (0.00%)
 nodes_052              | ~3M   | 665 MB (0.11%)     | 561 MB (0.11%)     | 105 MB (0.19%)   | 56 kB (0.00%)
 nodes_044              | ~3M   | 634 MB (0.11%)     | 539 MB (0.11%)     | 94 MB (0.17%)    | 24 kB (0.00%)
 nodes_009              | ~3M   | 609 MB (0.10%)     | 505 MB (0.10%)     | 104 MB (0.19%)   | 104 kB (0.00%)
 multipolygon_016       | ~127k | 600 MB (0.10%)     | 196 MB (0.04%)     | 18 MB (0.03%)    | 386 MB (1.18%)
 nodes_048              | ~3M   | 595 MB (0.10%)     | 500 MB (0.10%)     | 94 MB (0.17%)    | 8192 bytes (0.00%)
 multipolygon_018       | ~265k | 578 MB (0.10%)     | 304 MB (0.06%)     | 39 MB (0.07%)    | 235 MB (0.72%)
 multipolygon_015       | ~206k | 576 MB (0.10%)     | 254 MB (0.05%)     | 30 MB (0.05%)    | 293 MB (0.89%)
 multipolygon_021       | ~123k | 568 MB (0.09%)     | 227 MB (0.04%)     | 20 MB (0.04%)    | 321 MB (0.98%)
 nodes_046              | ~3M   | 563 MB (0.09%)     | 479 MB (0.09%)     | 84 MB (0.15%)    | 64 kB (0.00%)
 multipolygon_033       | ~154k | 540 MB (0.09%)     | 274 MB (0.05%)     | 24 MB (0.04%)    | 242 MB (0.74%)
 nodes_035              | ~2M   | 532 MB (0.09%)     | 458 MB (0.09%)     | 74 MB (0.13%)    | 56 kB (0.00%)
 nodes_049              | ~3M   | 524 MB (0.09%)     | 442 MB (0.09%)     | 82 MB (0.15%)    | 56 kB (0.00%)
 nodes_077              | ~3M   | 519 MB (0.09%)     | 432 MB (0.08%)     | 87 MB (0.16%)    | 56 kB (0.00%)
 nodes_010              | ~3M   | 513 MB (0.09%)     | 421 MB (0.08%)     | 91 MB (0.17%)    | 88 kB (0.00%)
 nodes_050              | ~3M   | 468 MB (0.08%)     | 388 MB (0.08%)     | 80 MB (0.15%)    | 8192 bytes (0.00%)
 multipolygon_013       | ~142k | 466 MB (0.08%)     | 203 MB (0.04%)     | 21 MB (0.04%)    | 242 MB (0.74%)
 multipolygon_020       | ~60k  | 465 MB (0.08%)     | 122 MB (0.02%)     | 8296 kB (0.01%)  | 335 MB (1.02%)
 multipolygon_037       | ~133k | 461 MB (0.08%)     | 251 MB (0.05%)     | 20 MB (0.04%)    | 190 MB (0.58%)
 nodes_067              | ~3M   | 459 MB (0.08%)     | 372 MB (0.07%)     | 87 MB (0.16%)    | 24 kB (0.00%)
 nodes_055              | ~2M   | 436 MB (0.07%)     | 361 MB (0.07%)     | 75 MB (0.14%)    | 56 kB (0.00%)
 nodes_076              | ~2M   | 433 MB (0.07%)     | 361 MB (0.07%)     | 72 MB (0.13%)    | 80 kB (0.00%)
 nodes_060              | ~2M   | 433 MB (0.07%)     | 360 MB (0.07%)     | 73 MB (0.13%)    | 24 kB (0.00%)
 nodes_026              | ~2M   | 426 MB (0.07%)     | 359 MB (0.07%)     | 67 MB (0.12%)    | 56 kB (0.00%)
 nodes_078              | ~2M   | 423 MB (0.07%)     | 358 MB (0.07%)     | 65 MB (0.12%)    | 24 kB (0.00%)
 nodes_036              | ~2M   | 421 MB (0.07%)     | 360 MB (0.07%)     | 61 MB (0.11%)    | 8192 bytes (0.00%)
 nodes_024              | ~2M   | 418 MB (0.07%)     | 351 MB (0.07%)     | 67 MB (0.12%)    | 56 kB (0.00%)
 multipolygon_019       | ~249k | 418 MB (0.07%)     | 257 MB (0.05%)     | 37 MB (0.07%)    | 124 MB (0.38%)
 nodes_028              | ~2M   | 403 MB (0.07%)     | 338 MB (0.07%)     | 65 MB (0.12%)    | 56 kB (0.00%)
 nodes_068              | ~2M   | 400 MB (0.07%)     | 343 MB (0.07%)     | 57 MB (0.10%)    | 8192 bytes (0.00%)
 nodes_066              | ~2M   | 392 MB (0.07%)     | 335 MB (0.07%)     | 57 MB (0.10%)    | 56 kB (0.00%)
 multipolygon_011       | ~73k  | 383 MB (0.06%)     | 120 MB (0.02%)     | 10 MB (0.02%)    | 252 MB (0.77%)
 nodes_075              | ~2M   | 380 MB (0.06%)     | 313 MB (0.06%)     | 68 MB (0.12%)    | 88 kB (0.00%)
 nodes_062              | ~2M   | 374 MB (0.06%)     | 322 MB (0.06%)     | 52 MB (0.09%)    | 24 kB (0.00%)
 nodes_088              | ~2M   | 373 MB (0.06%)     | 307 MB (0.06%)     | 65 MB (0.12%)    | 24 kB (0.00%)
 nodes_015              | ~2M   | 371 MB (0.06%)     | 309 MB (0.06%)     | 62 MB (0.11%)    | 256 kB (0.00%)
 nodes_021              | ~2M   | 359 MB (0.06%)     | 295 MB (0.06%)     | 63 MB (0.12%)    | 56 kB (0.00%)
 multipolygon_069       | ~62k  | 355 MB (0.06%)     | 109 MB (0.02%)     | 9608 kB (0.02%)  | 237 MB (0.72%)
 nodes_025              | ~2M   | 351 MB (0.06%)     | 291 MB (0.06%)     | 60 MB (0.11%)    | 64 kB (0.00%)
 nodes_079              | ~2M   | 345 MB (0.06%)     | 291 MB (0.06%)     | 54 MB (0.10%)    | 24 kB (0.00%)
 nodes_033              | ~2M   | 341 MB (0.06%)     | 286 MB (0.06%)     | 55 MB (0.10%)    | 64 kB (0.00%)
 multipolygon_068       | ~50k  | 341 MB (0.06%)     | 90 MB (0.02%)      | 7656 kB (0.01%)  | 244 MB (0.74%)
 nodes_038              | ~2M   | 340 MB (0.06%)     | 274 MB (0.05%)     | 65 MB (0.12%)    | 72 kB (0.00%)
 nodes_087              | ~2M   | 333 MB (0.06%)     | 273 MB (0.05%)     | 59 MB (0.11%)    | 80 kB (0.00%)
 nodes_013              | ~2M   | 331 MB (0.06%)     | 271 MB (0.05%)     | 60 MB (0.11%)    | 128 kB (0.00%)
 multipolygon_070       | ~105k | 317 MB (0.05%)     | 105 MB (0.02%)     | 16 MB (0.03%)    | 196 MB (0.60%)
 nodes_000              | ~2M   | 312 MB (0.05%)     | 251 MB (0.05%)     | 61 MB (0.11%)    | 112 kB (0.00%)
 multipolygon_077       | ~148k | 309 MB (0.05%)     | 171 MB (0.03%)     | 23 MB (0.04%)    | 115 MB (0.35%)
 nodes_023              | ~2M   | 308 MB (0.05%)     | 262 MB (0.05%)     | 46 MB (0.08%)    | 64 kB (0.00%)
 nodes_019              | ~2M   | 308 MB (0.05%)     | 252 MB (0.05%)     | 56 MB (0.10%)    | 24 kB (0.00%)
 nodes_007              | ~2M   | 306 MB (0.05%)     | 247 MB (0.05%)     | 59 MB (0.11%)    | 64 kB (0.00%)
 nodes_041              | ~2M   | 305 MB (0.05%)     | 256 MB (0.05%)     | 50 MB (0.09%)    | 120 kB (0.00%)
 nodes_086              | ~2M   | 305 MB (0.05%)     | 254 MB (0.05%)     | 51 MB (0.09%)    | 24 kB (0.00%)
 nodes_085              | ~2M   | 297 MB (0.05%)     | 246 MB (0.05%)     | 50 MB (0.09%)    | 64 kB (0.00%)
 nodes_014              | ~2M   | 293 MB (0.05%)     | 246 MB (0.05%)     | 47 MB (0.09%)    | 496 kB (0.00%)
 multipolygon_008       | ~50k  | 288 MB (0.05%)     | 77 MB (0.02%)      | 8032 kB (0.01%)  | 204 MB (0.62%)
 nodes_056              | ~2M   | 286 MB (0.05%)     | 234 MB (0.05%)     | 52 MB (0.09%)    | 24 kB (0.00%)
 multipolygon_038       | ~156k | 286 MB (0.05%)     | 173 MB (0.03%)     | 24 MB (0.04%)    | 89 MB (0.27%)
 nodes_012              | ~2M   | 286 MB (0.05%)     | 232 MB (0.05%)     | 52 MB (0.10%)    | 736 kB (0.00%)
 nodes_039              | ~1M   | 275 MB (0.05%)     | 230 MB (0.04%)     | 45 MB (0.08%)    | 72 kB (0.00%)
 multipolygon_058       | ~46k  | 274 MB (0.05%)     | 83 MB (0.02%)      | 6536 kB (0.01%)  | 184 MB (0.56%)
 nodes_083              | ~2M   | 270 MB (0.05%)     | 218 MB (0.04%)     | 52 MB (0.10%)    | 104 kB (0.00%)
 nodes_018              | ~2M   | 270 MB (0.04%)     | 222 MB (0.04%)     | 48 MB (0.09%)    | 64 kB (0.00%)
 nodes_084              | ~2M   | 268 MB (0.04%)     | 217 MB (0.04%)     | 52 MB (0.09%)    | 120 kB (0.00%)
 multipolygon_045       | ~130k | 259 MB (0.04%)     | 155 MB (0.03%)     | 20 MB (0.04%)    | 83 MB (0.25%)
 multipolygon_050       | ~60k  | 255 MB (0.04%)     | 83 MB (0.02%)      | 9736 kB (0.02%)  | 163 MB (0.50%)
 multipolygon_031       | ~84k  | 246 MB (0.04%)     | 129 MB (0.03%)     | 14 MB (0.02%)    | 103 MB (0.31%)
 multipolygon_048       | ~58k  | 245 MB (0.04%)     | 91 MB (0.02%)      | 9520 kB (0.02%)  | 145 MB (0.44%)
 multipolygon_017       | ~102k | 242 MB (0.04%)     | 129 MB (0.03%)     | 15 MB (0.03%)    | 98 MB (0.30%)
 multipolygon_040       | ~64k  | 241 MB (0.04%)     | 83 MB (0.02%)      | 10200 kB (0.02%) | 148 MB (0.45%)
 multipolygon_039       | ~117k | 241 MB (0.04%)     | 122 MB (0.02%)     | 18 MB (0.03%)    | 101 MB (0.31%)
 multipolygon_091       | ~43k  | 237 MB (0.04%)     | 67 MB (0.01%)      | 6432 kB (0.01%)  | 164 MB (0.50%)
 multipolygon_097       | ~54k  | 237 MB (0.04%)     | 80 MB (0.02%)      | 7976 kB (0.01%)  | 149 MB (0.46%)
 nodes_074              | ~1M   | 236 MB (0.04%)     | 197 MB (0.04%)     | 39 MB (0.07%)    | 64 kB (0.00%)
 nodes_058              | ~1M   | 235 MB (0.04%)     | 198 MB (0.04%)     | 37 MB (0.07%)    | 64 kB (0.00%)
 multipolygon_000       | ~51k  | 234 MB (0.04%)     | 79 MB (0.02%)      | 7584 kB (0.01%)  | 147 MB (0.45%)
 nodes_057              | ~2M   | 234 MB (0.04%)     | 187 MB (0.04%)     | 47 MB (0.08%)    | 24 kB (0.00%)
 multipolygon_054       | ~51k  | 232 MB (0.04%)     | 79 MB (0.02%)      | 7760 kB (0.01%)  | 146 MB (0.45%)
 nodes_070              | ~1M   | 229 MB (0.04%)     | 187 MB (0.04%)     | 42 MB (0.08%)    | 120 kB (0.00%)
 multipolygon_041       | ~36k  | 229 MB (0.04%)     | 63 MB (0.01%)      | 5688 kB (0.01%)  | 160 MB (0.49%)
 multipolygon_071       | ~103k | 228 MB (0.04%)     | 122 MB (0.02%)     | 16 MB (0.03%)    | 91 MB (0.28%)
 multipolygon_046       | ~84k  | 226 MB (0.04%)     | 127 MB (0.02%)     | 13 MB (0.02%)    | 86 MB (0.26%)
 multipolygon_047       | ~63k  | 223 MB (0.04%)     | 102 MB (0.02%)     | 10152 kB (0.02%) | 112 MB (0.34%)
 multipolygon_075       | ~62k  | 218 MB (0.04%)     | 90 MB (0.02%)      | 9432 kB (0.02%)  | 119 MB (0.36%)
 nodes_071              | ~1M   | 216 MB (0.04%)     | 175 MB (0.03%)     | 40 MB (0.07%)    | 88 kB (0.00%)
 nodes_054              | ~2M   | 214 MB (0.04%)     | 168 MB (0.03%)     | 46 MB (0.08%)    | 120 kB (0.00%)
 multipolygon_042       | ~56k  | 213 MB (0.04%)     | 88 MB (0.02%)      | 8960 kB (0.02%)  | 116 MB (0.35%)
 nodes_092              | ~1M   | 209 MB (0.03%)     | 170 MB (0.03%)     | 40 MB (0.07%)    | 64 kB (0.00%)
 multipolygon_052       | ~57k  | 207 MB (0.03%)     | 87 MB (0.02%)      | 9288 kB (0.02%)  | 111 MB (0.34%)
 nodes_020              | ~1M   | 207 MB (0.03%)     | 168 MB (0.03%)     | 39 MB (0.07%)    | 24 kB (0.00%)
 nodes_073              | ~1M   | 206 MB (0.03%)     | 173 MB (0.03%)     | 33 MB (0.06%)    | 8192 bytes (0.00%)
 multipolygon_007       | ~64k  | 202 MB (0.03%)     | 81 MB (0.02%)      | 9720 kB (0.02%)  | 111 MB (0.34%)
 nodes_065              | ~1M   | 202 MB (0.03%)     | 171 MB (0.03%)     | 31 MB (0.06%)    | 24 kB (0.00%)
 multipolygon_051       | ~68k  | 202 MB (0.03%)     | 106 MB (0.02%)     | 11 MB (0.02%)    | 85 MB (0.26%)
 nodes_022              | ~987k | 201 MB (0.03%)     | 171 MB (0.03%)     | 30 MB (0.05%)    | 24 kB (0.00%)
 multipolygon_094       | ~32k  | 201 MB (0.03%)     | 46 MB (0.01%)      | 4952 kB (0.01%)  | 150 MB (0.46%)
 nodes_017              | ~1M   | 195 MB (0.03%)     | 158 MB (0.03%)     | 37 MB (0.07%)    | 24 kB (0.00%)
 nodes_094              | ~1M   | 194 MB (0.03%)     | 162 MB (0.03%)     | 32 MB (0.06%)    | 88 kB (0.00%)
 multipolygon_005       | ~36k  | 191 MB (0.03%)     | 50 MB (0.01%)      | 5496 kB (0.01%)  | 135 MB (0.41%)
 multipolygon_049       | ~33k  | 190 MB (0.03%)     | 56 MB (0.01%)      | 5472 kB (0.01%)  | 129 MB (0.39%)
 nodes_091              | ~1M   | 187 MB (0.03%)     | 154 MB (0.03%)     | 33 MB (0.06%)    | 152 kB (0.00%)
 multipolygon_059       | ~34k  | 186 MB (0.03%)     | 66 MB (0.01%)      | 4904 kB (0.01%)  | 115 MB (0.35%)
 multipolygon_083       | ~34k  | 186 MB (0.03%)     | 55 MB (0.01%)      | 4832 kB (0.01%)  | 125 MB (0.38%)
 nodes_061              | ~1M   | 179 MB (0.03%)     | 148 MB (0.03%)     | 32 MB (0.06%)    | 8192 bytes (0.00%)
 multipolygon_009       | ~43k  | 174 MB (0.03%)     | 56 MB (0.01%)      | 6440 kB (0.01%)  | 112 MB (0.34%)
 multipolygon_078       | ~28k  | 169 MB (0.03%)     | 56 MB (0.01%)      | 4664 kB (0.01%)  | 108 MB (0.33%)
 nodes_059              | ~1M   | 165 MB (0.03%)     | 133 MB (0.03%)     | 32 MB (0.06%)    | 24 kB (0.00%)
 multipolygon_085       | ~29k  | 164 MB (0.03%)     | 42 MB (0.01%)      | 4400 kB (0.01%)  | 117 MB (0.36%)
 nodes_008              | ~1M   | 163 MB (0.03%)     | 130 MB (0.03%)     | 33 MB (0.06%)    | 56 kB (0.00%)
 nodes_072              | ~842k | 162 MB (0.03%)     | 136 MB (0.03%)     | 25 MB (0.05%)    | 64 kB (0.00%)
 nodes_093              | ~956k | 158 MB (0.03%)     | 130 MB (0.03%)     | 29 MB (0.05%)    | 104 kB (0.00%)
 multipolygon_030       | ~43k  | 158 MB (0.03%)     | 74 MB (0.01%)      | 6304 kB (0.01%)  | 78 MB (0.24%)
 multipolygon_076       | ~54k  | 155 MB (0.03%)     | 66 MB (0.01%)      | 8576 kB (0.02%)  | 81 MB (0.25%)
 multipolygon_098       | ~49k  | 150 MB (0.02%)     | 56 MB (0.01%)      | 7112 kB (0.01%)  | 87 MB (0.27%)
 multipolygon_055       | ~42k  | 149 MB (0.02%)     | 64 MB (0.01%)      | 6552 kB (0.01%)  | 79 MB (0.24%)
 nodes_030              | ~884k | 149 MB (0.02%)     | 122 MB (0.02%)     | 27 MB (0.05%)    | 56 kB (0.00%)
 multipolygon_029       | ~54k  | 148 MB (0.02%)     | 84 MB (0.02%)      | 8616 kB (0.02%)  | 56 MB (0.17%)
 multipolygon_010       | ~33k  | 147 MB (0.02%)     | 52 MB (0.01%)      | 4928 kB (0.01%)  | 90 MB (0.27%)
 multipolygon_024       | ~23k  | 146 MB (0.02%)     | 33 MB (0.01%)      | 3808 kB (0.01%)  | 109 MB (0.33%)
 multipolygon_084       | ~43k  | 146 MB (0.02%)     | 48 MB (0.01%)      | 6240 kB (0.01%)  | 92 MB (0.28%)
 multipolygon_096       | ~25k  | 145 MB (0.02%)     | 41 MB (0.01%)      | 3824 kB (0.01%)  | 101 MB (0.31%)
 nodes_016              | ~1M   | 145 MB (0.02%)     | 114 MB (0.02%)     | 31 MB (0.06%)    | 24 kB (0.00%)
 multipolygon_044       | ~65k  | 145 MB (0.02%)     | 96 MB (0.02%)      | 10 MB (0.02%)    | 38 MB (0.12%)
 multipolygon_028       | ~44k  | 143 MB (0.02%)     | 57 MB (0.01%)      | 7024 kB (0.01%)  | 79 MB (0.24%)
 multipolygon_053       | ~44k  | 140 MB (0.02%)     | 65 MB (0.01%)      | 7192 kB (0.01%)  | 68 MB (0.21%)
 multipolygon_023       | ~21k  | 135 MB (0.02%)     | 28 MB (0.01%)      | 3720 kB (0.01%)  | 103 MB (0.31%)
 multipolygon_065       | ~30k  | 133 MB (0.02%)     | 50 MB (0.01%)      | 4760 kB (0.01%)  | 78 MB (0.24%)
 nodes_027              | ~770k | 131 MB (0.02%)     | 108 MB (0.02%)     | 23 MB (0.04%)    | 64 kB (0.00%)
 multipolygon_025       | ~29k  | 130 MB (0.02%)     | 44 MB (0.01%)      | 4784 kB (0.01%)  | 82 MB (0.25%)
 nodes_098              | ~763k | 129 MB (0.02%)     | 106 MB (0.02%)     | 23 MB (0.04%)    | 112 kB (0.00%)
 multipolygon_036       | ~30k  | 128 MB (0.02%)     | 67 MB (0.01%)      | 4856 kB (0.01%)  | 57 MB (0.17%)
 multipolygon_043       | ~56k  | 127 MB (0.02%)     | 83 MB (0.02%)      | 9184 kB (0.02%)  | 35 MB (0.11%)
 nodes_081              | ~855k | 124 MB (0.02%)     | 99 MB (0.02%)      | 26 MB (0.05%)    | 56 kB (0.00%)
 multipolygon_035       | ~37k  | 124 MB (0.02%)     | 73 MB (0.01%)      | 6200 kB (0.01%)  | 45 MB (0.14%)
 multipolygon_006       | ~19k  | 122 MB (0.02%)     | 37 MB (0.01%)      | 3016 kB (0.01%)  | 82 MB (0.25%)
 nodes_003              | ~653k | 121 MB (0.02%)     | 101 MB (0.02%)     | 20 MB (0.04%)    | 64 kB (0.00%)
 multipolygon_074       | ~26k  | 115 MB (0.02%)     | 33 MB (0.01%)      | 4184 kB (0.01%)  | 78 MB (0.24%)
 multipolygon_087       | ~30k  | 113 MB (0.02%)     | 43 MB (0.01%)      | 4672 kB (0.01%)  | 66 MB (0.20%)
 multipolygon_060       | ~37k  | 113 MB (0.02%)     | 50 MB (0.01%)      | 5656 kB (0.01%)  | 57 MB (0.17%)
 multipolygon_099       | ~36k  | 112 MB (0.02%)     | 36 MB (0.01%)      | 5784 kB (0.01%)  | 70 MB (0.21%)
 nodes_034              | ~638k | 112 MB (0.02%)     | 92 MB (0.02%)      | 19 MB (0.03%)    | 56 kB (0.00%)
 multipolygon_026       | ~34k  | 111 MB (0.02%)     | 48 MB (0.01%)      | 5680 kB (0.01%)  | 57 MB (0.17%)
 multipolygon_079       | ~32k  | 108 MB (0.02%)     | 43 MB (0.01%)      | 5360 kB (0.01%)  | 60 MB (0.18%)
 multipolygon_089       | ~39k  | 106 MB (0.02%)     | 48 MB (0.01%)      | 5808 kB (0.01%)  | 52 MB (0.16%)
 nodes_006              | ~666k | 105 MB (0.02%)     | 85 MB (0.02%)      | 20 MB (0.04%)    | 136 kB (0.00%)
 multipolygon_092       | ~44k  | 99 MB (0.02%)      | 56 MB (0.01%)      | 6800 kB (0.01%)  | 36 MB (0.11%)
 multipolygon_067       | ~23k  | 99 MB (0.02%)      | 40 MB (0.01%)      | 3688 kB (0.01%)  | 56 MB (0.17%)
 multipolygon_032       | ~25k  | 99 MB (0.02%)      | 39 MB (0.01%)      | 4256 kB (0.01%)  | 56 MB (0.17%)
 multipolygon_088       | ~20k  | 97 MB (0.02%)      | 29 MB (0.01%)      | 3120 kB (0.01%)  | 66 MB (0.20%)
 nodes_095              | ~554k | 95 MB (0.02%)      | 78 MB (0.02%)      | 17 MB (0.03%)    | 64 kB (0.00%)
 multipolygon_066       | ~22k  | 94 MB (0.02%)      | 39 MB (0.01%)      | 3424 kB (0.01%)  | 51 MB (0.16%)
 multipolygon_073       | ~18k  | 93 MB (0.02%)      | 23 MB (0.00%)      | 2856 kB (0.01%)  | 67 MB (0.20%)
 multipolygon_082       | ~37k  | 93 MB (0.02%)      | 40 MB (0.01%)      | 5832 kB (0.01%)  | 47 MB (0.14%)
 multipolygon_086       | ~21k  | 92 MB (0.02%)      | 32 MB (0.01%)      | 3296 kB (0.01%)  | 57 MB (0.17%)
 nodes_080              | ~626k | 92 MB (0.02%)      | 73 MB (0.01%)      | 19 MB (0.03%)    | 80 kB (0.00%)
 multipolygon_072       | ~20k  | 90 MB (0.02%)      | 26 MB (0.00%)      | 3272 kB (0.01%)  | 61 MB (0.19%)
 multipolygon_080       | ~18k  | 88 MB (0.01%)      | 37 MB (0.01%)      | 2880 kB (0.01%)  | 49 MB (0.15%)
 nodes_063              | ~508k | 87 MB (0.01%)      | 71 MB (0.01%)      | 15 MB (0.03%)    | 24 kB (0.00%)
 nodes_099              | ~521k | 85 MB (0.01%)      | 69 MB (0.01%)      | 16 MB (0.03%)    | 192 kB (0.00%)
 nodes_032              | ~447k | 85 MB (0.01%)      | 71 MB (0.01%)      | 13 MB (0.02%)    | 120 kB (0.00%)
 nodes_001              | ~537k | 80 MB (0.01%)      | 64 MB (0.01%)      | 16 MB (0.03%)    | 72 kB (0.00%)
 nodes_097              | ~450k | 80 MB (0.01%)      | 66 MB (0.01%)      | 14 MB (0.02%)    | 88 kB (0.00%)
 multipolygon_034       | ~46k  | 79 MB (0.01%)      | 50 MB (0.01%)      | 7440 kB (0.01%)  | 22 MB (0.07%)
 nodes_090              | ~502k | 77 MB (0.01%)      | 62 MB (0.01%)      | 15 MB (0.03%)    | 72 kB (0.00%)
 multipolygon_003       | ~18k  | 77 MB (0.01%)      | 25 MB (0.00%)      | 2704 kB (0.00%)  | 49 MB (0.15%)
 nodes_096              | ~464k | 76 MB (0.01%)      | 62 MB (0.01%)      | 14 MB (0.03%)    | 56 kB (0.00%)
 multipolygon_061       | ~17k  | 75 MB (0.01%)      | 29 MB (0.01%)      | 2712 kB (0.00%)  | 43 MB (0.13%)
 multipolygon_064       | ~22k  | 71 MB (0.01%)      | 36 MB (0.01%)      | 3704 kB (0.01%)  | 30 MB (0.09%)
 multipolygon_027       | ~16k  | 70 MB (0.01%)      | 29 MB (0.01%)      | 2704 kB (0.00%)  | 39 MB (0.12%)
 multipolygon_081       | ~45k  | 68 MB (0.01%)      | 37 MB (0.01%)      | 6576 kB (0.01%)  | 24 MB (0.07%)
 multipolygon_093       | ~19k  | 68 MB (0.01%)      | 26 MB (0.00%)      | 2848 kB (0.01%)  | 39 MB (0.12%)
 multipolygon_090       | ~19k  | 65 MB (0.01%)      | 23 MB (0.00%)      | 2880 kB (0.01%)  | 39 MB (0.12%)
 multipolygon_022       | ~14k  | 63 MB (0.01%)      | 24 MB (0.00%)      | 2272 kB (0.00%)  | 37 MB (0.11%)
 multipolygon_056       | ~21k  | 63 MB (0.01%)      | 34 MB (0.01%)      | 3064 kB (0.01%)  | 26 MB (0.08%)
 multipolygon_057       | ~15k  | 56 MB (0.01%)      | 23 MB (0.00%)      | 2384 kB (0.00%)  | 30 MB (0.09%)
 multipolygon_062       | ~40k  | 53 MB (0.01%)      | 41 MB (0.01%)      | 6128 kB (0.01%)  | 6216 kB (0.02%)
 multipolygon_001       | ~19k  | 48 MB (0.01%)      | 23 MB (0.00%)      | 3224 kB (0.01%)  | 22 MB (0.07%)
 multipolygon_095       | ~17k  | 47 MB (0.01%)      | 21 MB (0.00%)      | 2552 kB (0.00%)  | 24 MB (0.07%)
 nodes_005              | ~310k | 47 MB (0.01%)      | 37 MB (0.01%)      | 9576 kB (0.02%)  | 96 kB (0.00%)
 multipolygon_004       | ~20k  | 46 MB (0.01%)      | 17 MB (0.00%)      | 3080 kB (0.01%)  | 25 MB (0.08%)
 nodes_004              | ~191k | 38 MB (0.01%)      | 32 MB (0.01%)      | 5888 kB (0.01%)  | 56 kB (0.00%)
 nodes_002              | ~186k | 30 MB (0.01%)      | 25 MB (0.00%)      | 5736 kB (0.01%)  | 8192 bytes (0.00%)
 multipolygon_002       | ~9k   | 23 MB (0.00%)      | 13 MB (0.00%)      | 1480 kB (0.00%)  | 8760 kB (0.03%)
 multipolygon_063       | ~5k   | 21 MB (0.00%)      | 9096 kB (0.00%)    | 808 kB (0.00%)   | 11 MB (0.03%)
 h3_3_bounds_complex    | ~41k  | 17 MB (0.00%)      | 12 MB (0.00%)      | 4832 kB (0.01%)  | 8192 bytes (0.00%)
 osm_file_block         | ~135k | 15 MB (0.00%)      | 15 MB (0.00%)      | 0 bytes (0.00%)  | 
 spatial_ref_sys        | ~9k   | 7280 kB (0.00%)    | 6968 kB (0.00%)    | 304 kB (0.00%)   | 8192 bytes (0.00%)
 key_frequencies        | ~87k  | 4632 kB (0.00%)    | 4624 kB (0.00%)    | 0 bytes (0.00%)  | 8192 bytes (0.00%)
 key_stat_building      | ~23k  | 1112 kB (0.00%)    | 1104 kB (0.00%)    | 0 bytes (0.00%)  | 8192 bytes (0.00%)
 osm_stat_nodes_3_3     | ~14k  | 784 kB (0.00%)     | 776 kB (0.00%)     | 0 bytes (0.00%)  | 8192 bytes (0.00%)
 osm_stat_ways_3_3      | ~14k  | 776 kB (0.00%)     | 768 kB (0.00%)     | 0 bytes (0.00%)  | 8192 bytes (0.00%)
 size_stat              | ~313  | 48 kB (0.00%)      | 40 kB (0.00%)      | 0 bytes (0.00%)  | 8192 bytes (0.00%)
 osm_file_statistics    | ~1    | 8192 bytes (0.00%) | 8192 bytes (0.00%) | 0 bytes (0.00%)  | 

What's inside openstreetmap_h3 under the hood

If this topic is not so deeply interested, then feel free to scroll through the article to the pictures!

When I decided to choose level 3 for the H3 index, the theoretical limit for the number of partitions would be 41162, and the average length of the edge of the hexagon would be 59.811 km. Empirically found out that at level 2 there are huge partitions in Europe, and level 4 is already too detailed and indexes cannot be encoded as int2 in the database, and we strive to keep the data volume compact and the solution good performance.

What to do if the line crosses several cells of the H3 partition grid? In the original version of the software, I set the partition value to null, but now I use the "crutch" - the value 32767 (since some columnar databases are tricky with null values ​​and do not maintain a separate bitmap for them, but use the maximum value from the shot int type), and at the import stage in PostgreSQL, we fill in the h3_3_multi_regions array type field, which lists all the indices that are included in this line.

All this is great, only PostgreSQL allows you to work efficiently only with hundreds of partitions per table, and we got tens of thousands of them. So we need to merge the h3_3 ranges - here the value ranges in the partitions and the enlargement of the PartitionSplitter.java partitions will help us. We will try to collect h3 level 3 partitions into larger level 2 ones and if the thresholdPercentFromMaxPartition threshold is exceeded in fractions of the number of lines from the largest partition, we will create a new partition:

    public static List<Partition> distributeH33ByPartitionsForWays(Map<Short, Long> waysSizeStat, double thresholdPercentFromMaxPartition){
        Map<Short, Partition> h3RangeInH2 = null;
        Map<Short, Short> h33to2 = null;
        try {
            h3RangeInH2 = getH3RangeInH2();
            h33to2 = parseH33to2();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        Map<Short, Short> finalH33to = h33to2;
        Map<Short, Long> sumByH2 = new TreeMap<>(waysSizeStat.entrySet().stream().map(entry -> new AbstractMap.SimpleImmutableEntry<>(finalH33to.get(entry.getKey()), entry.getValue())).collect(Collectors.groupingBy(Map.Entry::getKey,
                Collectors.summingLong(Map.Entry::getValue))));
        long maxPartitionSize = (long) (sumByH2.values().stream().max(Comparator.comparingLong(Long::longValue)).get()* thresholdPercentFromMaxPartition);
        long currentSum=0;
        short lowerBound=LOWER_H3_3_BOUND;
        int partitionNumber=0;
        List<Partition> partitions = new ArrayList<>();
        for(Map.Entry<Short,Long> entry: sumByH2.entrySet()){
            Short h2 = entry.getKey();
            Partition nextRange = h3RangeInH2.get(h2);
            if(currentSum>0 && currentSum+entry.getValue()>=maxPartitionSize){
                short maxRangeOfInterval = nextRange.getMinRange();
                partitions.add(createPartition(waysSizeStat, currentSum, lowerBound, partitionNumber, maxRangeOfInterval));
                System.out.println(partitionNumber++ +"\t["+lowerBound+","+ maxRangeOfInterval +")\t"+currentSum+"\t"+(maxRangeOfInterval-lowerBound));
                lowerBound=nextRange.getMinRange();
                currentSum=entry.getValue();
                continue;
            }
            currentSum+=entry.getValue();
        }
        if(currentSum!=0){
            short maxRangeOfInterval = HIGH_H3_3_BOUND;
            partitions.add(createPartition(waysSizeStat, currentSum, lowerBound, partitionNumber, maxRangeOfInterval));
            System.out.println(partitionNumber+"\t["+lowerBound+","+maxRangeOfInterval+")\t"+currentSum+"\t"+(maxRangeOfInterval-lowerBound));
        }
        return partitions;
    }

I added H3 index values of level 8 to the OSM data for the purposes of aggregation and working with walking distances. The average area of such a cell is about 0.737 km2 and this is the last level that can be encoded in int4. But we will consider such indices only for lines with a length of less than 3 km according to the algorithm:

        Set<Integer> wayIntersectionH38Indexes = null;
        if(!isOneH38 && distanceMeter < 3000.0){
            wayIntersectionH38Indexes = Arrays.stream(h38SourceIdxs).mapToObj(CompactH3::serialize8).collect(Collectors.toCollection(TreeSet::new));
            for (int i = 0; i < h38SourceIdxs.length - 1; i++) {
                wayIntersectionH38Indexes.addAll(h3Core.gridPathCells(h38SourceIdxs[i],h38SourceIdxs[i+1]).stream().map(CompactH3::serialize8).collect(Collectors.toSet()));
            }
            if(closed){
                wayIntersectionH38Indexes.addAll(h3Core.gridPathCells(h38SourceIdxs[0],h38SourceIdxs[h38SourceIdxs.length-1]).stream().map(CompactH3::serialize8).collect(Collectors.toSet()));
                wayIntersectionH38Indexes.addAll(h3Core.polygonToCells(wayNodes.stream().map(wayNode -> new LatLng(wayNode.getLatitude(), wayNode.getLongitude())).collect(toList()), null, 8).stream().map(CompactH3::serialize8).collect(Collectors.toSet()));
            }
            if(wayIntersectionH38Indexes.size() == 1){
                if(wayIntersectionH38Indexes.iterator().next().equals(h38)){
                    wayIntersectionH38Indexes = null;// save only center h38 as it duplicate it
                }
            }
        }

Database Schema

Object field descriptions are in order of alignment of fixed and variable length objects to minimize the amount of data on disk. The postgres_dba utility helped me with these optimizations. I tried to make the table structure as close as possible to the pgSnapshot schema from Osmosis.

CREATE TABLE nodes (
    h3_3 smallint NOT NULL,
    h3_8 integer NOT NULL,
    tags hstore,
    geom  geometry(Point,4326) NOT NULL,
    id bigint NOT NULL
) PARTITION BY RANGE (h3_3);

CREATE TABLE ways (
    closed boolean NOT NULL,
    building boolean NOT NULL,
    highway boolean NOT NULL,
    h3_3 smallint,
    h3_8 integer NOT NULL,
    scale real NOT NULL,
    tags hstore,
    bbox geometry(POLYGON,4326) NOT NULL,
    centre  geometry(Point,4326) NOT NULL,
    id bigint NOT NULL,
    linestring geometry(LINESTRING,4326) NOT NULL,
    points bigint[],
    h3_8_regions integer[]
) PARTITION BY RANGE (h3_3);

CREATE TABLE relations (
    id bigint NOT NULL,
    tags hstore
);

CREATE TABLE relation_members (
    relation_id bigint NOT NULL,
    member_id bigint NOT NULL,
    sequence_id int NOT NULL,
    member_type character(1) NOT NULL,
    member_role text
);
CREATE TABLE multipolygon(
    h3_3 smallint NOT NULL,
    h3_8 integer NOT NULL,
    scale real NOT NULL,
    tags hstore,
    bbox geometry(POLYGON,4326) NOT NULL,
    id bigint NOT NULL,
    center geometry(Point,4326) NOT NULL,
    polygon geometry(MULTIPOLYGON,4326) NOT NULL,
    h3_3_multi_regions smallint[]
) PARTITION BY RANGE (h3_3);
After importing the list of partitions for ways...
                                                   Partitioned table "public.ways"
       Column       |          Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------------+-------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 closed             | boolean                 |           | not null |         | plain    |             |              | 
 h3_3               | smallint                |           |          |         | plain    |             |              | 
 scale              | real                    |           | not null |         | plain    |             |              | 
 tags               | hstore                  |           |          |         | extended |             |              | 
 bbox               | geometry(Geometry,4326) |           | not null |         | main     |             |              | 
 centre             | geometry(Point,4326)    |           | not null |         | main     |             |              | 
 id                 | bigint                  |           | not null |         | plain    |             |              | 
 linestring         | geometry(Geometry,4326) |           | not null |         | main     |             |              | 
 points             | bigint[]                |           |          |         | extended |             |              | 
 h3_3_multi_regions | smallint[]              |           |          |         | extended |             |              | 
Partition key: RANGE (h3_3)
Indexes:
    "idx_ways_id_h33" btree (id, h3_3)
    "idx_ways_scale" btree (scale)
Partitions: ways_000 FOR VALUES FROM ('-32768') TO ('-29912'),
            ways_001 FOR VALUES FROM ('-29912') TO ('-29488'),
            ways_002 FOR VALUES FROM ('-29488') TO ('-29264'),
            ways_003 FOR VALUES FROM ('-29264') TO ('-27104'),
            ways_004 FOR VALUES FROM ('-27104') TO ('-26960'),
            ways_005 FOR VALUES FROM ('-26960') TO ('-26712'),
            ways_006 FOR VALUES FROM ('-26712') TO ('-22752'),
            ways_007 FOR VALUES FROM ('-22752') TO ('-22384'),
            ways_008 FOR VALUES FROM ('-22384') TO ('-22192'),
            ways_009 FOR VALUES FROM ('-22192') TO ('-17584'),
            ways_010 FOR VALUES FROM ('-17584') TO ('-16824'),
            ways_011 FOR VALUES FROM ('-16824') TO ('-8384'),
            ways_012 FOR VALUES FROM ('-8384') TO ('2176'),
            ways_013 FOR VALUES FROM ('2176') TO ('2264'),
            ways_014 FOR VALUES FROM ('2264') TO ('2456'),
            ways_015 FOR VALUES FROM ('2456') TO ('3400'),
            ways_016 FOR VALUES FROM ('3400') TO ('4224'),
            ways_017 FOR VALUES FROM ('4224') TO ('4352'),
            ways_018 FOR VALUES FROM ('4352') TO ('4480'),
            ways_019 FOR VALUES FROM ('4480') TO ('4632'),
            ways_020 FOR VALUES FROM ('4632') TO ('5312'),
            ways_021 FOR VALUES FROM ('5312') TO ('6216'),
            ways_022 FOR VALUES FROM ('6216') TO ('6240'),
            ways_023 FOR VALUES FROM ('6240') TO ('6248'),
            ways_024 FOR VALUES FROM ('6248') TO ('6256'),
            ways_025 FOR VALUES FROM ('6256') TO ('6472'),
            ways_026 FOR VALUES FROM ('6472') TO ('6480'),
            ways_027 FOR VALUES FROM ('6480') TO ('6488'),
            ways_028 FOR VALUES FROM ('6488') TO ('6504'),
            ways_029 FOR VALUES FROM ('6504') TO ('6512'),
            ways_030 FOR VALUES FROM ('6512') TO ('7680'),
            ways_031 FOR VALUES FROM ('7680') TO ('7688'),
            ways_032 FOR VALUES FROM ('7688') TO ('7696'),
            ways_033 FOR VALUES FROM ('7696') TO ('7704'),
            ways_034 FOR VALUES FROM ('7704') TO ('7712'),
            ways_035 FOR VALUES FROM ('7712') TO ('7720'),
            ways_036 FOR VALUES FROM ('7720') TO ('7728'),
            ways_037 FOR VALUES FROM ('7728') TO ('7744'),
            ways_038 FOR VALUES FROM ('7744') TO ('7808'),
            ways_039 FOR VALUES FROM ('7808') TO ('7840'),
            ways_040 FOR VALUES FROM ('7840') TO ('7856'),
            ways_041 FOR VALUES FROM ('7856') TO ('7920'),
            ways_042 FOR VALUES FROM ('7920') TO ('7952'),
            ways_043 FOR VALUES FROM ('7952') TO ('7960'),
            ways_044 FOR VALUES FROM ('7960') TO ('7968'),
            ways_045 FOR VALUES FROM ('7968') TO ('8016'),
            ways_046 FOR VALUES FROM ('8016') TO ('8064'),
            ways_047 FOR VALUES FROM ('8064') TO ('8072'),
            ways_048 FOR VALUES FROM ('8072') TO ('8080'),
            ways_049 FOR VALUES FROM ('8080') TO ('8088'),
            ways_050 FOR VALUES FROM ('8088') TO ('8096'),
            ways_051 FOR VALUES FROM ('8096') TO ('8104'),
            ways_052 FOR VALUES FROM ('8104') TO ('8112'),
            ways_053 FOR VALUES FROM ('8112') TO ('8192'),
            ways_054 FOR VALUES FROM ('8192') TO ('9352'),
            ways_055 FOR VALUES FROM ('9352') TO ('9808'),
            ways_056 FOR VALUES FROM ('9808') TO ('9864'),
            ways_057 FOR VALUES FROM ('9864') TO ('9952'),
            ways_058 FOR VALUES FROM ('9952') TO ('10072'),
            ways_059 FOR VALUES FROM ('10072') TO ('10288'),
            ways_060 FOR VALUES FROM ('10288') TO ('10456'),
            ways_061 FOR VALUES FROM ('10456') TO ('10656'),
            ways_062 FOR VALUES FROM ('10656') TO ('10664'),
            ways_063 FOR VALUES FROM ('10664') TO ('10768'),
            ways_064 FOR VALUES FROM ('10768') TO ('10800'),
            ways_065 FOR VALUES FROM ('10800') TO ('10888'),
            ways_066 FOR VALUES FROM ('10888') TO ('10920'),
            ways_067 FOR VALUES FROM ('10920') TO ('10960'),
            ways_068 FOR VALUES FROM ('10960') TO ('11144'),
            ways_069 FOR VALUES FROM ('11144') TO ('11264'),
            ways_070 FOR VALUES FROM ('11264') TO ('11568'),
            ways_071 FOR VALUES FROM ('11568') TO ('11696'),
            ways_072 FOR VALUES FROM ('11696') TO ('11872'),
            ways_073 FOR VALUES FROM ('11872') TO ('11888'),
            ways_074 FOR VALUES FROM ('11888') TO ('12416'),
            ways_075 FOR VALUES FROM ('12416') TO ('14376'),
            ways_076 FOR VALUES FROM ('14376') TO ('14624'),
            ways_077 FOR VALUES FROM ('14624') TO ('14688'),
            ways_078 FOR VALUES FROM ('14688') TO ('14696'),
            ways_079 FOR VALUES FROM ('14696') TO ('14728'),
            ways_080 FOR VALUES FROM ('14728') TO ('15488'),
            ways_081 FOR VALUES FROM ('15488') TO ('15640'),
            ways_082 FOR VALUES FROM ('15640') TO ('16224'),
            ways_083 FOR VALUES FROM ('16224') TO ('16736'),
            ways_084 FOR VALUES FROM ('16736') TO ('17472'),
            ways_085 FOR VALUES FROM ('17472') TO ('17600'),
            ways_086 FOR VALUES FROM ('17600') TO ('17640'),
            ways_087 FOR VALUES FROM ('17640') TO ('18640'),
            ways_088 FOR VALUES FROM ('18640') TO ('19360'),
            ways_089 FOR VALUES FROM ('19360') TO ('21728'),
            ways_090 FOR VALUES FROM ('21728') TO ('22728'),
            ways_091 FOR VALUES FROM ('22728') TO ('24576'),
            ways_092 FOR VALUES FROM ('24576') TO ('25728'),
            ways_093 FOR VALUES FROM ('25728') TO ('26032'),
            ways_094 FOR VALUES FROM ('26032') TO ('26712'),
            ways_095 FOR VALUES FROM ('26712') TO ('26984'),
            ways_096 FOR VALUES FROM ('26984') TO ('27352'),
            ways_097 FOR VALUES FROM ('27352') TO ('27936'),
            ways_098 FOR VALUES FROM ('27936') TO ('31328'),
            ways_099 FOR VALUES FROM ('31328') TO ('32695'),
            ways_32767 DEFAULT

List of partitions for nodes...
                                           Partitioned table "public.nodes"
 Column |         Type         | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 h3_3   | smallint             |           | not null |         | plain    |             |              | 
 tags   | hstore               |           |          |         | extended |             |              | 
 geom   | geometry(Point,4326) |           | not null |         | main     |             |              | 
 id     | bigint               |           | not null |         | plain    |             |              | 
Partition key: RANGE (h3_3)
Indexes:
    "idx_nodes_id_h33" btree (id, h3_3)
Partitions: nodes_000 FOR VALUES FROM ('-32768') TO ('-29912'),
            nodes_001 FOR VALUES FROM ('-29912') TO ('-29488'),
            nodes_002 FOR VALUES FROM ('-29488') TO ('-29264'),
            nodes_003 FOR VALUES FROM ('-29264') TO ('-27104'),
            nodes_004 FOR VALUES FROM ('-27104') TO ('-26960'),
            nodes_005 FOR VALUES FROM ('-26960') TO ('-26712'),
            nodes_006 FOR VALUES FROM ('-26712') TO ('-22752'),
            nodes_007 FOR VALUES FROM ('-22752') TO ('-22384'),
            nodes_008 FOR VALUES FROM ('-22384') TO ('-22192'),
            nodes_009 FOR VALUES FROM ('-22192') TO ('-17584'),
            nodes_010 FOR VALUES FROM ('-17584') TO ('-16824'),
            nodes_011 FOR VALUES FROM ('-16824') TO ('-8384'),
            nodes_012 FOR VALUES FROM ('-8384') TO ('2176'),
            nodes_013 FOR VALUES FROM ('2176') TO ('2264'),
            nodes_014 FOR VALUES FROM ('2264') TO ('2456'),
            nodes_015 FOR VALUES FROM ('2456') TO ('3400'),
            nodes_016 FOR VALUES FROM ('3400') TO ('4224'),
            nodes_017 FOR VALUES FROM ('4224') TO ('4352'),
            nodes_018 FOR VALUES FROM ('4352') TO ('4480'),
            nodes_019 FOR VALUES FROM ('4480') TO ('4632'),
            nodes_020 FOR VALUES FROM ('4632') TO ('5312'),
            nodes_021 FOR VALUES FROM ('5312') TO ('6216'),
            nodes_022 FOR VALUES FROM ('6216') TO ('6240'),
            nodes_023 FOR VALUES FROM ('6240') TO ('6248'),
            nodes_024 FOR VALUES FROM ('6248') TO ('6256'),
            nodes_025 FOR VALUES FROM ('6256') TO ('6472'),
            nodes_026 FOR VALUES FROM ('6472') TO ('6480'),
            nodes_027 FOR VALUES FROM ('6480') TO ('6488'),
            nodes_028 FOR VALUES FROM ('6488') TO ('6504'),
            nodes_029 FOR VALUES FROM ('6504') TO ('6512'),
            nodes_030 FOR VALUES FROM ('6512') TO ('7680'),
            nodes_031 FOR VALUES FROM ('7680') TO ('7688'),
            nodes_032 FOR VALUES FROM ('7688') TO ('7696'),
            nodes_033 FOR VALUES FROM ('7696') TO ('7704'),
            nodes_034 FOR VALUES FROM ('7704') TO ('7712'),
            nodes_035 FOR VALUES FROM ('7712') TO ('7720'),
            nodes_036 FOR VALUES FROM ('7720') TO ('7728'),
            nodes_037 FOR VALUES FROM ('7728') TO ('7744'),
            nodes_038 FOR VALUES FROM ('7744') TO ('7808'),
            nodes_039 FOR VALUES FROM ('7808') TO ('7840'),
            nodes_040 FOR VALUES FROM ('7840') TO ('7856'),
            nodes_041 FOR VALUES FROM ('7856') TO ('7920'),
            nodes_042 FOR VALUES FROM ('7920') TO ('7952'),
            nodes_043 FOR VALUES FROM ('7952') TO ('7960'),
            nodes_044 FOR VALUES FROM ('7960') TO ('7968'),
            nodes_045 FOR VALUES FROM ('7968') TO ('8016'),
            nodes_046 FOR VALUES FROM ('8016') TO ('8064'),
            nodes_047 FOR VALUES FROM ('8064') TO ('8072'),
            nodes_048 FOR VALUES FROM ('8072') TO ('8080'),
            nodes_049 FOR VALUES FROM ('8080') TO ('8088'),
            nodes_050 FOR VALUES FROM ('8088') TO ('8096'),
            nodes_051 FOR VALUES FROM ('8096') TO ('8104'),
            nodes_052 FOR VALUES FROM ('8104') TO ('8112'),
            nodes_053 FOR VALUES FROM ('8112') TO ('8192'),
            nodes_054 FOR VALUES FROM ('8192') TO ('9352'),
            nodes_055 FOR VALUES FROM ('9352') TO ('9808'),
            nodes_056 FOR VALUES FROM ('9808') TO ('9864'),
            nodes_057 FOR VALUES FROM ('9864') TO ('9952'),
            nodes_058 FOR VALUES FROM ('9952') TO ('10072'),
            nodes_059 FOR VALUES FROM ('10072') TO ('10288'),
            nodes_060 FOR VALUES FROM ('10288') TO ('10456'),
            nodes_061 FOR VALUES FROM ('10456') TO ('10656'),
            nodes_062 FOR VALUES FROM ('10656') TO ('10664'),
            nodes_063 FOR VALUES FROM ('10664') TO ('10768'),
            nodes_064 FOR VALUES FROM ('10768') TO ('10800'),
            nodes_065 FOR VALUES FROM ('10800') TO ('10888'),
            nodes_066 FOR VALUES FROM ('10888') TO ('10920'),
            nodes_067 FOR VALUES FROM ('10920') TO ('10960'),
            nodes_068 FOR VALUES FROM ('10960') TO ('11144'),
            nodes_069 FOR VALUES FROM ('11144') TO ('11264'),
            nodes_070 FOR VALUES FROM ('11264') TO ('11568'),
            nodes_071 FOR VALUES FROM ('11568') TO ('11696'),
            nodes_072 FOR VALUES FROM ('11696') TO ('11872'),
            nodes_073 FOR VALUES FROM ('11872') TO ('11888'),
            nodes_074 FOR VALUES FROM ('11888') TO ('12416'),
            nodes_075 FOR VALUES FROM ('12416') TO ('14376'),
            nodes_076 FOR VALUES FROM ('14376') TO ('14624'),
            nodes_077 FOR VALUES FROM ('14624') TO ('14688'),
            nodes_078 FOR VALUES FROM ('14688') TO ('14696'),
            nodes_079 FOR VALUES FROM ('14696') TO ('14728'),
            nodes_080 FOR VALUES FROM ('14728') TO ('15488'),
            nodes_081 FOR VALUES FROM ('15488') TO ('15640'),
            nodes_082 FOR VALUES FROM ('15640') TO ('16224'),
            nodes_083 FOR VALUES FROM ('16224') TO ('16736'),
            nodes_084 FOR VALUES FROM ('16736') TO ('17472'),
            nodes_085 FOR VALUES FROM ('17472') TO ('17600'),
            nodes_086 FOR VALUES FROM ('17600') TO ('17640'),
            nodes_087 FOR VALUES FROM ('17640') TO ('18640'),
            nodes_088 FOR VALUES FROM ('18640') TO ('19360'),
            nodes_089 FOR VALUES FROM ('19360') TO ('21728'),
            nodes_090 FOR VALUES FROM ('21728') TO ('22728'),
            nodes_091 FOR VALUES FROM ('22728') TO ('24576'),
            nodes_092 FOR VALUES FROM ('24576') TO ('25728'),
            nodes_093 FOR VALUES FROM ('25728') TO ('26032'),
            nodes_094 FOR VALUES FROM ('26032') TO ('26712'),
            nodes_095 FOR VALUES FROM ('26712') TO ('26984'),
            nodes_096 FOR VALUES FROM ('26984') TO ('27352'),
            nodes_097 FOR VALUES FROM ('27352') TO ('27936'),
            nodes_098 FOR VALUES FROM ('27936') TO ('31328'),
            nodes_099 FOR VALUES FROM ('31328') TO ('32695')

Diagrams of the largest tables/partitions:

Let's look at a table sample where lines are stored that have more than one index H3_3

select linestring from ways_32767 TABLESAMPLE SYSTEM(10)

And now we visualize the polygons of the cells included in ways_000:

The largest partition according to data ways_051

select 
   h3_to_geo_boundary_geometry(
     to_full_from_short_h3_3(h3_3)::h3index) as outline 
   from (select distinct h3_3 from ways_051) subquery

Multipolygons are collected in a separate table select polygon from multipolygon TABLESAMPLE BERNOULLI(1)

The most interesting "easter egg" of my utility is the partition grid in the form of a regular table: select * from h3_3_bounds_complex

As an example, if we need to analyze data only within the Moscow Ring Road, then we can safely add h3_3=4522 to queries (not forgetting the lines belonging to several regions ways.h3_3_multi_regions).

Summary

A new high-performance tool openstreetmap_h3 is now available for analytics based on OpenStreetMap data, which allows you to load the whole world into PostgreSQL / PostGIS, takes advantage of the latest PostgreSQL versions to work with declarative partitions. At the same time, when splitting the data, their geographical proximity is taken into account based on the H3 indices. This allows both accessing all the data at once and parallelizing the execution of the query, and specifying the geoindex of the operation area in SQL, and PostgreSQL will access the data of only the necessary partitions out of the hundreds available in the database.

Tags:
Hubs:
0
Comments 0
Comments Leave a comment

Articles