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(
    (SELECT ST_LineMerge(ST_union(geom_way))::geography FROM route),
SELECT 1378549, destination 
  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()
  • You want to do this with a single query? I think it's easier to write a custom function. You can find an example in the pgRouting workshop: workshop.pgrouting.org/chapters/… – dkastl Jan 19 '15 at 6:35
  • Well I know the tutorials function, but it also only takes a pair of points an calculates the distance. I would prefer the one query solution, if possible. – Benjamin Jan 19 '15 at 12:48

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.