Ищем жилье в 500м от сетевого продуктового в Москве

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