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