1

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)
)
  • 2
    PostgreSQL 9.5? Really? That is not going to be released until the end of this year. – a_horse_with_no_name Jan 19 '15 at 13:16
  • What happens if you remove the limit 1? – a_horse_with_no_name Jan 19 '15 at 13:17
  • 1
    Yes I had to use postgresql 9.5(-dev) because of the SKIP LOCKED Feature, which is pretty awesome. – Benjamin Jan 19 '15 at 14:57
  • 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. – Benjamin Jan 19 '15 at 15:00
  • 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/… – Erwin Brandstetter Jan 20 '15 at 1:00
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?

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.