Найти жилье для покупки и аренды — непростая задача. Многие факторы становятся неожиданностью после оплаты и подписания договора, когда вы переезжаете на новое место. И сколько бы вы ни платили риелтору, это не гарантирует, что он будет защищать в первую очередь ваши интересы. Попробуем взять в руки пульт управления жизнью и самостоятельно проанализировать, где в Берлине и окрестностях жить вам точно не захочется.

Основная ценность этой публикации в новой визуализации. Теперь интерактивная карта доступна в браузере и работает на смартфонах!

В основе аналитики — моя субъективная модель, которая отговаривает меня жить в определенных местах. Главный её фильтр — расстояние по прямой от жилья на котором учитываются негативные факторы. Но, даже не смотря на это упрощение результаты работы модели даже на неполных исходных данных лучше, чем полное неведение об окрестностях жилья или только позитивная информация о районе. Набор факторов хоть и субъективный, но при возможности не жить рядом, думаю многие решат так же. Рядом с жильем, ближе чем в 150м не хотел бы видеть, нюхать и слышать влияющее на:

• качество воздуха: Станция перевалки мусора, Свалка, Террикон, Коровник, Свинарник, Жидкий навоз, Очистка сточных вод, Дымовая труба, Крематорий

• уровень шума: Стройка, Железнодорожные пути, Взлётно-посадочная полоса, Вертолётная площадка. Автомобильные дороги регионального значения, дороги с более чем 2мя полосами движения, Автомобильные дороги федерального значения, Стадион

• индустриальное настроение: Промышленные площади, Подстанции, Генератор, ТЭЦ, Карьер

• опасность: Контейнер, содержащий жидкости или сжатые газы, Магазин пиротехники, Опасность, Территория подвержена стихийным бедствиям, Район или дорога, подверженная затоплению

• комары: Заболоченые участки

Группировка данных по этим факторам основана на иерархической системе индексации Uber H3 с масштабом ячеек 10. Ячейки раскрашивались по следующим правилам:

• Если все жилые дома в ячейке подвержены воздействию отрицательных факторов, то ячейка окрашивается в красный цвет.

• Если жилые дома в ячейке не подвержены воздействию отрицательных факторов, то ячейка окрашивается в зеленый цвет.

• Если в ячейке присутствуют как здания без отрицательных факторов, так и постройки с влиянием негативных факторов, тогда ячейка будет окрашена в оранжевый цвет.

В случае если вас интересуют только результаты аналитики, вы можете зайти на GitHub Pages и посмотреть карту Берлина в своем веб‑браузере. Возможно прийдется подождать загрузки 80Мб GeoJSON - в первый раз это не быстрый процесс.

Использовал openstreetmap_h3 для обработки исходных данных OpenStreetMap:

wget https://download.geofabrik.de/europe/germany/brandenburg-latest.osm.pbf
docker run -it --rm -w $(pwd) -v $(pwd):/$(pwd) -v /var/run/docker.sock:/var/run/docker.sock openstreetmap_h3:latest -source_pbf $(pwd)/brandenburg-latest.osm.pbf -result_in_tsv true
docker run --name postgis15-brandenburg --memory=12g --memory-swap=12g --memory-swappiness 0 --shm-size=1g -v $(pwd)/database:/var/lib/postgresql/data -v $(pwd)/brandenburg-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 создает таблицы с геоданными OSM, секционированными по индексам H3 и может упростить запросы SQL с помощью geometry_global_view.

PSQL подключится к базе данных PostGIS только после успешного импорта данных OSM:

psql -h 127.0.0.1 -p 5432 -U postgres -d osmworld

Давайте создадим геоиндексы для ускорения SQL-запросов и после этого активируем расширение H3:

CREATE INDEX idx_nodes_geometry ON nodes USING gist (geom);
CREATE INDEX idx_ways_geometry ON ways USING gist (linestring);
CREATE INDEX idx_multipolygon_geometry ON multipolygon USING gist (polygon);

