<div dir="ltr"><div>Hi Charles,<br></div><div><br></div><div>Thank you for code.</div><div><br></div><div>Regards,</div><div>Miroslav</div><br><div class="gmail_quote">2011/3/23 Charles Galpin <span dir="ltr"><<a href="mailto:cgalpin@lhsw.com">cgalpin@lhsw.com</a>></span><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 discussion with Stephen on this subject. It is essentially an 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 at some point as well.<br>
<br>
hth<br>
charles<br>
<div class="im"><br>
On Mar 23, 2011, at 6:44 AM, Miroslav Novta wrote:<br>
<br>
> Hello list,<br>
><br>
> I want to use the assign_vertices_3d() function developed by Stephen Woodbridge. I want to use it for iGO shp map format with "turn restrictions" too.<br>
><br>
> Is there source code and documentation available on any public repository I may access?<br>
<br>
</div>-- Function: assign_vertex_id3d(character varying, double precision, character varying, character varying, character varying, character varying)<br>
<br>
-- DROP FUNCTION assign_vertex_id3d(character varying, double precision, character varying, character varying, character varying, character varying);<br>
<br>
CREATE OR REPLACE FUNCTION assign_vertex_id3d(geom_table character varying, tolerance double precision, geo_cname character varying, gid_cname character varying, source_zlevel_cname character varying, target_zlevel_cname character varying)<br>
RETURNS character varying AS<br>
$BODY$<br>
DECLARE<br>
_r record;<br>
source_id int;<br>
target_id int;<br>
srid integer;<br>
BEGIN<br>
<br>
BEGIN<br>
DROP TABLE vertices_tmp;<br>
EXCEPTION<br>
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 f_table_name='''|| quote_ident(geom_table)||''';' LOOP<br>
-- srid := _r.srid;<br>
-- END LOOP;<br>
<br>
srid := Find_SRID('public',quote_ident(geom_table),quote_ident(geo_cname));<br>
<br>
<br>
EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', '||srid||', ''POINT'', 3)';<br>
CREATE INDEX vertices_tmp_idx ON 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) ||')), ST_Y(StartPoint('|| quote_ident(geo_cname) ||')), '|| quote_ident(source_zlevel_cname) ||') AS source,'<br>
|| ' ST_MakePoint(ST_X(EndPoint('|| quote_ident(geo_cname) ||')), ST_Y(EndPoint('|| quote_ident(geo_cname) ||')), '|| quote_ident(target_zlevel_cname) ||') AS target'<br>
|| ' FROM ' || quote_ident(geom_table)<br>
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) ||<br>
' SET source = ' || source_id ||<br>
', target = ' || target_id ||<br>
' WHERE ' || quote_ident(gid_cname) || ' = ' || _<a href="http://r.id" target="_blank">r.id</a>;<br>
END LOOP;<br>
<br>
RETURN 'OK';<br>
<br>
END;<br>
$BODY$<br>
LANGUAGE 'plpgsql' VOLATILE STRICT<br>
COST 100;<br>
ALTER FUNCTION assign_vertex_id3d(character varying, double precision, character varying, character varying, character varying, character 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 precision)<br>
RETURNS bigint AS<br>
$BODY$<br>
<br>
DECLARE<br>
_r record;<br>
_id bigint;<br>
_srid integer;<br>
<br>
BEGIN<br>
<br>
_srid := Find_SRID('public','vertices_tmp','the_geom');<br>
<br>
SELECT<br>
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 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)); _id:=lastval();<br>
<br>
END IF;<br>
<br>
RETURN _id;<br>
<br>
END; $BODY$<br>
LANGUAGE 'plpgsql' VOLATILE STRICT<br>
COST 100;<br>
ALTER FUNCTION point_to_id3d(geometry, double precision) OWNER TO postgres;<br>
<br>
<br>
_______________________________________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org">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></div><br><br clear="all"><br>-- <br>Miroslav Novta<br>
</div>