Created
March 2, 2015 21:23
-
-
Save mixedbredie/e206a2d27927160ca2b3 to your computer and use it in GitHub Desktop.
TRSP wrapper with distance costs and turn restrictions
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- TRSP wrapper with distance costs and turn restrictions | |
-- Function: routing.pgr_fromatob_trsp_length(character varying, double precision, double precision, double precision, double precision) | |
-- DROP FUNCTION routing.pgr_fromatob_trsp_length(character varying, double precision, double precision, double precision, double precision); | |
CREATE OR REPLACE FUNCTION routing.pgr_fromatob_trsp_length(IN tbl character varying, IN x1 double precision, IN y1 double precision, IN x2 double precision, IN y2 double precision, OUT seq integer, OUT gid integer, OUT name text, OUT heading double precision, OUT cost double precision, OUT geom geometry) | |
RETURNS SETOF record AS | |
$BODY$ | |
DECLARE | |
sql text; | |
rec record; | |
source integer; | |
target integer; | |
point integer; | |
BEGIN | |
-- Find nearest node | |
EXECUTE 'SELECT id::integer FROM itn_network_vertices_pgr | |
ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' | |
|| x1 || ' ' || y1 || ')'',27700) LIMIT 1' INTO rec; | |
source := rec.id; | |
EXECUTE 'SELECT id::integer FROM itn_network_vertices_pgr | |
ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' | |
|| x2 || ' ' || y2 || ')'',27700) LIMIT 1' INTO rec; | |
target := rec.id; | |
-- Shortest path query (TODO: limit extent by BBOX) | |
seq := 0; | |
sql := 'SELECT gid, geometry, COALESCE(roadname,dftname) AS name, cost, source, target, ST_Reverse(geometry) AS flip_geom FROM ' || | |
'pgr_trsp(''SELECT gid as id, source::int, target::int, ' | |
|| 'cost_len::float AS cost, rcost_len::float AS reverse_cost FROM ' | |
|| quote_ident(tbl) || '''::text' || ', ' | |
|| source || ', ' | |
|| target || ', ' | |
|| 'true, true, ' | |
|| '''SELECT to_cost, teid AS target_id, feid||COALESCE('''',''''||via,'''''''') AS via_path FROM routing.itn_turn_restrictions''::text), ' | |
|| quote_ident(tbl) | |
|| ' WHERE gid = id2 ORDER BY seq'; | |
-- Remember start point | |
point := source; | |
FOR rec IN EXECUTE sql | |
LOOP | |
-- Flip geometry (if required) | |
IF ( point != rec.source ) THEN | |
rec.geometry := rec.flip_geom; | |
point := rec.source; | |
ELSE | |
point := rec.target; | |
END IF; | |
-- Calculate heading (simplified) | |
EXECUTE 'SELECT degrees( ST_Azimuth( | |
ST_StartPoint(''' || rec.geometry::text || '''), | |
ST_EndPoint(''' || rec.geometry::text || ''') ) )' | |
INTO heading; | |
-- Return record | |
seq := seq + 1; | |
gid := rec.gid; | |
name := rec.name; | |
cost := rec.cost; | |
geom := rec.geometry; | |
RETURN NEXT; | |
END LOOP; | |
RETURN; | |
END; | |
$BODY$ | |
LANGUAGE plpgsql VOLATILE STRICT | |
COST 100 | |
ROWS 1000; | |
ALTER FUNCTION routing.pgr_fromatob_trsp_length(character varying, double precision, double precision, double precision, double precision) | |
OWNER TO postgres; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment