Find closest points in routing network for a set of points to pick shortest route

Question

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. In the SQL statement I fetch 1 Point from the station and search the closest point in my routing network, but I want to do this for all stations from the WITH Query

WITH stations AS (SELECT id, geom FROM de_tt_stations AS s 
  WHERE ST_DWithin(
    s.geom::geography, 
    (SELECT ST_LineMerge(ST_union(geom_way))::geography FROM route),
    1000)
)
SELECT 1378549, destination 
FROM (
  SELECT id::integer AS destination 
  FROM de_2po_vertex ORDER BY geom_vertex <-> (
    SELECT geom FROM stations LIMIT 1)
  LIMIT 1) 
AS foo

From the resulting list then I want to calculate the length of each route so that I can choose the closest filling station. Optimal output would look like

start   | destination      | station_id  | length
---------------------------------------------------------
1378549 | de_2po_vertex.id | stations.id | pgr_dijkstra()
...

Answer 1

You could write a pl/pgsql-function in which you create a temp table(which you can drop on commit).then loop through the records of your (executed) sql-statement and write one result during each loop in this table.if all loops are done execute a "select * from temptable" and set the result of this query as return value.

Answer 2

Oh well I totally forgot about this question. I in general did what you said, but without a function. Also I changed to approach to the problem slightly to gain much better performance.

So instead of looking for the filling stations each time I need to refill in my simulation, I query all available filling stations at the beginning using the Query below.

CREATE TEMP TABLE filling (destination integer, station_id character varying(64)) ON COMMIT DROP;
INSERT INTO filling (station_id) SELECT id FROM de_tt_stations AS s  WHERE ST_DWithin(s.geom::geography, ST_GEomFromEWKB(%(route)s)::geography, %(distance)s);
UPDATE filling SET destination = (SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <-> (SELECT geom FROM de_tt_stations WHERE id = filling.station_id) LIMIT 1);
SELECT * FROM filling;

Now when I hit a refilling event in the simulation I use pgr_kdijkstraCost at the current point with all previously query available fuelstations

SELECT seq, id1 as start, id2 as destination, cost as distance FROM pgr_kdijkstraCost(
  'SELECT id, source, target, km as cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex WHERE id = %(r_start)s OR id = %(r_dest)s) as box WHERE geom_way && box.box', 
  1234, ARRAY[3651, 3655, 3694], false, false) AS result ORDER BY cost LIMIT 1

This way i save a lot of complex queries to the database and it has improved the simulation time for a commuter from 2.6 to 1.8sec.