Я бы не жил в Сочи в этих местах…

docker run -it --rm -w $(pwd) -v $(pwd):/$(pwd) -v /var/run/docker.sock:/var/run/docker.sock openstreetmap_h3:latest -source_pbf $(pwd)/south-fed-district-latest.osm.pbf -result_in_tsv true
docker run --name postgis15-south-fed-district --memory=12g --memory-swap=12g --memory-swappiness 0 --shm-size=1g -v $(pwd)/database:/var/lib/postgresql/data -v $(pwd)/south-fed-district-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'

openstreetmap_h3 нужен для преобразования дампов OpenStreetMap. Позволяет параллельно загружать их в PostGIS, используя партиционирование данных, либо экспортировать в набор parquet/arrow файлов для Spark/Hadoop. Про этот инструмент подробно рассказывал в серии публикаций на Хабре. Теперь его можно использовать в Docker!

select geom from geometry_global_view where 
tags->'addr:region'='Краснодарский край' and 
tags->'admin_level'='6' and 
(tags->'name'='городской округ Сочи' or tags->'name'='городской округ Сириус')

Эти полигоны с помощью функции h3_polygon_to_cells(geom,8) можно превратить в геоиндексы. С ними будет быстрее и удобнее работать в запросах к геоданным...

create table distance_sochi as 
  select * from distance 
  where h3_8 in (select h3_polygon_to_cells(geom,8) 
                   from geometry_global_view 
                   where tags->'addr:region'='Краснодарский край' and tags->'admin_level'='6' and (tags->'name'='городской округ Сочи' or tags->'name'='городской округ Сириус'));

create table h3_10_stat_sochi as 
  select h3,h3_cell_to_boundary_geography(h3) cell ,count, reason 
  from (
          select h3_10 h3, count(distinct (to_id,to_type)),array_agg(distinct reason) reason 
            from distance_sochi 
            where to_id is not null
            group by 1 
            order by 1
       ) geo;

select id,type,tags from geometry_global_view 
  where (tags->'hazard_prone' is not null or 
         tags->'flood_prone' is not null or 
         tags->'hazard' is not null)  
      and h3_8 in ( select to_short_h3_8(h3_polygon_to_cells(geom,8)::bigint) 
                     from geometry_global_view 
                      where  tags->'addr:region'='Краснодарский край' and 
                             tags->'admin_level'='6' and  
                                (tags->'name'='городской округ Сочи' or 
                                 tags->'name'='городской округ Сириус') 
                  )

to_short_h3_8 и приведение h3index к типу bigint нужно из-за компактного представления индексов h3_8 масштаба 8 в моей базе данных openstreetmap_h3

     id     | type  |                        tags                         
------------+-------+-----------------------------------------------------
  706309487 | nodes | "ford"=>"yes", "flood_prone"=>"yes"
 3682998930 | nodes | "ford"=>"yes", "depth"=>"0.8", "flood_prone"=>"yes"
 3682998934 | nodes | "ford"=>"yes", "depth"=>"0.8", "flood_prone"=>"yes"
 8616000015 | nodes | "hazard"=>"landslide"
(4 rows)

Time: 2797,106 ms (00:02,797)

osmworld=# copy (select distinct h3_10, reason, 
                              CASE
                                  WHEN to_type='nodes' THEN 'https://www.openstreetmap.org/node/'||to_id 
                                  WHEN to_type='ways' THEN 'https://www.openstreetmap.org/way/'||to_id 
                                  WHEN to_type='multipolygon' THEN 'https://www.openstreetmap.org/relation/'||to_id 
                              END OSM_URL 
                    from distance_sochi where to_id is not null order by 1) to 'uncomfortable_sochi.tsv'
COPY 27609
Time: 80,586 ms
osmworld=# copy (select json_build_object('type', 'FeatureCollection',
                                           'features', json_agg(json_build_object('type', 'Feature',
                                                                                  'geometry', st_AsGeoJSON(cell)::json, 
                                                                                  'properties', json_build_object('count', count,'h3_10',h3, 'reason', reason)))) 
                  FROM h3_10_stat_sochi) to 'uncomfortable_h3-10_region_sochi.json.geojson';
COPY 1
Time: 52,928 ms

osmworld=# copy ( select json_build_object('type', 'FeatureCollection','features', 
                                            json_agg(json_build_object('type', 'Feature','geometry', st_AsGeoJSON(cell)::json, 
                                                                       'properties', json_build_object('count', count,'h3_10',h3)))) 
                    FROM (
                           select h3_cell_to_boundary_geography(h3) cell,* 
                           from (
                                  select h3_10 h3,count(*) 
                                  from distance_sochi inner join geometry_global_view 
                                  on to_id is null and from_id=id and from_type=type group by 1
                                ) good
                         ) good_h3_10) to 'maybecomfortable_h3-10_region_sochi.json.geojson';
COPY 1
Time: 447,092 ms