[pgrouting-users] assign_vertices_3d source code & docs

[Miroslav Novta]

Hi Charles,

Thank you for code.

Regards,
Miroslav

2011/3/23 Charles Galpin <cgalpin at lhsw.com>

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



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