[pgrouting-users] assign_vertices_3d source code & docs

[Daniel Kastl]

2011/3/23 Miroslav Novta <miroslav.novta at gmail.com>

> 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?
>

You need to look at the unit of your data projection.
If your data is without errors, then you can keep the tolerance very small,
just enough to snap vertices, that should be connected. It depends on your
data.

Daniel






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


-- 
Georepublic UG & Georepublic Japan
eMail: daniel.kastl at georepublic.de
Web: http://georepublic.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110323/86a79d4d/attachment.html