0

The pgRouting pgr_drivingDistance function returns only ids of road network vertices, but without ids of road edges in specific drive time.
To find this road edges I created the query:

SELECT all_roads.id, the_geom
    FROM
        (
            SELECT e.id, e.the_geom
            FROM tmp_ddist dd,
                tmp_edge e
            WHERE
                e.target = dd.vertex 
        UNION
            SELECT e.id, e.the_geom
            FROM tmp_ddist dd,
                tmp_edge e
            WHERE
                e.source = dd.vertex 
        ) all_roads
    GROUP BY all_roads.id, the_geom
    HAVING COUNT(all_roads.id) = 2

Basically it finds road edges which have source and target vertices in set generated by pgr_drivingDistance function and returns only those edges which have both source and target in this set.

Is there any way to optimize this query?

Marcin
  • 123
  • 4
  • I don't get the difference in the inner selects. They are the same and you are doing `UNION` which makes `DISTINCT` and `ORDER BY` operations i.e. you are doing second select in vain. – Mladen Uzelac Nov 10 '14 at 12:56
  • 1
    In first SELECT I compare with `e.target` and in second with `e.source` – Marcin Nov 10 '14 at 12:58

1 Answers1

0

Can you try like that and see if this helps:

SELECT all_roads.id, the_geom
  FROM
    (
        SELECT e.id, e.the_geom
        FROM tmp_ddist dd,
            tmp_edge e
        WHERE
            e.target = dd.vertex 
    UNION
        SELECT e2.id, e2.the_geom
        FROM tmp_ddist dd2,
            tmp_edge e2
        WHERE
            e2.source = dd2.vertex AND
            e2.id <> e.id
    ) all_roads
GROUP BY all_roads.id, the_geom
HAVING COUNT(all_roads.id) = 2

I am curious to see the benchmarking between the queries.

Antoan Milkov
  • 2,152
  • 17
  • 30