<div dir="ltr"><div>Steve,<br></div><div><br></div><div>Thank you, I will follow your Instructions. I don't expect a lot of problems with turn restrictions because maneuvers table is still empty and will be filled after routing become functional.</div>
<div>I still have some baby problems to make pgRouting functional.</div><div><br></div><div>For example what is correct range of tolerance if i use meters not degrees in assign vertex?</div><div><br></div><div>Miroslav</div>
<div><br></div><div class="gmail_quote">2011/3/23 Stephen Woodbridge <span dir="ltr"><<a href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Charles,<br>
<br>
Thank you for posting that. I am glad I read the whole thread because I was just about to did up my old code and dust it off to post. There is no need for that as your implementation accurately reflects mine.<br>
<br>
Miroslav,<br>
<br>
Regarding turn restrictions, you should search the archives for this as I have been fairly vocal about the limitations. To summarize:<br>
<br>
1. turn restrictions only work with shooting star<br>
2. If you have multiple turn restrictions, I think you have to enter the related links multiple times, ie: once for each restriction<br>
3. IMHO, we need to rework the turn restrictions to make adding them more intuitive and easier<br>
4. before you try to implement a large number of them, I would verify that you can in fact add multiple turn restrictions in a trivial graph that is easy to verify if it works as expected, because it is my sense that this area has not been used to any great extent.<br>
<br>
-Steve<div><div class="h5"><br>
<br>
On 3/23/2011 7:43 AM, Charles Galpin wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi Miroslav<br>
<br>
This is an implementation I have been using which I made after a<br>
discussion with Stephen on this subject. It is essentially an<br>
implementation of his ideas and works fine for me, but ymmv.<br>
<br>
Otherwise search the email archives as I believe he posted his here<br>
at some point as well.<br>
<br>
hth charles<br>
<br>
On Mar 23, 2011, at 6:44 AM, Miroslav Novta wrote:<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hello list,<br>
<br>
I want to use the assign_vertices_3d() function developed by<br>
Stephen Woodbridge. I want to use it for iGO shp map format with<br>
"turn restrictions" too.<br>
<br>
Is there source code and documentation available on any public<br>
repository I may access?<br>
</blockquote>
<br>
-- Function: assign_vertex_id3d(character varying, double precision,<br>
character varying, character varying, character varying, character<br>
varying)<br>
<br>
-- DROP FUNCTION assign_vertex_id3d(character varying, double<br>
precision, character varying, character varying, character varying,<br>
character varying);<br>
<br>
CREATE OR REPLACE FUNCTION assign_vertex_id3d(geom_table character<br>
varying, tolerance double precision, geo_cname character varying,<br>
gid_cname character varying, source_zlevel_cname character varying,<br>
target_zlevel_cname character varying) RETURNS character varying AS<br>
$BODY$ DECLARE _r record; source_id int; target_id int; srid<br>
integer; BEGIN<br>
<br>
BEGIN DROP TABLE vertices_tmp; EXCEPTION WHEN UNDEFINED_TABLE THEN<br>
END;<br>
<br>
EXECUTE 'CREATE TABLE vertices_tmp (id serial)';<br>
<br>
-- FOR _r IN EXECUTE 'SELECT srid FROM geometry_columns WHERE<br>
f_table_name='''|| quote_ident(geom_table)||''';' LOOP -- srid<br>
:= _r.srid; -- END LOOP;<br>
<br>
srid :=<br>
Find_SRID('public',quote_ident(geom_table),quote_ident(geo_cname));<br>
<br>
<br>
EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'',<br>
'||srid||', ''POINT'', 3)'; CREATE INDEX vertices_tmp_idx ON<br>
vertices_tmp USING GIST (the_geom);<br>
<br>
FOR _r IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,' ||<br>
' ST_MakePoint(ST_X(StartPoint('|| quote_ident(geo_cname) ||')),<br>
ST_Y(StartPoint('|| quote_ident(geo_cname) ||')), '||<br>
quote_ident(source_zlevel_cname) ||') AS source,' || '<br>
ST_MakePoint(ST_X(EndPoint('|| quote_ident(geo_cname) ||')),<br>
ST_Y(EndPoint('|| quote_ident(geo_cname) ||')), '||<br>
quote_ident(target_zlevel_cname) ||') AS target' || ' FROM ' ||<br>
quote_ident(geom_table) LOOP<br>
<br>
source_id := point_to_id3d(setsrid(_r.source, srid), tolerance);<br>
target_id := point_to_id3d(setsrid(_r.target, srid), tolerance);<br>
<br>
EXECUTE 'update ' || quote_ident(geom_table) || ' SET source = ' ||<br>
source_id || ', target = ' || target_id || ' WHERE ' ||<br>
quote_ident(gid_cname) || ' = ' || _<a href="http://r.id" target="_blank">r.id</a>; END LOOP;<br>
<br>
RETURN 'OK';<br>
<br>
END; $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT COST 100; ALTER<br>
FUNCTION assign_vertex_id3d(character varying, double precision,<br>
character varying, character varying, character varying, character<br>
varying) OWNER TO postgres;<br>
<br>
<br>
-- Function: point_to_id3d(geometry, double precision)<br>
<br>
-- DROP FUNCTION point_to_id3d(geometry, double precision);<br>
<br>
CREATE OR REPLACE FUNCTION point_to_id3d(p geometry, tolerance double<br>
precision) RETURNS bigint AS $BODY$<br>
<br>
DECLARE _r record; _id bigint; _srid integer;<br>
<br>
BEGIN<br>
<br>
_srid := Find_SRID('public','vertices_tmp','the_geom');<br>
<br>
SELECT ST_Length3D(ST_MakeLine(the_geom,p)) AS d, id<br>
<br>
INTO _r FROM vertices_tmp WHERE<br>
<br>
the_geom&& Expand(p, tolerance) AND<br>
ST_Length3D(ST_MakeLine(the_geom,p))< tolerance<br>
<br>
ORDER BY d LIMIT 1;<br>
<br>
IF FOUND THEN<br>
<br>
_id:= _<a href="http://r.id" target="_blank">r.id</a>;<br>
<br>
ELSE<br>
<br>
INSERT INTO vertices_tmp(the_geom) VALUES (SetSRID(p,_srid));<br>
_id:=lastval();<br>
<br>
END IF;<br>
<br>
RETURN _id;<br>
<br>
END; $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT COST 100; ALTER<br>
FUNCTION point_to_id3d(geometry, double precision) OWNER TO<br>
postgres;<br>
<br>
<br>
_______________________________________________ Pgrouting-users<br>
mailing list <a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
</blockquote>
<br>
_______________________________________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Miroslav Novta<br>
</div>