Ищем жилье в 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)
);

Основная проблема с расчетом расстояний - это время вычислений, в случае если сервис взаимодействует с программой как REST через TCP добавляется round trip пакетов к времени обработки каждого запроса, поэтому буду использовать Graphhopper как библиотеку внутри процесса без вызовов по HTTP:

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;
        }
    }
}