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

nearest neighborpostgispostgresqlselectsubquery

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

Best Answer

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?

Related Question