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 install
Building 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.pbf
The 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.pbf
Create 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 ms
The 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
