How to make a single row result query to return multiple rows

Question

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography.

I also have a given route as a Linestring with the SRID 4326.

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, ST_FromEWKB(%(route)s)::geography, 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 | SUM(pgr_dijkstra())
...

I'm using PostgreSQL 9.5, Postgis 2.1.5 and pgrouting 2.0.0

Table definitions:

public.de_2po_4pgr
(
 id integer NOT NULL,
 osm_id bigint,
 osm_name character varying,
 osm_meta character varying,
 osm_source_id bigint,
 osm_target_id bigint,
 clazz integer,
 flags integer,
 source integer,
 target integer,
 km double precision,
 kmh integer,
 cost double precision,
 reverse_cost double precision,
 x1 double precision,
 y1 double precision,
 x2 double precision,
 y2 double precision,
 geom_way geometry(LineString,4326),
 CONSTRAINT pkey_de_2po_4pgr PRIMARY KEY (id)
)
public.de_2po_vertex
(
 id integer NOT NULL,
 clazz integer,
 osm_id bigint,
 osm_name character varying,
 ref_count integer,
 restrictions character varying,
 geom_vertex geometry(Point,4326),
 CONSTRAINT pkey_de_2po_vertex PRIMARY KEY (id)
)
public.de_tt_stations
(
 id character varying(255) NOT NULL,
 name character varying(255),
 brand character varying(255),
 street character varying(255),
 "number" character varying(255),
 zip character varying(255),
 city character varying(255),
 premium_e5_time timestamp without time zone,
 premium_e5_value double precision,
 premium_e10_time timestamp without time zone,
 premium_e10_value double precision,
 diesel_time timestamp without time zone,
 diesel_value double precision,
 holiday_identifier character varying(255),
 opening_times text,
 opening_times_extended text,
 override_opening_times text,
 geom geometry(Point,4326),
 CONSTRAINT de_tt_stations_id_pkey PRIMARY KEY (id)
)

• user1822: PostgreSQL 9.5? Really? That is not going to be released until the end of this year.
• user1822: What happens if you remove the limit 1?
• Benjamin: Yes I had to use postgresql 9.5(-dev) because of the SKIP LOCKED Feature, which is pretty awesome.
• Benjamin: If I remove the ylimit 1 from the SELECT geom FROM stations I get an error. If I remove the other LIMIT 1 I get an ordered list of all points which are ordered by distance.
• Erwin Brandstetter: Why the need to cast s.geom::geography and id::integer? Precise table definitions would help (as always). Seems like you are dealing with a classical "nearest neighbour" problem. This may be of use: dba.stackexchange.com/questions/60619/order-by-distance/…

Answer 1

A possible Solution to the Problem I found working is a step-by-step solution which looks like this:

BEGIN;
CREATE TEMP TABLE route ON COMMIT DROP AS
 SELECT seq, source, target, km, kmh, clazz, geom_way 
 FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex 
 WHERE id = 1362258 OR id = 1625523 LIMIT 1) as box WHERE geom_way && box.box', 
 1362258, 1625523, FALSE, FALSE) AS route
 LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id 
 ORDER BY seq;
CREATE TEMP TABLE filling (start integer, destination integer, station_id character varying(255), distance double precision) ON COMMIT DROP;
INSERT INTO filling (start, station_id) 
 SELECT 1378549, id FROM de_tt_stations AS s WHERE ST_DWithin(s.geom::geography, (SELECT ST_LineMerge(ST_union(geom_way))::geography FROM route), 1000);
 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);
WITH f AS (SELECT start, destination FROM filling)
UPDATE filling SET distance = (SELECT SUM(km) AS distance FROM (
 SELECT km FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, 
 (SELECT ST_Expand(ST_Extent(geom_vertex),0.05) as box FROM de_2po_vertex WHERE id = '|| filling.start ||' OR id = '|| filling.destination ||' LIMIT 1) as box 
 WHERE geom_way && box.box', filling.start, filling.destination, FALSE, FALSE) AS route 
 LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id) as dist);
SELECT * FROM filling ORDER BY distance;
COMMIT;

Execution Time is fair with around 500ms and less. Is there any possibility to further optimize the Querys?