[pgrouting-users] assign_vertices_3d source code & docs

[Charles Galpin]

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;