Today I will discover America to you based on OpenStreetMap data in PostgreSQL15/PostGIS and my project openstreetmap_h3. Let's run the query and compare its execution time on the Citus column store in PostgreSQL and on the standard 100GB database partitioned by H3 geoindex.
We will find the top15 buildable locations in North America and the total length of roads, as well as their type and surface. I will not overload the publication with program logs, let's focus on the data! You can easily repeat all requests yourself on your laptop/computer.
Statistics
We live in an amazing world where the modern laptop is running the maker of many supercomputers 20 years ago and the available storage drives allow you to store terabytes of data with fast access. In addition, every year more and more open data becomes available geographical, economic. New specialized databases and Big Data movements are emerging. New tools that make it easy to get answers to questions.
As Mark Twain said, “there are three types of lies: Lies, damned lies, and statistics” We will not believe the available statistics, but will recalculate everything on our own based on open data.
North American geodata loading into PostgreSQL15
We build openstreetmap-h3, for this we need maven, git, JDK11+
git clone https://github.com/igor-suhorukov/openstreetmap_h3.git
cd openstreetmap_h3
git checkout dev/postgresql15
mvn installBuilding a docker image with PostgreSQL 15.1, PostGIS 3.3.2, H3 4.1.1:
cd postgis_docker-master
docker build -t postgres15_postgis .Download America osm.PBF data from the Geofabrik website:
wget https://download.geofabrik.de/north-america-latest.osm.pbfThe volume of initial OpenStreetMap data is about 13.0 GB, which we turn from north-america-latest.osm.pbf into TSV format data and a set of scripts for loading into PostgreSQL:
java -jar target/osm-to-pgsnapshot-schema-ng-1.0-SNAPSHOT.jar -columnar_storage -source_pbf ~/dev/map/north-america/north-america-latest.osm.pbfCreate a new docker container and load data into it:
docker run --name postgis15-north-america-citus --memory=12g --memory-swap=12g --memory-swappiness 0 --shm-size=1g -v /home/acc/dev/map/database/north-america:/var/lib/postgresql/data -v /home/acc/dev/map/north-america/north-america-latest_loc_ways:/input -e POSTGRES_PASSWORD=osmworld -d -p 5432:5432 postgres15_postgis:latest -c checkpoint_timeout='15 min' -c checkpoint_completion_target=0.9 -c shared_buffers='4096 MB' -c wal_buffers=-1 -c bgwriter_delay=200ms -c bgwriter_lru_maxpages=100 -c bgwriter_lru_multiplier=2.0 -c bgwriter_flush_after=0 -c max_wal_size='32768 MB' -c min_wal_size='16384 MB'North America geo database
Connect to the database and check the component versions:
psql -h 172.17.0.1 -p 5432 -U postgres -d osmworld
osmworld=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
osmworld=# \dx
List of installed extensions
Name | Version | Schema | Description
----------------+---------+------------+------------------------------------------------------------
citus | 11.1-1 | pg_catalog | Citus distributed database
citus_columnar | 11.1-1 | pg_catalog | Citus Columnar extension
h3 | 4.1.1 | public | H3 bindings for PostgreSQL
hstore | 1.8 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.3.2 | public | PostGIS geometry and geography spatial types and functions
(6 rows)
osmworld=# :dba
Time: 1,738 ms
Time: 0,212 ms
Time: 0,122 ms
Time: 0,247 ms
Menu:
0 – Node & current DB information: master/replica, lag, DB size, tmp files, etc.
1 – Databases: size, stats
q – Quit
Type your choice and press <Enter>:
0
Time: 0,872 ms
metric | value
-----------------------------------+-----------------------------------------------------------------------------------------------------------------------------
Postgres Version | PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
Config file | /var/lib/postgresql/data/postgresql.conf
Role | Master
Replicas |
Started At | 2023-02-15 09:37:17+00
Uptime | 00:01:06
Checkpoints | 9
Forced Checkpoints | 88.9%
Checkpoint MB/sec | 0.020285
--------------------------------- | ----------------------------------------------------------------------------------------
Database Name | osmworld
Database Size | 100 GB
Stats Since ccc |
Stats Age |
Installed Extensions | citus 11.1-1, citus_columnar 11.1-1, h3 4.1.1, hstore 1.8, plpgsql 1.0 +
| postgis 3.3.2
Cache Effectiveness | 41.82%
Successful Commits | 100.00%
Conflicts | 0
Temp Files: total size | 3771 MB
Temp Files: total number of files | 98
Temp Files: avg file size | 38 MB
Deadlocks | 0
(22 rows)
Time: 21,103 msThe tables on which we will run queries in the citus_columnar column store are stored in the PostgreSQL database, which consume 100 GB of storage space.
Building density
Now we can count how many buildings in the "ways" table in each of the hexagons of the H3 partition grid at level 3 and select only 15 of them in decreasing order of the number of buildings:
osmworld=# select h3_3, count(*) from ways where building group by 1 order by 2 desc limit 15;
h3_3 | count
-------+---------
10768 | 2608290
10657 | 2031406
10920 | 1620451
10660 | 1396225
10772 | 1224476
17601 | 1197694
11163 | 1168809
10803 | 1108671
9928 | 1091346
10800 | 1089511
10288 | 1039033
10656 | 980499
10661 | 973984
10480 | 928448
9828 | 909524
(15 rows)
Time: 3974,699 ms (00:03,975)We visualize these regions using a query in QGIS:
select buildings.*,h3b.bounds,row_number() OVER ( order by count desc) as rownum
from h3_3_bounds_complex h3b
inner join
(select h3_3, count(*) from ways where building group by 1 order by 2 desc limit 15) buildings
on h3b.id=buildings.h3_3
For comparison, the same query on the classic (heap) PostgreSQL storage:
docker start postgis15-north-america
osmworld=# select h3_3, count(*) from ways where building group by 1 order by 2 desc limit 15;
h3_3 | count
-------+---------
10768 | 2608290
10657 | 2031406
10920 | 1620451
10660 | 1396225
10772 | 1224476
17601 | 1197694
11163 | 1168809
10803 | 1108671
9928 | 1091346
10800 | 1089511
10288 | 1039033
10656 | 980499
10661 | 973984
10480 | 928448
9828 | 909524
(15 rows)
Time: 42153,407 ms (00:42,153)This query took 10.6 times longer to complete than on a Citus column (42,153sec heap VS 3,975sec column).
Roads of North America
Total length of roads and trails:
osmworld=# select sum(ST_Length(linestring::geography))
osmworld-# from ways
osmworld-# where tags->'highway' is not null
osmworld-# and tags->'highway' not in ('platform','bus_stop','corridor','rest_area');
sum
--------------------
17333069670.373722
(1 row)
Time: 236019,990 ms (03:56,020)The length of only roads with defined coating type:
osmworld=# select sum(ST_Length(linestring::geography))
osmworld-# from ways
osmworld-# where tags->'highway' is not null
osmworld-# and tags->'highway' not in ('platform','bus_stop','corridor','rest_area')
osmworld-# and tags->'surface' is not null;
sum
-------------------
4683910752.668413
(1 row)
Time: 84941,031 ms (01:24,941)Let's evaluate the coverage data of surface type:
osmworld=# select count(tags->'surface')*100.0/count(*) form from ways
where tags->'highway' is not null
and tags->'highway' not in ('platform','bus_stop','corridor','rest_area');
form
---------------------
17.8903148327659968
(1 row)
Time: 15601,571 ms (00:15,602)Now let's summarize the coating characteristics:
osmworld=# select
tags->'surface' "surface_type",
sum(ST_Length(linestring::geography))
from ways
where tags->'highway' is not null
and tags->'highway' not in ('platform','bus_stop','corridor','rest_area')
and tags->'surface' is not null
group by 1
having count(*) >=100
order by 2 desc;
surface_type | sum
-----------------------+--------------------
unpaved | 1652815720.1065524
asphalt | 1364860501.5095317
gravel | 508458110.63075835
paved | 417214264.78016436
dirt | 298434798.3305051
ground | 186407920.22584632
concrete | 163305537.72818062
compacted | 29695112.501213416
sand | 10358279.6686242
grass | 8177577.5513973655
concrete:plates | 7839326.0808026455
fine_gravel | 7070308.215675858
paving_stones | 4528857.6939902995
earth | 4047844.559239158
ice | 3251766.8227435704
pebblestone | 3044145.111581855
wood | 1548257.2810464564
cobblestone | 1537007.58652311
limerock | 1154768.9652535582
dirt/sand | 1122131.6047778637
trail | 957280.1821749548
crushed_limestone | 693230.4216405117
concrete:lanes | 550729.2561993373
soil | 486167.0521078333
mud | 481869.20646625035
brick | 405033.45128156105
bricks | 375971.1124591282
chipseal | 363933.36915447767
sett | 248202.94349638248
natural | 235102.6922085221
woodchips | 160545.90482571782
rock | 146208.27722591715
unhewn_cobblestone | 125749.92425415723
metal | 88785.57955673145
spur | 54000.13141806118
cobblestone:flattened | 51647.857174154095
bark | 32652.962135521146
mulch | 27545.921618327953
boardwalk | 27291.09042737315
cement | 26397.24288501879
stone | 25544.130645372174
grass_paver | 22737.854763573207
block | 13035.364332975803
metal_grid | 8508.7689744505
2 | 8200.875450534988
stepping_stones | 1905.2749432306568
(46 rows)Let's see the length by road type:
osmworld=# select tags->'highway',sum(ST_Length(linestring::geography))
from ways where tags->'highway' is not null
and tags->'highway' not in ('platform','bus_stop','corridor','rest_area')
group by 1 having count(*)>=100 order by 2 desc;
?column? | sum
----------------+--------------------
residential | 5645591764.076551
service | 2971654450.570504
track | 2457547483.583801
unclassified | 1993885988.1285813
tertiary | 1214043395.8814895
secondary | 723958953.0845761
footway | 632639912.4949341
path | 480816819.3575947
primary | 469162054.7166547
motorway | 245362469.24192086
trunk | 239323368.6696571
cycleway | 100250842.26426062
motorway_link | 70930030.73010331
proposed | 13177426.45433327
construction | 12253141.298817866
trunk_link | 10567863.175455065
bridleway | 9684141.014057217
living_street | 8846280.895166134
primary_link | 7444718.182813775
pedestrian | 6924058.676602626
secondary_link | 5057131.034404312
road | 4315063.417226655
raceway | 3479146.9071076848
tertiary_link | 2315092.220026257
steps | 1590123.0661222872
services | 803185.248279694
abandoned | 679751.4909001294
busway | 234694.90633171005
bus_guideway | 130270.53462602737
planned | 129935.32424801859
razed | 101886.62926516314
escape | 75061.51276658909
disused | 29283.81533118899
elevator | 8043.809559098002
(34 rows)
Time: 252662,933 ms (04:12,663)And add the surface type to the road type:
osmworld=# select tags->'highway' "road_type",
tags->'surface' "surface_type",
sum(ST_Length(linestring::geography))
from ways
where tags->'highway' is not null
and tags->'highway' not in ('platform','bus_stop','corridor','rest_area')
group by 1,2
having count(*)>=100
order by 3 desc;
road_type | surface_type | sum
----------------+--------------------+--------------------
residential | | 4763401026.858094
service | | 2646528071.9827824
track | | 1884625080.1819654
unclassified | | 803621442.5929096
tertiary | | 710555856.4372112
unclassified | unpaved | 705905327.6811218
footway | | 471736761.945697
secondary | | 421890532.64897823
track | unpaved | 343763333.596261
residential | asphalt | 333411585.3559667
residential | unpaved | 299735764.42184454
path | | 289098741.5865223
primary | | 244156319.24732155
unclassified | gravel | 243598138.31110144
tertiary | asphalt | 240733173.70222318
secondary | asphalt | 215401222.95500344
primary | asphalt | 170428859.68408427
service | unpaved | 146554062.94818687
motorway | | 137914050.71618488
trunk | | 129236710.00592434
track | dirt | 125562066.74714226
residential | gravel | 110258472.83744836
tertiary | unpaved | 100908889.53088643
service | asphalt | 93346831.40959103
trunk | asphalt | 89758448.38001442
residential | paved | 87999243.86922555
tertiary | paved | 87994213.52129921
path | ground | 82002271.23108393
motorway | asphalt | 81019470.89036791
footway | concrete | 79210367.51960273
unclassified | dirt | 67817273.36151944
unclassified | asphalt | 61762007.44361189
unclassified | paved | 57112015.35820832
tertiary | gravel | 51756606.24016952
secondary | paved | 49833719.879313916
motorway_link | | 46416693.82958645
path | dirt | 45621263.32209663
track | ground | 42852401.54191421
primary | paved | 42449956.38505393
track | gravel | 38496421.88388152
unclassified | ground | 36441972.3942225
cycleway | | 34811115.38976194
service | gravel | 32493147.550596934
cycleway | asphalt | 27113351.132957958
path | unpaved | 24397178.109620415
service | paved | 22499920.406084005
footway | paved | 21128165.24793853
residential | dirt | 18140805.01287318
motorway_link | asphalt | 17697463.3352245
residential | concrete | 17180996.330276847
motorway | concrete | 15797143.405233588
secondary | unpaved | 14951004.396679036
trunk | paved | 14544248.496826058
footway | asphalt | 14139380.466019733
service | dirt | 13533635.975871304
footway | dirt | 12475554.942645315
proposed | | 12412404.479500988
secondary | gravel | 12359859.326103551
footway | ground | 12179598.43185987
cycleway | paved | 11692894.243439678
construction | | 10434301.517878689
unclassified | compacted | 10083340.114492217
motorway | paved | 9739514.320030514
path | asphalt | 9414713.962064302
tertiary | dirt | 9052189.009797093
service | concrete | 8954778.624278365
secondary | concrete | 7238263.248977672
path | gravel | 7127529.505746644
footway | unpaved | 7121128.04537417
trunk_link | | 7100005.578315217
living_street | | 6957202.813900431
cycleway | concrete | 6418397.632759386
path | paved | 6364598.297245062
residential | compacted | 5912938.710068744
track | sand | 5608795.294502438
trunk | concrete | 5273502.003184203
tertiary | concrete | 5029174.945935459
primary_link | | 4850982.357941081
path | concrete | 4845785.159585622
primary | concrete | 4769072.452942328
pedestrian | | 4727580.457758072
bridleway | | 4683993.889887575
cycleway | unpaved | 4592254.488169263
cycleway | gravel | 4531796.28164687
motorway_link | concrete | 4446552.844258176
track | compacted | 3882946.5630481234
tertiary | compacted | 3768274.047507852
primary | gravel | 3699044.781700628
track | grass | 3593391.0202003038
footway | concrete:plates | 3591011.286780505
cycleway | dirt | 3545659.5238580364
service | ground | 3437765.4926561713
secondary_link | | 3414984.794106794
footway | gravel | 3383808.3633199693
road | | 3356383.4854103657
residential | ground | 2956457.0084883217
primary | unpaved | 2847185.76046211
trunk_link | asphalt | 2775679.113175762
cycleway | fine_gravel | 2446477.540956408
raceway | | 2411263.7490123133
track | pebblestone | 2301870.9579834472
tertiary | ground | 2260195.753934444
motorway_link | paved | 2144888.2806652286
primary_link | asphalt | 2134589.9168418245
path | earth | 2040922.4883732798
cycleway | ground | 1970167.8353412757
bridleway | ground | 1967466.2354609983
path | grass | 1888070.7409965922
path | compacted | 1857097.471348198
residential | paving_stones | 1814454.1311404312
footway | grass | 1731987.8252963582
unclassified | sand | 1691170.1008224054
track | paved | 1632302.4668008054
cycleway | compacted | 1616176.9569416267
tertiary_link | | 1612537.7967704032
bridleway | dirt | 1609471.513948336
service | compacted | 1607442.4257461077
footway | paving_stones | 1415560.3667816524
path | sand | 1350888.8938942542
secondary_link | asphalt | 1329023.0169268274
path | fine_gravel | 1281473.8216985897
unclassified | concrete | 1221896.2071398764
steps | | 1184717.256174309
track | limerock | 1129378.424354146
track | asphalt | 1075047.282542879
residential | cobblestone | 1059485.2912852596
pedestrian | concrete | 1032715.6747134975
track | earth | 887940.2426770492
motorway | concrete:plates | 887772.985242092
path | trail | 883190.683327884
unclassified | fine_gravel | 861872.3240972031
living_street | asphalt | 861745.8339908258
services | | 798963.5122002938
residential | concrete:plates | 781482.2986809855
secondary | concrete:plates | 750952.7214043038
construction | asphalt | 726776.6693312767
cycleway | crushed_limestone | 685798.6089688728
residential | fine_gravel | 679792.8561554619
footway | fine_gravel | 673063.1444234059
abandoned | | 660868.6176068272
footway | wood | 613236.7794218957
footway | sand | 576903.3580167836
construction | paved | 570144.5593899674
path | wood | 566661.8641079792
bridleway | unpaved | 545455.7255129513
footway | compacted | 533918.300248029
service | fine_gravel | 524744.6615275827
secondary | ice | 522710.2378628283
tertiary_link | asphalt | 520411.8219238426
service | concrete:plates | 478497.79336747277
residential | sand | 449229.43782958493
living_street | unpaved | 419139.16090518294
primary | concrete:plates | 412410.0601491582
proposed | asphalt | 409837.9017696265
track | dirt/sand | 408159.62457694276
footway | earth | 398114.2568012427
track | fine_gravel | 396033.7962820694
raceway | dirt | 384412.85082210996
footway | concrete:lanes | 384388.78014350444
road | dirt | 375608.21000385896
bridleway | gravel | 351706.6890854066
raceway | asphalt | 338814.6551858863
pedestrian | paving_stones | 330745.75517430564
service | grass | 330241.696258488
road | unpaved | 326967.0915144114
trunk_link | paved | 326698.20477597584
service | sand | 325567.8481155458
trunk_link | concrete | 321293.93015355075
pedestrian | asphalt | 306435.4369459363
tertiary | concrete:plates | 304447.7109953402
pedestrian | paved | 291670.5994772637
track | concrete | 288702.54363131255
unclassified | dirt/sand | 282958.4598390193
track | soil | 280367.2842426119
service | paving_stones | 253113.35890887617
construction | concrete | 250489.66635970082
residential | earth | 243368.67611399104
residential | bricks | 241547.18157778349
primary_link | paved | 233362.83494628928
track | mud | 222623.162350262
path | natural | 222525.11418556486
tertiary | paving_stones | 221661.8326535929
motorway_link | concrete:plates | 220084.27158910522
living_street | concrete | 211784.926229045
unclassified | grass | 207202.7872336678
steps | concrete | 204075.57897368207
primary_link | concrete | 192420.4034805249
unclassified | pebblestone | 185529.188543652
unclassified | earth | 185284.6129128161
path | dirt/sand | 178323.15887339736
cycleway | wood | 177585.0612189861
living_street | paved | 176779.42574736488
residential | brick | 175813.97652501083
secondary_link | concrete | 170566.33634487086
unclassified | cobblestone | 168035.4812049392
bridleway | grass | 166927.06816627685
residential | grass | 145007.91263839617
tertiary | cobblestone | 144531.42837198608
busway | | 141668.00141975118
proposed | concrete | 135256.43971835688
bridleway | compacted | 134367.6730410877
residential | sett | 134159.89625658424
raceway | paved | 123668.95187402441
planned | | 121862.77791955674
secondary_link | paved | 121253.34000382005
service | pebblestone | 114028.31557654691
service | earth | 112700.86070646881
path | mud | 110942.38114480482
footway | brick | 110034.66372621732
construction | unpaved | 105318.89300274091
residential | chipseal | 104757.43908833718
unclassified | paving_stones | 104080.85461469444
razed | | 98493.19564664886
footway | woodchips | 97849.09078415699
raceway | unpaved | 95637.53003178755
bridleway | sand | 91964.97424098915
trunk | concrete:plates | 89707.81293471971
unclassified | chipseal | 87646.31337049662
living_street | paving_stones | 86956.00904801363
path | pebblestone | 86065.84995453423
proposed | paved | 84174.64638228346
footway | pebblestone | 84094.7337710366
cycleway | grass | 80871.43961649334
bus_guideway | | 80059.3065879965
residential | pebblestone | 78676.32827696331
cycleway | concrete:plates | 78626.97730968334
path | paving_stones | 77547.23236472643
secondary | paving_stones | 76915.90335693996
residential | soil | 74775.50249977992
cycleway | paving_stones | 74162.84040374875
path | concrete:plates | 73522.71543854626
path | rock | 72564.14805588275
service | concrete:lanes | 71742.76509436533
footway | bricks | 70658.46439321125
bridleway | earth | 68365.0746144508
busway | asphalt | 67189.06572572011
tertiary_link | paved | 65320.49597644599
unclassified | concrete:plates | 65246.51136772131
residential | unhewn_cobblestone | 62209.080716995595
service | soil | 57900.53748149729
service | brick | 57445.63695545244
service | dirt/sand | 55324.03395016277
footway | dirt/sand | 54507.746362286554
pedestrian | wood | 54095.63523217174
path | woodchips | 53331.943702748395
path | spur | 52892.086996842205
steps | paved | 52506.80292003064
steps | wood | 46461.755843003
residential | dirt/sand | 44724.94194470337
tertiary_link | concrete | 43962.57398137904
living_street | gravel | 40862.191687342194
tertiary_link | unpaved | 40323.036217485154
footway | cobblestone | 39606.18577173825
primary | paving_stones | 39191.029812534376
pedestrian | unpaved | 38294.86108637042
escape | | 38059.76110339022
trunk_link | concrete:plates | 37474.92274911355
bus_guideway | asphalt | 34199.652828803846
service | cobblestone | 33720.36809304341
pedestrian | gravel | 30344.666352008073
footway | metal | 26149.36463939565
footway | unhewn_cobblestone | 26105.202815754776
residential | concrete:lanes | 25981.44764468979
primary_link | concrete:plates | 25205.712215486255
track | wood | 25177.403482617865
tertiary | bricks | 24087.396407763354
path | bark | 23419.850321512553
footway | sett | 20372.797974850975
service | sett | 19287.991708956688
footway | boardwalk | 18912.766098181168
service | wood | 18522.987330308424
busway | concrete | 18334.605504341
tertiary_link | gravel | 16821.764628381225
steps | paving_stones | 16168.314285169055
secondary_link | concrete:plates | 14340.732436440501
steps | dirt | 13881.373525668309
steps | ground | 13678.596643850293
residential | wood | 13554.642019149022
unclassified | wood | 13290.616084666595
pedestrian | sett | 12923.641279906136
pedestrian | cobblestone | 12706.879040066673
pedestrian | concrete:plates | 12697.754248647872
tertiary | wood | 11502.550585919225
pedestrian | brick | 11333.743133012355
living_street | cobblestone | 10926.338677026815
steps | unpaved | 10707.201476784405
path | metal | 10678.05085322325
steps | asphalt | 9955.642433659
steps | metal | 9485.310731598558
footway | stone | 8536.746047905674
service | bricks | 8414.061977404393
elevator | | 8043.809559098003
steps | gravel | 7077.323945817867
pedestrian | metal | 5018.339976670147
cycleway | metal | 4132.195446674829
steps | stone | 3131.918694257478
steps | rock | 2040.101130895399
steps | concrete:plates | 1700.1674751379323
(298 rows)Openstreetmap H3 and why PostgreSQL
In this post, I tried to focus on data processing. If you are interested in the topic of data preparation, loading them into PostgreSQL and you want to learn more about my openstreetmap_h3 project, geodata partitioning - I recommend reading "How to put the whole world in a regular laptop: PostgreSQL and OpenStreetMap" and articles on links from the section "See also".
PostgreSQL/PostGIS is superior to other available open source solutions in terms of stability and geodata functionality. I have been loading data from OSM into HeavyDB, Spark. And the question is not only about database performance, but also about the supported functions and maturity of the ecosystem, available visualization programs, IDEs, extension modules, drivers for your programming language. As an example, the Citus extension for PostgreSQL made it possible to speed up the building count query by 10 times without any changes to the data schema and query.
Someone prefers to use Clickhouse, Elasticsearch and you can choose any tool that is convenient for you to process your data. The Openstreetmap H3 utility can save data in the Apache Arrow format - universal for Big Data processing tools. The mode is activated by the -result_in_arrow parameter, so there is a freedom to choose how to process OSM data.
Summary
The maximum building density in North America according to OpenStreetMap is 1 - New York, 2 - Los Angeles and 3 - WashingtonDC, it turns out that most of the top15 locations are near the ocean.
The total length of roads is 17,333,069 km, while only 17.89% of the total number of roads in America in OSM have information about the surface type. In first place in terms of total length "residential" 5,645,591 km (32.57% of the total length), in second place are roads for "service" 2,971,654 km, in third place "track" 2,457,547 km