CREATE INDEX idx_nodes_geography ON nodes USING gist (cast (geom as geography));
CREATE INDEX idx_ways_geography ON ways USING gist (cast (linestring as geography));
CREATE INDEX idx_multipolygon_geography ON multipolygon USING gist (cast (polygon as geography));

CREATE EXTENSION h3_postgis CASCADE;

Данные для карты я подготовил и выгрузил с помощью следующих запросов:

CREATE TABLE distance AS
            select b.h3_3, h3_lat_lng_to_cell(b.centre,10) h3_10, h3_lat_lng_to_cell(b.centre,8) h3_8,
                    round(ST_Distance(b.geom::geography,g.geom::geography))::integer distance,
                    b.type from_type, g.type to_type, b.id from_id, g.id to_id,
                    CASE
                        WHEN g.tags->'amenity'='waste_transfer_station' or
                             g.tags->'landuse'='landfill' or
                             g.tags->'man_made'='spoil_heap' or
                             g.tags->'man_made'='wastewater_plant' or
                             g.tags->'building'='cowshed' or
                             g.tags->'building'='sty' or
                             g.tags->'building'='slurry_tank' or
                             g.tags->'man_made'='chimney' or
                             g.tags->'amenity'='crematorium' THEN 'air_quality'
                        WHEN g.tags->'power'='substation' or
                             g.tags->'power'='generator' or
                             g.tags->'power'='plant' or
                             g.tags->'building'='industrial' or
                             g.tags->'landuse'='industrial' or
                             g.tags->'landuse'='quarry' THEN 'industrial'
                        WHEN g.tags->'natural'='wetland' and g.tags->'wetland' is distinct from 'mangrove' THEN 'mosquitoes'
                        WHEN g.tags->'aeroway'='runway' or
                             g.tags->'aeroway'='helipad' or
                             g.tags->'railway'='rail' or
                             g.tags->'highway'='primary' or
                             g.tags->'highway'='trunk' or
                             g.tags->'leisure'='stadium' or
                             g.tags->'landuse'='construction' or
                             g.tags->'lanes'~'^\d+$' and (g.tags->'lanes')::integer>2 THEN 'noisy_place'
                        WHEN g.tags->'shop'='pyrotechnics' or
                             g.tags->'hazard' is not null or
                             g.tags->'hazard_prone' is not null or
                             g.tags->'flood_prone' is not null or
                             g.tags->'man_made'='storage_tank' THEN 'dangerous'
                    END reason

                        FROM geometry_global_view b
                        left join geometry_global_view g

                            on ST_DWithin(b.geom::geography,g.geom::geography,150)
    and g.tags->'disused' is distinct from 'yes'
    and (
        g.tags->'amenity'='waste_transfer_station' or --_air quality_
        g.tags->'landuse'='landfill' or --_air quality_
        g.tags->'man_made'='spoil_heap' or --_air quality_
        g.tags->'man_made'='wastewater_plant' or --_air quality_
        g.tags->'amenity'='crematorium' or --_air quality_
        (g.tags->'natural'='wetland' and g.tags->'wetland' is distinct from 'mangrove') or --_mosquitoes_
        (g.tags->'power'='substation' and g.tags->'substation' is distinct from 'minor_distribution') or --_industrial_
        (g.tags->'power'='generator' and g.tags->'generator:source' is distinct from 'wind' and g.tags->'generator:source' is distinct from 'solar' and g.tags->'generator:method' is distinct from 'photovoltaic' and g.tags->'generator:method' is distinct from 'run-of-the-river' and g.tags->'generator:method' is distinct from 'heat_pump' and g.tags->'generator:method' is distinct from 'thermal') or --_industrial_
        g.tags->'power'='plant' or --_industrial_
        g.tags->'building'='industrial' or --_industrial_
        g.tags->'landuse'='industrial' or --_industrial_
        g.tags->'landuse'='quarry' or --industrial_
        g.tags->'aeroway'='runway' or --_noisy_place_
        g.tags->'aeroway'='helipad' or --_noisy_place_
        g.tags->'railway'='rail' or --_noisy_place_
        g.tags->'highway'='primary' or --_noisy_place_
        g.tags->'highway'='trunk' or --_noisy_place_
        g.tags->'leisure'='stadium' or --_noisy_place_
        g.tags->'landuse'='construction' or --_noisy_place_
        g.tags->'shop'='pyrotechnics' or --_dangerous_
        g.tags->'man_made'='storage_tank' or --_dangerous_
        g.tags->'hazard' in ('landslide','erosion','rock_slide','falling_rocks','shooting_range','flooding','minefield','rockfall','ditch','flood','toxic plants','toxic fumes','quicksand','avalanche','fall','falling_ice','toxic_fumes','slide','Rock_slide','fire') or --_dangerous_
        g.tags->'hazard_prone' is not null or --_dangerous_
        g.tags->'flood_prone' is not null or --_dangerous_
        g.tags->'building'='cowshed' or --_air quality_
        g.tags->'building'='sty' or --_air quality_
        g.tags->'building'='slurry_tank' or --_air quality_
        g.tags->'man_made'='chimney' or --_air quality_
        (g.tags->'lanes' is not null and g.tags->'lanes'~'^\d+$' and (g.tags->'lanes')::integer>2) --noisy_place
    )
  where
    b.tags->'building' is not null and
    b.tags->'amenity' is null  and
    b.tags->'shop' is null and
    b.tags->'building' not in --the buildings listed below do not house people on a permanent basis
        ('service','garages','industrial','retail','office','roof','commercial','garage','kiosk','warehouse','church',
        'parking','public','shed','hangar','train_station','guardhouse','transportation','terrace','greenhouse','bridge',
        'government','chapel','gazebo','civic','ruins','supermarket','sports_centre','semidetached_house','toilets',
        'sports_hall','clinic','farm_auxiliary','stable','grandstand','bunker','gatehouse','store','temple','ventilation_kiosk',
        'carport','cowshed','barracks','shop','cabin','barn','cathedral','wall','townhouse','manufacture','shelter',
        'fire_station','stadium','stands','sport_hall','theatre','storage_tank','checkpoint','houseboat','abandoned','dovecote',
        'mosque','museum','military','container','observatory','lift','tent','factory','sport','mall','riding_hall','depot',
        'prison','gate','triumphal_arch','water_works','public_building','pavilion','bank','institute','works','collapsed',
        'car_repair','crossing_box','fuel','tree_house','presbytery','yesq','farm','outbuilding','police','porch','sauna',
        'monastery','cinema','tower','boathouse','library','transformer_tower','heat_exchange_station','ice_rink')
    and (ST_MinimumBoundingRadius(b.geom)).radius > 0.00007; --filter small non living buildings

