[pgrouting-users] assign_vertices_3d source code & docs

[Stephen Woodbridge]

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; 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;
>
> 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
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users