Блог

Главная - Вопросы по программированию - POSTGIS: эффективный запрос ближайшей точки

POSTGIS: эффективный запрос ближайшей точки

#java #postgresql #postgis #openstreetmap

#java #postgresql #postgis #openstreetmap

Вопрос:

У меня следующая проблема: у меня есть миллионы записей (точек на карте), и я хочу сопоставить их с ближайшей дорогой (данные OSM). Я реализовал подход грубой силы в java, где я запрашиваю базу данных OSM, чтобы найти ближайшую дорогу, а затем присваиваю объект osm (road-id) соответствующей записи.

Однако этот подход неэффективен, поскольку запрос для каждой записи занимает почти 1 секунду, что делает весь процесс бесконечным.

         PreparedStatement psWithLastPosition = this.conn.prepareStatement(sql);

        int i = 0;
        for (FCDEntry entry : dataset.getEntries()) {
            i  = 1;
            String sqlQueryRoad = "SELECT osm_id,highway FROM planet_osm_roads ORDER BY " 
              "ST_DISTANCE('SRID=4326;" 
              ST_SetSRID.setSRID(new ST_MakePoint().createPoint(entry.getLongitude(),entry.getLatitude()), 4326).toString()
              "'::geometry, " 
              "ST_Transform(way::geometry,4326)) ASC LIMIT 1;";

            PreparedStatement psID = this.conn.prepareStatement(sqlQueryRoad);
            ResultSet rs = psID.executeQuery();
            String osm_id ="";
            while (rs.next()) {
                osm_id = rs.getString("osm_id");
            }

            log.info(osm_id);

            PreparedStatement ps = psWithLastPosition;
            ps.setString(1, entry.getAssetId());
            ...
            ps.setInt(18, Integer.valueOf(osm_id));
            ps.addBatch();

            // Execute every 1000 items
            if (i % 1000 == 0 || i == dataset.getEntries().size()) {
                log.info(i   "/"   dataset.getEntries().size());
                psWithLastPosition.executeBatch();
                psWithoutLastPosition.executeBatch();
            }

Есть идеи о том, как ускорить процесс сопоставления?

Комментарии:

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

2. используйте <-> оператор вместо st_distance в предложении order by, поскольку он может использовать пространственный индекс

3. создайте пространственный индекс

4. У меня создалось впечатление, что когда я вставил данные OSM в postgis, он автоматически сгенерировал пространственный индекс. Не могли бы вы указать мне примеры запросов, которые используют операторы <-> ?

5. Индекс автоматически создается на way , но вы используете ST_Transform(way::geometry,4326) , поэтому индекс не используется. Вам пришлось бы создать ее в этом выражении (мой комментарий об ОТКАЗЕ от использования 4326 остается в силе)

Ответ №1:

Решение основано на комментариях (благодаря @JGH).

Мне пришлось изменить команду st_distance с помощью оператора <->. Запрос теперь выполняется почти в 1000 раз быстрее.

т. е. мой запрос сейчас:

 String sqlQueryRoad = "SELECT osm_id,highway FROM planet_osm_roads ORDER BY " 
                      "way <-> ST_Transform(ST_GeomFromText('POINT ("  entry.getLongitude()   " " 
                      entry.getLatitude()  ")',4326), 3857) ASC LIMIT 1;";

По какой-то причине вместо столбца geom у меня был столбец way, связанный с геометрией (почти во всех найденных мной сообщениях был столбец geom, лучше всего предположить, что они изменили функцию вставки osm2pgsql для данных OSM).