如何使單行結果查詢返回多行

我有以下 WITH SQL 查詢，它確實選擇了 1000m 距離內沿路線的一組加油站。為了以米為單位進行測量，我將幾何圖形轉換為地理。

我也有一個給定的路線作為帶有 SRID 4326 的線串。

在 SQL 語句中，我從車站獲取 1 個點並蒐索路由網路中最近的點，但我想對 WITH 查詢中的所有車站執行此操作

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

從結果列表中，我想計算每條路線的長度，以便我可以選擇最近的加油站。最佳輸出看起來像

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

我正在使用 PostgreSQL 9.5、Postgis 2.1.5 和 pgrouting 2.0.0

表定義：

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)
)

我發現可行的問題的可能解決方案是分步解決方案，如下所示：

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;

執行時間是公平的，大約 500 毫秒或更短。有沒有可能進一步優化查詢？

引用自：https://dba.stackexchange.com/questions/89628