[pgrouting-users] assign_vertices_3d source code & docs

[Miroslav Novta]

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?

Miroslav

2011/3/23 Stephen Woodbridge <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; 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
>>
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>



-- 
Miroslav Novta
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110323/ce872292/attachment-0001.html