Skip to content

Instantly share code, notes, and snippets.

@sukhjitsehra
Forked from woodbri/pgr_graphviz.sql
Created July 25, 2016 17:39
Show Gist options
  • Save sukhjitsehra/f2ba40a4b727e5a29962f0a4180fc527 to your computer and use it in GitHub Desktop.
Save sukhjitsehra/f2ba40a4b727e5a29962f0a4180fc527 to your computer and use it in GitHub Desktop.
Here is a stored procedure that can be user to create a GraphViz input file from a pgRouting edge table. It create a text output that can be copied to myfile.gv, then can be converted to an image using ``neato -Tpng -O myfile.gv`` and the image will be in ``myfile.gv.png``.
create or replace function pgr_graphviz(edge_table text,
eid text default 'id',
source text default 'source',
target text default 'target',
oneway text default '',
ft text[] default array['FT']::text[],
tf text[] default array['TF']::text[],
where_clause text default 'true',
nodepos bool default false)
returns text as
$body$
declare
txt text;
sql text;
rec record;
r record;
begin
-- setup the file header information
txt := '
/*
generated by: select pgr_graphviz('''||edge_table||''', '''||eid||''', '''||source||''', '''||target||''',
'''||oneway||''', ''array['||array_to_string(ft, ',')||']'',
''array['||array_to_string(tf, ',')||']'',
'''||where_clause||''','||nodepos||');
';
if oneway = '' then
txt := txt || ' save result as: graph.gv
run: neato -Tpng -O graph.gv
generates: graph.png
';
else
txt := txt || ' save result as: digraph.gv
run: neato -Tpng -O digraph.gv
or run: dot -Tpng -O digraph.gv
generates: digraph.png
';
end if;
txt := txt || '
*/
';
if oneway = '' then
txt := txt || 'graph {
';
else
txt := txt || 'digraph {
';
end if;
if nodepos then
select * from pgr_getTableName(edge_table||'_vertices_pgr') INTO r;
if r.tname is null then
raise notice 'Table % does not exist can not position nodes!', quote_literal(edge_table||'_vertices_pgr');
else
sql := 'select id, st_x(the_geom) as x, st_y(the_geom) as y
from ' || pgr_quote_ident(edge_table||'_vertices_pgr') ||
' order by id';
for rec in execute sql loop
txt := txt || rec.id || ' [pos="' || rec.x || ',' || rec.y ||'"];
';
end loop;
end if;
end if;
sql := 'select ' || quote_ident(eid) || ' as eid, ' ||
quote_ident(source) || ' as source, ' ||
quote_ident(target) || ' as target';
if oneway != '' then
sql := sql || ', ' || quote_ident(oneway) || '::text as oneway';
end if;
sql := sql || ' from '||pgr_quote_ident(edge_table)||' where '||where_clause;
for rec in execute sql loop
if oneway != '' then
if tf @> ARRAY[rec.oneway] then
txt := txt || rec.target || ' -> ' || rec.source || ' [weight="1.0" label="' || rec.eid || '"];
';
elsif ft @> ARRAY[rec.oneway] then
txt := txt || rec.source || ' -> ' || rec.target || ' [weight="1.0" label="' || rec.eid || '"];
';
else
txt := txt || rec.source || ' -> ' || rec.target || ' [weight="1.0" label="' || rec.eid || '"];
';
txt := txt || rec.target || ' -> ' || rec.source || ' [weight="1.0" label="' || rec.eid || '"];
';
end if;
else
txt := txt || rec.source || ' -- ' || rec.target || ' [weight="1.0" label="' || rec.eid || '"];
';
end if;
end loop;
txt := txt || '}';
return txt;
end;
$body$
language plpgsql volatile strict;
/*
select pgr_graphviz('edge_table');
select pgr_graphviz('edge_table', oneway:='dir');
select pgr_graphviz('network', nodepos:=true);
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment