Приятно когда живешь там где у тебя вход в метро в 15 минутах ходьбы и с комфортом жизни неплохо. Но кушать хочется всегда, а на рабочей неделе уж совсем нет времени и желания ехать в супермаркет чтобы сварить гречневую кашу и взять что-нибудь на завтрак. Магазины шаговой доступности есть везде, но мне хотелось бы чтобы это было что-нибудь более привычное, типа Пятёрочки, Дикси, Магнита, Перекрёстка, Магнолии, Атак или Ленты.

Жизнь по этому фактору в Серебряном бору( это один из победителей в категории удаленности от неблагоприятных факторов) отпадает сразу - там и до метро не близко! Найдем более удобные места для ежедневной жизни и похода в продуктовый магазин.

Это как в дилеме что первее: курица или яйцо, так же сетевой продуктовый/жилой район. Компании владельцы торговых сетей тратят деньги на геоанализ где им лучше расположить продуктовый магазин. Так что в каком-то виде мы переиспользуем их аналитику и найдем где живут мои соседи. Если же нужна другая группа соседей, то нужно поискать окрестности Азбуки Вкуса и бывшего Елисеевского.

Для анализа буду как обычно использовать геоданные OSM, и вот для выбора сетевых магазинов без "черной магии" мне не удастся обойтись, потому что в исходных данных полно опечаток, разных написаний и разношёрстности в обозначениях. После получаса экспериментов мне удалось подобрать нужное заклинание:

create temporary table supermarket_entrance as 
  select id, centre, tags 
    from geometry_global_view 
    where (tags@>'shop=>supermarket' or tags@>'shop=>convenience') and 
    		(tags->'brand' in ('Пятёрочка','ВкусВилл','Дикси','Магнит',
                               'Перекрёсток','Магнолия','Перекресток',
                               'Лента','Eurospar','Ашан','Супер Лента',
                               'Атак','СуперЛента','О’КЕЙ','Spar',
                               'Перекресток Экспресс','SPAR',
                               'METRO Cash & Carry','EuroSPAR','АТАК',
                               'Ашан Сити','Мини Лента','EUROSPAR',
                               'МиниЛента','METRO','Auchan','супер Лента') 
    		or tags@>'brand:wikipedia=>ru:Auchan');

Подробнее про подготовку данных к анализу можете почитать в прошлой статье. Чтобы исключить из геоанализа мавзолей на Красной площади и Кремлевские башни добавлю фильтр:not(b.tags?'tomb') and not(b.tags?'man_made') и найду жилые здания у интересующих магазинов:

explain create table living_building_near as
select m.id supermarket_entrance_id,st_x(m.centre) supermarket_entrance_x,st_y(m.centre) supermarket_entrance_y,
       array_agg((b.id,b.type,st_x(b.centre),st_y(b.centre))::building_info) buildings
  from supermarket_entrance m inner join geometry_global_view b on
    b.tags?'building' and
    not(b.tags?'amenity') and
    not(b.tags?'shop') and
    not(b.tags?'tomb') and
    not(b.tags?'man_made') and
    b.tags->'building' not in --в перечисленных ниже зданиях не живут на постоянной основе
        ('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','entrance','construction','transformer'
        ) and 
    ST_DWithin(b.geom::geography,m.centre::geography,500) 
  group by 1,2,3; -- create table living_building_near as select m.id supermarket_entrance_id,st_x(m.centre) supermarket_entrance_x,st_y(m.centre) supermarket_entrance_y,array_agg((b.id,b.type,st_x(b.centre),st_y(b.centre))::building_info) buildings from supermarket_entrance m inner join geometry_global_view b on b.tags?'_lb' and ST_DWithin(b.geom::geography,m.centre::geography,500) group by 1,2,3;

Посчитаю пешеходные расстояния от магазина до домов в радиусе 500м. Чтобы учесть случаи когда дом расположен через реку или автостраду без подземного перехода, не включать его в расчет расстояния.

create table supermarket_distance(
                entrance_id bigint,
                building_id bigint,
                building_type table_reference,
                distance smallint,
                primary key(entrance_id,building_id,building_type)
);

package com.github.isuhorukov.routing;

import com.graphhopper.GHRequest;
import com.graphhopper.GHResponse;
import com.graphhopper.GraphHopper;
import com.graphhopper.ResponsePath;
import com.graphhopper.config.CHProfile;
import com.graphhopper.config.Profile;
import org.postgresql.jdbc.PgArray;
import org.postgresql.util.PGobject;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

public class Routing {

    public static final int MAX_DISTANCE = 500;

    public static void main(String[] args) throws Exception {
        GraphHopper hopper = getGraphHopper();
        try (Connection connection = DriverManager.getConnection(
                System.getenv("jdbc_url"), System.getenv("user"), System.getenv("password"))) {
            connection.setAutoCommit(false);
            try {
                var distances = calculateDistances(connection, hopper);
                saveDistance(connection, distances);
            } catch (SQLException e) {
                connection.rollback();
                throw e;
            }
        }
    }

    private static GraphHopper getGraphHopper() {
        var hopper = new GraphHopper();
        hopper.setOSMFile(System.getenv("osm_file"));
        hopper.setGraphHopperLocation("route");
        hopper.getCHPreparationHandler().setCHProfiles(new CHProfile("foot"));
        hopper.setProfiles(new Profile("foot").setVehicle("foot"));
        hopper.importOrLoad();
        return hopper;
    }

