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

Roads and building density in North America. 100GB geodata processing OSM data in PostgreSQL