create table h3_10_stat_colored as 
select h3,h3_cell_to_boundary_geography(h3) cell ,count, reason,
case
when ARRAY_POSITION(all_reason,NULL::text) is null and cardinality(reason)>0 then '#ff0000'
when ARRAY_POSITION(all_reason,NULL::text)>0 and cardinality(reason)>0 then '#ff9700' else '#00ff00'
end colour
from (
       select h3_10 h3, count(distinct (to_id,to_type)),
              array_agg(distinct reason) filter (where reason is not null ) reason, 
              array_agg(distinct reason) all_reason 
       from distance
group by 1 order by 1) geo;

\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, 'reason', reason,'fill',colour,'fill-opacity',0.5)))) FROM h3_10_stat_colored ) to 'berlin-h3-10_colored.json.geojson';

Разделил загружаемый файл размером 80 Мб на 4 части (как обходной путь ограничения размера файла GitHub), загрузил эти данные в репозиторий github и использовал виджет MapLibre для визуализации этих данных:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/html">
<head>
    <title>Map of negative factors in Berlin that affect housing comfort</title>
    <script src='https://unpkg.com/maplibre-gl@latest/dist/maplibre-gl.js'></script>
    <link href='https://unpkg.com/maplibre-gl@latest/dist/maplibre-gl.css' rel='stylesheet'/>
    <style>
        #map-container {
                position: absolute;
                top: 0;
                left: 0;
                width: 100%;
                height: 100%;
            }
        #map {
                width: 100%;
                height: 100%;
             }
    </style>