    private static List<Distance> calculateDistances(Connection connection, GraphHopper hopper) throws SQLException {
        List<Distance> distances = new ArrayList<>();
        try (Statement supermarketQuery = connection.createStatement()){
            int step=0;
            try (ResultSet livingBuilding = supermarketQuery.executeQuery("select * from living_building_near")){
                while (livingBuilding.next()){
                    long supermarketEntranceId = livingBuilding.getLong("supermarket_entrance_id");
                    double supermarketEntranceX = livingBuilding.getDouble("supermarket_entrance_x");
                    double supermarketEntranceY = livingBuilding.getDouble("supermarket_entrance_y");
                    PgArray buildingsArray = (PgArray) livingBuilding.getArray("buildings");
                    List<Building> buildings = mapBuildings(buildingsArray);
                    System.out.println(step++);
                    distances.addAll(buildings.stream().map(building -> {
                        short distance = calculateDistances(hopper, building, supermarketEntranceY, supermarketEntranceX);
                        return new Distance(supermarketEntranceId,building.buildingId, building.buildingType, distance);
                    }).collect(Collectors.toList()));
                }
            }
        }
        return distances;
    }

    private static void saveDistance(Connection connection, List<Distance> distances) throws SQLException {
        try (PreparedStatement insert = connection.prepareStatement(
                "insert into supermarket_distance(entrance_id,building_id,building_type,distance) " +
                        "values (?,?,?::table_reference,?)")){
            distances.forEach(distance -> {
                if(distance.distance> MAX_DISTANCE) return;
                try {
                    insert.setLong(1, distance.supermarketEntranceId);
                    insert.setLong(2, distance.buildingId);
                    insert.setString(3, distance.buildingType);
                    insert.setShort(4, distance.distance);
                    insert.addBatch();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            });
            insert.executeBatch();
            connection.commit();
        }
    }

    private static List<Building> mapBuildings(PgArray buildingsArray) throws SQLException {
        return getPgObjects(buildingsArray).stream().map(pGobject -> {
            String value = pGobject.getValue();
            if(value==null || value.length()<=2){
                throw new IllegalArgumentException();
            }
            String[] parts = value.substring(1, value.length() - 1).split(",");
            long buildingId = Long.parseLong(parts[0]);
            String buildingType = parts[1];
            double buildingX = Double.parseDouble(parts[2]);
            double buildingY = Double.parseDouble(parts[3]);
            return new Building(buildingId, buildingType, buildingX, buildingY);
        }).collect(Collectors.toList());
    }

    private static List<PGobject> getPgObjects(PgArray buildingsArray) throws SQLException {
        Object[] array = (Object[]) buildingsArray.getArray();
        buildingsArray.free();
        return Arrays.stream(array).map(object -> (PGobject) object).collect(Collectors.toList());
    }

    private static short calculateDistances(GraphHopper hopper,
                                      Building building, double supermarketEntranceY, double supermarketEntranceX) {
        GHRequest request = new GHRequest(supermarketEntranceY, supermarketEntranceX, building.buildingY, building.buildingX);
        request.setProfile("foot");
        GHResponse route = hopper.route(request);
        if(route.hasErrors()){
            return Short.MAX_VALUE;
        }
        ResponsePath bestRoute = route.getBest();
        return (short) Math.round(bestRoute.getDistance());
    }

    private static class Building{
        long buildingId;
        String buildingType;
        double buildingX;
        double buildingY;
        public Building(long buildingId, String buildingType, double buildingX, double buildingY) {
            this.buildingId = buildingId;
            this.buildingType = buildingType;
            this.buildingX = buildingX;
            this.buildingY = buildingY;
        }
    }

    private static class Distance{
        long supermarketEntranceId;
        long buildingId;
        String buildingType;
        short distance;
        public Distance(long supermarketEntranceId, long buildingId, String buildingType, short distance) {
            this.supermarketEntranceId = supermarketEntranceId;
            this.buildingId = buildingId;
            this.buildingType = buildingType;
            this.distance = distance;
        }
    }
}

Выгружаю результат анализа в GeoJSON файл и отправляю его на GitHub:

\copy (select json_build_object('type', 'FeatureCollection','features', json_agg(json_build_object('type', 'Feature','geometry', st_AsGeoJSON(centre)::json))) from (select distinct centre from supermarket_distance s inner join geometry_global_view b on b.id=s.building_id and b.type=s.building_type) buildings ) to '~/moscow_supermarket_footpath.json';

Интересные находки

Подъезды в жилых домах в Москве часто не размечены на OpenStreetMap.

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

В Москве для зданий иногда не хватает информации о его типе. Как заметили в коментариях к карте само здание магазина может быть учтено как жилое, например. В этом случае правильным решением будет изменить тип здания на building=retail и пересчитать карту. Я могу найти все такие здания и сформировать список, чтобы люди живущие в этом районе перепроверили и если здание действительно только с коммерческими объектами/магазином, обновили данные в OSM.

Исходные данные OpenStreetMap доступны каждому и по лицензии являются свободными. Не надо быть data science специалистом чтобы посчитать расстояния от магазинов до домов.

Результат геоаналитики

Готовую карту жилых домов Москвы в 500м от сетевых продуктовых магазинов вы можете посмотреть по ссылке на GitHub Gist. Где точки на карте обозначают центры жилых зданий в шаговой доступности от магазинов.

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

Случайная популярность или как работает пресса

А теперь пару слов как работают журналисты или кто-то заказал очернение этой публикации про работу с открытыми гео данными или это резюме от ChatGPT.

Помните мем: Ученые научились лечить рак?

В этом случае все произошло так же. Лента ру опубликовала статью про то что удобное не удобно. Интересно случайно или в этом есть цель обесценить мой труд? Оставлю скриншот:

И самое интересное ссылка ведет сразу на Github, избегая описания на Хабре... Сравните оригинал и новость!)