Блог

Главная - Вопросы по программированию - Использовать результаты одного SQL-запроса PostGIS в предложении WHERE другого запроса

Использовать результаты одного SQL-запроса PostGIS в предложении WHERE другого запроса

#sql #postgresql #postgis

#sql #postgresql #postgis

Вопрос:

Я создаю картографическое приложение, которое хранит GPS-треки в виде списков координат в вызываемой таблице base_points .

У меня есть веб-приложение, которое использует листовку для отображения этих дорожек. Когда я перемещаю карту, я выполняю AJAX-вызов на свой серверный сервер с границами текущего представления.

Я хотел бы вернуть упрощенный набор точек в пределах карты.

Сначала я использовал этот запрос, который работает довольно хорошо:

 SELECT ST_AsGeoJSON(
               ST_Simplify(
                       ST_MakeLine(ARRAY(SELECT geom
                                         FROM base_point
                                         WHERE track_id = %s
                                           AND geom @
                                               ST_MakeEnvelope(
                                                       %s, %s, %s, %s, %s)
                                         ORDER BY time)),
                       %s
                   )
           );

Проблема в том, что на определенных уровнях масштабирования, где дорожка существует и входит в область просмотра, я получаю подмножество дорожки, поскольку некоторые части вырезаются geom @ ST_MakeEnvelop . Я хотел бы найти минимальную и максимальную точку отслеживания в окне просмотра и использовать их для извлечения всех точек, а затем использовать ST_Simplify(ST_MakeLine) .

Я пытаюсь использовать следующее выражение SQL, но оно не работает.

 WITH bounds as (SELECT MIN(id) as min, MAX(id) as max
                FROM base_point
                WHERE track_id = %s
                  AND geom @ ST_MakeEnvelope(%s, %s, %s, %s, %s))
SELECT ST_AsGeoJSON(ST_Simplify(ST_MakeLine(ARRAY(
        SELECT geom 
        FROM base_point, bounds 
        WHERE track_id = %s 
            AND id < bounds.max 
          AND id > bounds.min ORDER BY time
    )),
%s));

Я отредактировал это ранее, и теперь запрос работает, но очень медленно.

Вот результат explain / analyze:

 Result  (cost=8.95..34.46 rows=1 width=32) (actual time=59806.894..59806.896 rows=1 loops=1)
  InitPlan 1 (returns $2)
    ->  Sort  (cost=8.95..8.95 rows=1 width=40) (actual time=59803.755..59804.242 rows=14001 loops=1)
"          Sort Key: base_point.""time"""
          Sort Method: quicksort  Memory: 1478kB
          ->  Nested Loop  (cost=4.88..8.94 rows=1 width=40) (actual time=13.074..59799.753 rows=14001 loops=1)
                Join Filter: ((base_point.id < (max(base_point_1.id))) AND (base_point.id > (min(base_point_1.id))))
                Rows Removed by Join Filter: 523
                ->  Index Scan using base_point_track_id_b527d95c on base_point  (cost=0.43..4.45 rows=1 width=44) (actual time=0.031..2.440 rows=14524 loops=1)
                      Index Cond: (track_id = 40)
                ->  Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=4.117..4.117 rows=1 loops=14524)
                      ->  Index Scan using base_point_track_id_b527d95c on base_point base_point_1  (cost=0.43..4.45 rows=1 width=4) (actual time=0.005..3.477 rows=14003 loops=14524)
                            Index Cond: (track_id = 40)
                            Filter: (geom @ '0103000020E61000000100000005000000C3F5285C8FF256C08FC2F5285C4F4340C3F5285C8FF256C07B14AE47E15A4440A4703D0AD73355C07B14AE47E15A4440A4703D0AD73355C08FC2F5285C4F4340C3F5285C8FF256C08FC2F5285C4F4340'::geometry)
                            Rows Removed by Filter: 521
Planning Time: 0.294 ms
Execution Time: 59807.087 ms

Если я выделяю запросы и жестко кодирую идентификаторы для сравнения, запрос выполняется за 15 мс.

Ответ №1:

Добавьте границы к вашему предложению from . Выберите геометрию из base_point, границы которой…..

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

1. Это кажется слишком простым! Я попробую это позже сегодня.

2. Это работает, но очень медленно. Я обновил вопрос. Я не думаю, что результаты CTE действительно кэшируются, но я никогда раньше не читал вывод explain / analyze, поэтому это немного загадочно. Может быть, это будет иметь больше смысла завтра, после того, как я посплю.

Ответ №2:

Я понял это. Выполнение соединения намного быстрее.

 WITH bounds as (SELECT MIN(id) as min, MAX(id) as max
                FROM base_point
                WHERE track_id = %s
                  AND geom @ ST_MakeEnvelope(%s, %s, %s, %s, %s))
SELECT ST_AsGeoJSON(ST_Simplify(ST_MakeLine(ARRAY(
        SELECT geom 
        FROM base_point
        INNER JOIN bounds ON
            id < bounds.max 
          AND id > bounds.min ORDER BY time
    )),
%s));

Если кто-нибудь может объяснить, почему, я был бы очень признателен!