Продолжу рассказ "Как поместить весь мир в обычный ноутбук: PostgreSQL и OpenStreetMap" секретами о геоданных OpenStreetMap, на которых множество компаний построили бизнес но не все делятся подробностями... Что ж, сегодня приоткроем завесу!
База данных в PosgreSQL после загрузки из дампа занимает больше 587 GB. Это уже по меркам СУБД большая база и одна огромная таблица на каждый тип объектов не сработает. Для управляемости такие данные надо секционировать, хорошо что PostgreSQL поддерживает декларативное секционирование данных. Осталось лишь придумать как разделить географические данные. После поисков и сравнений мне на помощь пришла иерархическая гексагональная геопространственная система индексирования H3. Все это было реализовано в моем проекте openstreetmap_h3 для быстрой обработки и загрузки мира в базу.
Из систем геопартиционирования рассматривал варианты:
деление по административным границам/странам. Вариант вычислительно емкий на этапе подготовки данных и очень условный, так как политическая ситуация и границы меняются. В каких-то регионах внутренние границы условны, как например в ЕС.
разбиение прямоугольной сеткой. Не особо хорошо работает из-за искажений проекции сферических координат на плоскость, хотя и самый вычислительно простой!
иерархическая система S2, некогда популярная благодаря компании разработчику. Использует квадратные ячейки и в задачах агрегации данных и роутинга хуже чем шестигранная ячейка.
иерархическая H3. И выбор был сделан в пользу последней, так как эта иерархическая система индексирования H3 имеет преимущества в сохранении расстояний (приблизительно одинаковый радиус сегмента в любой точке земного шара), отлично подходит для задач маршрутизации, библиотеки для работы с H3 хорошо поддерживаются и постоянно развиваются и доступны для PostgreSQL/JVM.
Дамп планеты официально публикуется OSM в форматах XML и PBF. XML формат слишком объемный и многословный для геоданных планеты, а PBF - бинарный формат на основе protobuf с секциями(обычно скомпрессированными) где в каждой отдельной секции сохраняется только один тип объекта: node, way, relation отсортированные в порядке их ID. К тому же строки хранятся в каждой секции в StringTable в порядке частоты использования и теги на них ссылаются по номеру. Все это позволяет OSM компактно хранить геометрию, метаданные и получить хорошую компрессию данных блока.
Даже если мы идеально секционируем все данные OpenStreetMap каким-либо из алгоритмов, то останутся еще особенности данных и формата, которые лучше учесть сразу. Первое - что линии хранятся ссылками на точки, а не реальными значениями координат, поэтому нужно собрать ways из объектов nodes на этапе подготовки скриптов и не загружать nodes в таблицу без тегов или такие nodes на которые не ссылаются объекты relations. Это изначально делал в своем коде на java, в позже отдал на откуп обработку исходного PBF утилите osmium add-locations-to-ways --keep-member-nodes --output-format pbf,pbf_compression=none. А второе, что самый большой объем данных приходится на объекты ways после сборки и хотелось бы узнать эвристику по данным. С чем нам придется работать чаще всего?
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)
Получается, что от общего количества линий в OpenStreetMap: 59% - это здания, 24% - дороги и тропы. Так что выделим признаки типа в отдельные колонки и если понадобится позже, то сможем вынести эти данные в субпартиции. Если для задач аналитики не нужны здания и дороги, то их импорт в базу можно отключить, задав параметры утилиты skip_buildings и skip_highwayсоответственно.
Парсинг формата PBF отдадим на откуп библиотеке org.openstreetmap.osmosis:osmosis-pbf2, но вот с секциями немного позже пошаманим для увеличения параллелизма парсинга и обработки данных. Например, planet-220704.osm.pbf дамп содержит 1255 секций. То, как работает osmosis-pbf2 меня не устраивает и написал свою реализацию доступа к секциям данных в программе(Splitter, PbfBlobOffsets). Это позволяет пропорционально числу секций распараллелить парсинг и обработку данных.
Эти эвристики помогли мне разбить ways, nodes, multipolygon каждый на сотню партиций...
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%) | Что же в openstreetmap_h3 "под капотом"
Если настолько глубоко эта тема не интересует, то смело пролистывайте статью до картинок!
Когда решил выбрать уровень 3 для индекса H3, то теоретический предел числа партиций будет равен 41162, а средняя длина ребра шестигранника 59.811 км. Опытным путем выяснил что на уровне 2 есть огромные партиции в Европе, а уровень 4 уже слишком детализированный и индексы не удастся закодировать как int2 в базе данных, а мы стремимся сохранить компактность объема данных и хорошую производительность решения.
Что же делать, если линия пересекает несколько ячеек сетки разбиения H3? В первоначальной версии ПО я выставлял значение партиции в null, теперь же использую "костыль" - значение 32767(так как некоторые колоночные БД хитрят с null значениями и не ведут отдельную битовую карту для них, а используют максимальное значение из типа shot int), и заполняем на этапе импорта в PostgreSQL поле типа массив h3_3_multi_regions, где перечислены все индексы которые входят в эту линию.
Все это замечательно, только PostgreSQL позволяет эффективно работать только с сотнями партиций на таблицу, а у нас их получилось десятки тысяч. Значит надо объединять диапазоны h3_3 - тут нам помогут диапазоны значений в партициях и укрупнение партиций PartitionSplitter.java. Будем пытаться собрать h3 уровня 3 партиции в более крупные уровня 2 и при превышении порога thresholdPercentFromMaxPartition в долях числа линий от самой крупной партиции, будем создавать новую партицию:
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;
}
Для целей агрегации и работы с пешеходными дистанциями я добавил к OSM данным значения H3 индекса уровня 8. Средняя площадь такой ячейки около 0.737км2 и это последний уровень который можно закодировать в int4. Но такие индексы будем считать только для линий длиной меньше 3км по алгоритму:
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
}
}
}Схема базы данных
Описание полей объектов идут в порядке выравнивания объектов фиксированной и переменной длинны, чтобы минимизировать объем данных на диске. В этих оптимизациях мне помогла утилита postgres_dba. Структуру таблиц старался сделать максимально близко к pgSnapshot схеме из 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);
После импорта список партиций для 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
Cписок партиций для 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')
Диаграммы самых крупных таблиц/партиций:
Посмотрим на семпл таблицы где хранятся линии, у которых больше чем один индекс H3_3
select linestring from ways_32767 TABLESAMPLE SYSTEM(10)
А теперь визуализируем полигоны ячеек входящих в ways_000:
Самая объемная по данным партиция 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
Мультиполигоны собраны в отдельной таблице select polygon from multipolygon TABLESAMPLE BERNOULLI(1)
Наиболее интересная "пасхалочка" моей утилиты - сетка разбиения в виде обычной таблицы: select * from h3_3_bounds_complex
Как пример, если нам нужно анализировать данные только в пределах МКАД в Москве, то можно смело добавлять h3_3=4522 к запросам (не забывая про линии принадлежащие к нескольким регионам ways.h3_3_multi_regions).
Итог
Теперь для аналитики по данным OpenStreetMap доступен новый высокопроизводительный инструмент openstreetmap_h3, который позволяет загружать весь мир в PostgreSQL/PostGIS, использует преимущества последних версий PostgreSQL для работы с декларативными партициями. При этом при разбиении данных учитывается их географическая близость на основе индексов H3. Это позволяет как обращаться ко всем данным сразу и параллелизовать выполнения запроса, так и уточнять в SQL геоиндекс области операции, а PostgreSQL обратится к данным только нужных партиций из сотни доступных в базе.