[pgrouting-users] assign_vertices_3d source code & docs

[Stephen Woodbridge]

On 3/23/2011 10:23 AM, Miroslav Novta wrote:
> Steve,
>
> 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.
> I still have some baby problems to make pgRouting functional.
>
> For example what is correct range of tolerance if i use meters not
> degrees in assign vertex?

This depends on your data for example in degrees if you data has a 
resolution of 0.000001 then this is a good value to use.

For meters, you might use 0.5 or 0.9, the best way to evaluate this is 
to look the the coordinates at the ends of to segments that should be 
connected. So:
     abs(Xa - Xb) < tolerance
to match the two ends to the same node. If you set tolerance too big 
then it will merge close by nodes that should not be connected. If you 
set the tolerance too small it will not match nodes that should be 
connected. We use tolerance to deal with floating point numbers not 
being exact like integers and can have rounding errors in the numbers.

-Steve

> Miroslav
>
> 2011/3/23 Stephen Woodbridge <woodbri at swoodbridge.com
> <mailto:woodbri at swoodbridge.com>>
>
>     Charles,
>
>     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.
>
>     Miroslav,
>
>     Regarding turn restrictions, you should search the archives for this
>     as I have been fairly vocal about the limitations. To summarize:
>
>     1. turn restrictions only work with shooting star
>     2. If you have multiple turn restrictions, I think you have to enter
>     the related links multiple times, ie: once for each restriction
>     3. IMHO, we need to rework the turn restrictions to make adding them
>     more intuitive and easier
>     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.
>
>     -Steve
>
>
>     On 3/23/2011 7:43 AM, Charles Galpin wrote:
>
>         Hi Miroslav
>
>         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.
>
>         Otherwise search the email archives as I believe he posted his here
>         at some point as well.
>
>         hth charles
>
>         On Mar 23, 2011, at 6:44 AM, Miroslav Novta wrote:
>
>             Hello list,
>
>             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.
>
>             Is there source code and documentation available on any public
>             repository I may access?
>
>
>         -- Function: assign_vertex_id3d(character varying, double precision,
>         character varying, character varying, character varying, character
>         varying)
>
>         -- DROP FUNCTION assign_vertex_id3d(character varying, double
>         precision, character varying, character varying, character varying,
>         character varying);
>
>         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) RETURNS character varying AS
>         $BODY$ DECLARE _r record; source_id int; target_id int; srid
>         integer; BEGIN
>
>         BEGIN DROP TABLE vertices_tmp; EXCEPTION WHEN UNDEFINED_TABLE THEN
>         END;
>
>         EXECUTE 'CREATE TABLE vertices_tmp (id serial)';
>
>         --    FOR _r IN EXECUTE 'SELECT srid FROM geometry_columns WHERE
>         f_table_name='''|| quote_ident(geom_table)||''';' LOOP --      srid
>         := _r.srid; --    END LOOP;
>
>         srid :=
>         Find_SRID('public',quote_ident(geom_table),quote_ident(geo_cname));
>
>
>         EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'',
>         '||srid||', ''POINT'', 3)'; CREATE INDEX vertices_tmp_idx ON
>         vertices_tmp USING GIST (the_geom);
>
>         FOR _r IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS
>         id,' ||
>         ' ST_MakePoint(ST_X(StartPoint('|| quote_ident(geo_cname) ||')),
>         ST_Y(StartPoint('|| quote_ident(geo_cname) ||')), '||
>         quote_ident(source_zlevel_cname) ||') AS source,' || '
>         ST_MakePoint(ST_X(EndPoint('|| quote_ident(geo_cname) ||')),
>         ST_Y(EndPoint('|| quote_ident(geo_cname) ||')), '||
>         quote_ident(target_zlevel_cname) ||') AS target' || ' FROM ' ||
>         quote_ident(geom_table) LOOP
>
>         source_id := point_to_id3d(setsrid(_r.source, srid), tolerance);
>         target_id := point_to_id3d(setsrid(_r.target, srid), tolerance);
>
>         EXECUTE 'update ' || quote_ident(geom_table) || ' SET source = ' ||
>         source_id || ', target = ' || target_id || ' WHERE ' ||
>         quote_ident(gid_cname) || ' = ' || _r.id <http://r.id>; END LOOP;
>
>         RETURN 'OK';
>
>         END; $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT COST 100; ALTER
>         FUNCTION assign_vertex_id3d(character varying, double precision,
>         character varying, character varying, character varying, character
>         varying) OWNER TO postgres;
>
>
>         -- Function: point_to_id3d(geometry, double precision)
>
>         -- DROP FUNCTION point_to_id3d(geometry, double precision);
>
>         CREATE OR REPLACE FUNCTION point_to_id3d(p geometry, tolerance
>         double
>         precision) RETURNS bigint AS $BODY$
>
>         DECLARE _r record; _id bigint; _srid integer;
>
>         BEGIN
>
>         _srid := Find_SRID('public','vertices_tmp','the_geom');
>
>         SELECT ST_Length3D(ST_MakeLine(the_geom,p)) AS d, id
>
>         INTO _r FROM vertices_tmp WHERE
>
>         the_geom&&  Expand(p, tolerance) AND
>         ST_Length3D(ST_MakeLine(the_geom,p))<  tolerance
>
>         ORDER BY d LIMIT 1;
>
>         IF FOUND THEN
>
>         _id:= _r.id <http://r.id>;
>
>         ELSE
>
>         INSERT INTO vertices_tmp(the_geom) VALUES (SetSRID(p,_srid));
>         _id:=lastval();
>
>         END IF;
>
>         RETURN _id;
>
>         END; $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT COST 100; ALTER
>         FUNCTION point_to_id3d(geometry, double precision) OWNER TO
>         postgres;
>
>
>         _______________________________________________ Pgrouting-users
>         mailing list Pgrouting-users at lists.osgeo.org
>         <mailto:Pgrouting-users at lists.osgeo.org>
>         http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
>
>     _______________________________________________
>     Pgrouting-users mailing list
>     Pgrouting-users at lists.osgeo.org <mailto:Pgrouting-users at lists.osgeo.org>
>     http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
>
>
>
> --
> Miroslav Novta