</head>
<body>
<div id="map-container">
    <div id="map"></div>
</div>
<script>
    function displayInfo(selectedFeature) {
        if(selectedFeature.properties.fill=='#00ff00') return;
        var popup = new maplibregl.Popup()
        .setLngLat(selectedFeature.geometry.coordinates[0][0])
        .setHTML('<h3>count ' + selectedFeature.properties.count +
                    '<br/>reason '+ selectedFeature.properties.reason+'</h3>')
        .addTo(map);
    }
    
    const style = {
      "version": 8,
        "sources": {
        "osm": {
                "type": "raster",
                "tiles": ["https://a.tile.openstreetmap.org/{z}/{x}/{y}.png"],
                "tileSize": 256,
          "attribution": "&copy; OpenStreetMap Contributors",
          "maxzoom": 19
        }
      },
      "layers": [
        {
          "id": "osm",
          "type": "raster",
          "source": "osm"
        }
      ]
    };

    var map = new maplibregl.Map({
      container: 'map',
      style: style,
      center: [13.4092, 52.5151],
      zoom: 12
    });

    map.on('load', function () {
        
        for (var idx = 0; idx <= 3; idx++) {

            map.addSource('geojson-'+idx, {
                type: 'geojson',
                data: 'https://raw.githubusercontent.com/igor-suhorukov/igor-suhorukov/main/berlin-h3-10_colored-'+idx+'.json'
            });

            map.addLayer({
                id: 'geojson-'+idx+'-layer',
                type: 'fill',
                source: 'geojson-'+idx,
                paint: {
                    'fill-color': ['get', 'fill'],
                    'fill-opacity': 0.5
                }
            });

            map.on('click', 'geojson-'+idx+'-layer', function (e) {
                var selectedFeature = e.features[0];
                displayInfo(selectedFeature);
            });

            map.on('mouseenter', 'geojson-'+idx+'-layer', function () {
                map.getCanvas().style.cursor = 'pointer';
            });

            map.on('mouseleave', 'geojson-'+idx+'-layer', function () {
                map.getCanvas().style.cursor = '';
            });
        }
    });
</script>
</body>
</html>

Окончательная визуализация доступна в виде статического веб‑сайта на GitHub Pages по адресу: https://igor‑suhorukov.github.io.

Напомню, что ячейки раскрашивались по следующим правилам:

• Если все жилые дома в ячейке подвержены воздействию отрицательных факторов, то ячейка окрашивается в красный цвет.

• Если жилые дома в ячейке не подвержены воздействию отрицательных факторов, то ячейка окрашивается в зеленый цвет.

• Если в ячейке присутствуют как здания без отрицательных факторов, так и постройки с влиянием негативных факторов, тогда ячейка окрашена в оранжевый цвет.

Можно щелкнуть на ячейку и посмотреть группы факторов, влияющие на жилые дома внутри выбранной области.

Эта модель и подход просты на первый взгляд, но за ними стоят многие месяцы работы как над openstreetmap_h3, так и недели отладки SQL здесь, с сообществом на Хабре. Спасибо всем кто помог!

В то же время результат этой работы будет полезен при выборе безопасного и комфортного жилья в Берлине. Остерегайтесь "красных" зон, изучайте факторы которые влияют на комфорт жилья!