Bugs/mods in sql scripts

[rodj59]

The function assign_vertex_id() seems to be missing from the 1.0 version; likewise for shortest_path_as_geometry. Was there any reason for these omissions? With the 6.2.2 version of GRASS, exports into PostgreSQL tend to create LINESTRINGS not MULTILINESTRINGS; though you can convert them, a few generalisations let the functions in the tutorial work on LINESTRINGS as well as MULTILINESTRINGS. The direct exports into PostgreSQL seem to capture more data than exports to shapefiles etc. Also, it's a bit tedious having to add the columns in by hand so I have modified them to create the needed columns automatically. There is a bug in some versions of postgis (eg.1.3.1) which causes endpoint() to crash the system. I have included a trivial (though less efficient) workaround. Overloading some of the functions allows extra convenience with choices of column names. There also seemed to be some non-functional code which I deleted. This stuff has not been extensively tested: treat with caution. There may be consequences which I haven't yet considered. There is a general inconsistency between use or source/source_id & target/target_id. A decision needs to be made as to which to use. I'm attaching the whole files since it could get messy.

[anton]

assign_vertex_id() function is not missing - it still exists in routing_core_wrappers.sql file (even in that one you posted here).

OK, I will test your files. Thanks!

[rodj59]

Yes it should have been shortest_path_as_geometry() instead of not as well as.

[rodj59]

Some fixes in the patch files: apply to files included here no originals.

[rodj59]

Could anyone please verify the following interpretation of graph,graph_vertices,graph_edges:- graph

---

source_id: points to (geom_id) in graph_vertices

target_id: points to (geom_id) in graph_vertices

edge_id: points to (id) in graph_edges

wkb_geometry(or the_geom): linestring of the arc

graph_vertices

---

id: primary key, reverse links to graph_edge's source or target column geom_id: links to graph's source_id or target_id wkb_geometry: point geometry of an endpoint of wkb_geometry in splitroadnet whose

source_id or target_id matches geom_id

graph_edges

---

id : primary key, reverse links to edge_id field of corresponding graph row

source: links to graph's geom_id column for the source endpoint

target: links to graph's geom_id column for the target endpoint

---

Implications , something is wrong in shortest_path_as_geometry() eg.

CREATE OR REPLACE FUNCTION shortest_path_as_geometry(geom_table varchar,

geom_source anyelement,geom_target anyelement,geomcol varchar,costcol varchar) RETURNS SETOF GEOMS AS

$$ DECLARE

r record; source int4; target int4; path_result record; v_id integer; e_id integer; geom geoms;

BEGIN

FOR r IN EXECUTE 'SELECT id FROM ' quote_ident(geom_table)

'_vertices WHERE id = ' quote_literal(geom_source) LOOP

source = r.id;

END LOOP;

IF source IS NULL THEN

RAISE EXCEPTION 'Cant find source edge';

END IF;

FOR r IN EXECUTE 'SELECT id FROM ' quote_ident(geom_table)

'_vertices WHERE id = ' quote_literal(geom_target) LOOP

target = r.id;

END LOOP;

IF target IS NULL THEN

RAISE EXCEPTION 'Cant find target edge';

END IF;

FOR geom IN SELECT * FROM

shortest_path_as_geometry_internal_id(geom_table,

source, target,geomcol,costcol) LOOP

RETURN NEXT geom;

END LOOP;

RETURN;

END; $$ LANGUAGE 'plpgsql' VOLATILE STRICT;

[anton]

Why do you need vertices table?

[rodj59]

I didn't write the function, only changed geom_id to id since that's what it needs to be in this case unless the tables have been wrongly constructed.

[anton]

Ah! I see. Thanks! I will test it.

[rodj59]

To make it work, change the loop in

-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION shortest_path_as_geometry_internal_id(
       geom_table varchar, source int4, target int4,geomcol varchar,costcol varchar) 
       RETURNS SETOF GEOMS AS
$$

with

		
	query := 'SELECT a.edge_id as gid,'||quote_ident(geomcol)||' as the_geom FROM ' ||
          'shortest_path(''SELECT source ,id,  target, ' || quote_ident(costcol)||
          ' as cost from '||quote_ident(geom_table)||'_edges'','||source||','||target||',false,false)' ||
			' a, '||quote_ident(geom_table)||' b where b.edge_id = a.edge_id';
	  
	FOR path_result IN EXECUTE query

[rodj59]

This applies to original file in this ticket.

[rodj59]

You can get driving distance for example by

select sum(length_wgs84(the_geom)) from shortest_path_as_geometry('splitroadnet',182,547,'wkb_geometry','length_gd')

I haven't taken into account usage of non-integer indexes since much of the original code ignores this possibility.
It might be an idea to have a "contributed" code repository somewhere?

[sunrise]

This works ok for me.. but It probably needs feedback. postlbs Air Jordan

[rodj59]

Adapted to ver 1.3 & for osm. Not fully tested. This version uses source/target rather than source_id/target_id. The files should be loaded in after the standard ones.