[pgrouting-users] assign_vertices_3d source code & docs

Miroslav_Novta · March 23, 2011, 10:44am

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?

Best regards,
Miroslav Novta

Charles_Galpin · March 23, 2011, 11:43am

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;

Miroslav_Novta · March 23, 2011, 1:48pm

Hi Charles,

Thank you for code.

Regards,
Miroslav

2011/3/23 Charles Galpin <cgalpin@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Miroslav Novta

Stephen_Woodbridge1 · March 23, 2011, 2:10pm

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

Miroslav_Novta · March 23, 2011, 2:23pm

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

turn restrictions only work with shooting star

If you have multiple turn restrictions, I think you have to enter the related links multiple times, ie: once for each restriction

IMHO, we need to rework the turn restrictions to make adding them more intuitive and easier

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

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Miroslav Novta

dkastl · March 23, 2011, 2:29pm

2011/3/23 Miroslav Novta <miroslav.novta@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@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:

turn restrictions only work with shooting star

If you have multiple turn restrictions, I think you have to enter the related links multiple times, ie: once for each restriction

IMHO, we need to rework the turn restrictions to make adding them more intuitive and easier

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

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Miroslav Novta

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

Stephen_Woodbridge1 · March 23, 2011, 3:49pm

On 3/23/2011 10:23 AM, Miroslav Novta wrote:

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?

This depends on your data for example in degrees if you data has a resolution of 0.000001 then this is a good value to use.

For meters, you might use 0.5 or 0.9, the best way to evaluate this is to look the the coordinates at the ends of to segments that should be connected. So:
abs(Xa - Xb) < tolerance
to match the two ends to the same node. If you set tolerance too big then it will merge close by nodes that should not be connected. If you set the tolerance too small it will not match nodes that should be connected. We use tolerance to deal with floating point numbers not being exact like integers and can have rounding errors in the numbers.

-Steve

Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
<mailto:woodbri@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 <http://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 <http://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@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>
        http://lists.osgeo.org/mailman/listinfo/pgrouting-users

    _______________________________________________
    Pgrouting-users mailing list
    Pgrouting-users@lists.osgeo.org <mailto:Pgrouting-users@lists.osgeo.org>
    http://lists.osgeo.org/mailman/listinfo/pgrouting-users

--
Miroslav Novta

Miroslav_Novta · March 24, 2011, 10:32pm

Hello Dear,

I have faced a problem with assign_vertex_id3d. It is a bit strange error message i have get. I have explored all code i suspect and have no clue. Error report is network is not a column, it is a table and it should be a table, i have no idea why it expect it to be a column.

Here is error i get:

ERROR: column “’network’” does not exist
LINE 1: SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, '…
^

In statement:
SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’, ‘T_LEVEL’);

To explain more, network is my ways table defined with iGO standard. I have added source and target columns according to guide

– Add “source” and “target” column
ALTER TABLE “network” ADD COLUMN “source” integer;
ALTER TABLE “network” ADD COLUMN “target” integer;

Do you have any idea what is wrong? What have i missed?

Regards,
Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com>

On 3/23/2011 10:23 AM, Miroslav Novta wrote:

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?

This depends on your data for example in degrees if you data has a resolution of 0.000001 then this is a good value to use.

For meters, you might use 0.5 or 0.9, the best way to evaluate this is to look the the coordinates at the ends of to segments that should be connected. So:
abs(Xa - Xb) < tolerance
to match the two ends to the same node. If you set tolerance too big then it will merge close by nodes that should not be connected. If you set the tolerance too small it will not match nodes that should be connected. We use tolerance to deal with floating point numbers not being exact like integers and can have rounding errors in the numbers.

-Steve

Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
mailto:[woodbri@swoodbridge.com](mailto:woodbri@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:

turn restrictions only work with shooting star

If you have multiple turn restrictions, I think you have to enter
the related links multiple times, ie: once for each restriction

IMHO, we need to rework the turn restrictions to make adding them
more intuitive and easier

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 <http://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 <http://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@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Miroslav Novta

–
Miroslav Novta

iGO_internal_format_SHP.pdf (116 KB)

Stephen_Woodbridge1 · March 25, 2011, 12:04am

network is a postgresql type so it must be quoted. So you can try doing:

SELECT assign_vertex_id3d(’"network"’, 1, ’the_geom’, ’gid’, 'F_LEVEL', 'T_LEVEL');

This might work, but in general you might have other issues with other stored procedures also.

You could also try changing your table name like:

alter table "network" rename to "the_network";

then reference it by the_network like:

SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’, 'F_LEVEL', 'T_LEVEL');

-Steve

On 3/24/2011 6:32 PM, Miroslav Novta wrote:

Hello Dear,

I have faced a problem with assign_vertex_id3d. It is a bit strange
error message i have get. I have explored all code i suspect and have no
clue. Error report is network is not a column, it is a table and it
should be a table, i have no idea why it expect it to be a column.

Here is error i get:

ERROR: column "’network’" does not exist
LINE 1: SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, '...
                                  ^

In statement:
SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, 'F_LEVEL',
'T_LEVEL');

To explain more, network is my ways table defined with iGO standard. I
have added source and target columns according to guide

-- Add "source" and "target" column
ALTER TABLE "network" ADD COLUMN "source" integer;
ALTER TABLE "network" ADD COLUMN "target" integer;

Do you have any idea what is wrong? What have i missed?

Regards,
Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
<mailto:woodbri@swoodbridge.com>>
>
> On 3/23/2011 10:23 AM, Miroslav Novta wrote:
>>
>> 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?
>
> This depends on your data for example in degrees if you data has a
resolution of 0.000001 then this is a good value to use.
>
> For meters, you might use 0.5 or 0.9, the best way to evaluate this
is to look the the coordinates at the ends of to segments that should be
connected. So:
> abs(Xa - Xb) < tolerance
> to match the two ends to the same node. If you set tolerance too big
then it will merge close by nodes that should not be connected. If you
set the tolerance too small it will not match nodes that should be
connected. We use tolerance to deal with floating point numbers not
being exact like integers and can have rounding errors in the numbers.
>
> -Steve
>
>> Miroslav
>>
>> 2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
<mailto:woodbri@swoodbridge.com>
>> <mailto:woodbri@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 <http://r.id>
<http://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 <http://r.id> <http://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@lists.osgeo.org
<mailto:Pgrouting-users@lists.osgeo.org>
>> <mailto:Pgrouting-users@lists.osgeo.org
<mailto:Pgrouting-users@lists.osgeo.org>>
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>
>> _______________________________________________
>> Pgrouting-users mailing list
>> Pgrouting-users@lists.osgeo.org
<mailto:Pgrouting-users@lists.osgeo.org>
<mailto:Pgrouting-users@lists.osgeo.org
<mailto:Pgrouting-users@lists.osgeo.org>>
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>
>> --
>> Miroslav Novta
>

--
Miroslav Novta

Miroslav_Novta · March 28, 2011, 10:04pm

Hi Steve,

It has changed table name to the_network and after that to ways. Error is same all the time. Is there something wrong in function? Or i am missing something?

SQL error:

ERROR:  column "’the_network’" does not exist
LINE 1: SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid...

                                  ^

In statement:
SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’, ‘T_LEVEL’);

Function assign_vertex_id3d is bellow

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 character varying plpgsql
Definition

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](http://r.id);

   END LOOP;

   RETURN 'OK';

END;

Miroslav

2011/3/25 Stephen Woodbridge <woodbri@swoodbridge.com>

network is a postgresql type so it must be quoted. So you can try doing:

SELECT assign_vertex_id3d(’“network”’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’, ‘T_LEVEL’);

This might work, but in general you might have other issues with other stored procedures also.

You could also try changing your table name like:

alter table “network” rename to “the_network”;

then reference it by the_network like:

SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’, ‘T_LEVEL’);

-Steve

On 3/24/2011 6:32 PM, Miroslav Novta wrote:

Hello Dear,

I have faced a problem with assign_vertex_id3d. It is a bit strange
error message i have get. I have explored all code i suspect and have no
clue. Error report is network is not a column, it is a table and it
should be a table, i have no idea why it expect it to be a column.

Here is error i get:

ERROR: column “’network’” does not exist
LINE 1: SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, '…
^

In statement:
SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’,
‘T_LEVEL’);

To explain more, network is my ways table defined with iGO standard. I
have added source and target columns according to guide

– Add “source” and “target” column
ALTER TABLE “network” ADD COLUMN “source” integer;
ALTER TABLE “network” ADD COLUMN “target” integer;

Do you have any idea what is wrong? What have i missed?

Regards,
Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com

mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)>

On 3/23/2011 10:23 AM, Miroslav Novta wrote:

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?

This depends on your data for example in degrees if you data has a
resolution of 0.000001 then this is a good value to use.

For meters, you might use 0.5 or 0.9, the best way to evaluate this
is to look the the coordinates at the ends of to segments that should be
connected. So:
abs(Xa - Xb) < tolerance
to match the two ends to the same node. If you set tolerance too big
then it will merge close by nodes that should not be connected. If you
set the tolerance too small it will not match nodes that should be
connected. We use tolerance to deal with floating point numbers not
being exact like integers and can have rounding errors in the numbers.

-Steve

Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)

<mailto:woodbri@swoodbridge.com mailto:[woodbri@swoodbridge.com](mailto:woodbri@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:

turn restrictions only work with shooting star

If you have multiple turn restrictions, I think you have to enter
the related links multiple times, ie: once for each restriction

IMHO, we need to rework the turn restrictions to make adding them
more intuitive and easier

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 <http://r.id>
<http://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 <http://r.id> <http://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@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Miroslav Novta

–
Miroslav Novta

–
Miroslav Novta

Charles_Galpin · March 29, 2011, 2:22pm

I don’t know how to get a line number from the function to see where it’s failing so I’d just try each step by hand and see what fails.

hth
charles

On Mar 28, 2011, at 6:04 PM, Miroslav Novta wrote:

Hi Steve,

It has changed table name to the_network and after that to ways. Error is same all the time. Is there something wrong in function? Or i am missing something?

SQL error:
ERROR:  column "’the_network’" does not exist
LINE 1: SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid...

                                  ^
In statement:
SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’, ‘T_LEVEL’);

Function assign_vertex_id3d is bellow

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 character varying plpgsql
Definition
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

32
33
34
35
36
37
38
39
40
41
42
43
44
45
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](http://r.id/);

   END LOOP;

   RETURN 'OK';

END;
Miroslav

2011/3/25 Stephen Woodbridge <woodbri@swoodbridge.com>

network is a postgresql type so it must be quoted. So you can try doing:

SELECT assign_vertex_id3d(’“network”’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’, ‘T_LEVEL’);

This might work, but in general you might have other issues with other stored procedures also.

You could also try changing your table name like:

alter table “network” rename to “the_network”;

then reference it by the_network like:

SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’, ‘T_LEVEL’);

-Steve

On 3/24/2011 6:32 PM, Miroslav Novta wrote:

Hello Dear,

I have faced a problem with assign_vertex_id3d. It is a bit strange
error message i have get. I have explored all code i suspect and have no
clue. Error report is network is not a column, it is a table and it
should be a table, i have no idea why it expect it to be a column.

Here is error i get:

ERROR: column “’network’” does not exist
LINE 1: SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, '…
^

In statement:
SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, ‘F_LEVEL’,
‘T_LEVEL’);

To explain more, network is my ways table defined with iGO standard. I
have added source and target columns according to guide

– Add “source” and “target” column
ALTER TABLE “network” ADD COLUMN “source” integer;
ALTER TABLE “network” ADD COLUMN “target” integer;

Do you have any idea what is wrong? What have i missed?

Regards,
Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com

mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)>

On 3/23/2011 10:23 AM, Miroslav Novta wrote:

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?

This depends on your data for example in degrees if you data has a
resolution of 0.000001 then this is a good value to use.

For meters, you might use 0.5 or 0.9, the best way to evaluate this
is to look the the coordinates at the ends of to segments that should be
connected. So:
abs(Xa - Xb) < tolerance
to match the two ends to the same node. If you set tolerance too big
then it will merge close by nodes that should not be connected. If you
set the tolerance too small it will not match nodes that should be
connected. We use tolerance to deal with floating point numbers not
being exact like integers and can have rounding errors in the numbers.

-Steve

Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)

<mailto:woodbri@swoodbridge.com mailto:[woodbri@swoodbridge.com](mailto:woodbri@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:

turn restrictions only work with shooting star

If you have multiple turn restrictions, I think you have to enter
the related links multiple times, ie: once for each restriction

IMHO, we need to rework the turn restrictions to make adding them
more intuitive and easier

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 <http://r.id>
<http://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 <http://r.id> <http://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@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Miroslav Novta

–
Miroslav Novta

–
Miroslav Novta

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Stephen_Woodbridge1 · March 29, 2011, 3:29pm

The easier way to do this is to liberally add

RAISE NOTICE 'some text var1=% var2=%', var1, var2;

to the functions and then these values will get printed as the code is executed.

If the function compute some SQL that then gets executed then print out the SQL and you can then execute the same SQL in psql.

sql := 'select ....';
RAISE NOTICE 'SQL: %', sql
EXECUTE sql;

-Steve

On 3/29/2011 10:22 AM, Charles Galpin wrote:

I don't know how to get a line number from the function to see where
it's failing so I'd just try each step by hand and see what fails.

hth
charles

On Mar 28, 2011, at 6:04 PM, Miroslav Novta wrote:

Hi Steve,

It has changed table name to the_network and after that to ways. Error
is same all the time. Is there something wrong in function? Or i am
missing something?

*SQL error:*

ERROR: column"’the_network’" does not exist
LINE 1: SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid...

                                   ^

*In statement:*
SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’,
'F_LEVEL', 'T_LEVEL');

Function assign_vertex_id3d is bellow

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 character varying plpgsql
Definition
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

32
33
34
35
36
37
38
39
40
41
42
43
44
45

DECLARE
    _r record;
    source_idint;
    target_idint;

    sridinteger;
BEGIN

    BEGIN

    DROP TABLE vertices_tmp;
    EXCEPTION

    WHEN UNDEFINED_TABLETHEN
    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_idxON vertices_tmpUSING GIST (the_geom);

    FOR _rIN 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 <http://r.id/>;

    END LOOP;

    RETURN 'OK';

END;

- Miroslav

2011/3/25 Stephen Woodbridge <woodbri@swoodbridge.com
<mailto:woodbri@swoodbridge.com>>

    network is a postgresql type so it must be quoted. So you can try
    doing:

    SELECT assign_vertex_id3d(’"network"’, 1, ’the_geom’, ’gid’,
    'F_LEVEL', 'T_LEVEL');

    This might work, but in general you might have other issues with
    other stored procedures also.

    You could also try changing your table name like:

    alter table "network" rename to "the_network";

    then reference it by the_network like:

    SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’,
    'F_LEVEL', 'T_LEVEL');

    -Steve

    On 3/24/2011 6:32 PM, Miroslav Novta wrote:

        Hello Dear,

        I have faced a problem with assign_vertex_id3d. It is a bit
        strange
        error message i have get. I have explored all code i suspect
        and have no
        clue. Error report is network is not a column, it is a table
        and it
        should be a table, i have no idea why it expect it to be a column.

        Here is error i get:

        ERROR: column "’network’" does not exist
        LINE 1: SELECT assign_vertex_id3d(’network’, 1, ’the_geom’,
        ’gid’, '...
        ^

        In statement:
        SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’,
        'F_LEVEL',
        'T_LEVEL');

        To explain more, network is my ways table defined with iGO
        standard. I
        have added source and target columns according to guide

        -- Add "source" and "target" column
        ALTER TABLE "network" ADD COLUMN "source" integer;
        ALTER TABLE "network" ADD COLUMN "target" integer;

        Do you have any idea what is wrong? What have i missed?

        Regards,
        Miroslav

        2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
        <mailto:woodbri@swoodbridge.com>
        <mailto:woodbri@swoodbridge.com>>
        >
        > On 3/23/2011 10:23 AM, Miroslav Novta wrote:
        >>
        >> 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?
        >
        > This depends on your data for example in degrees if you data
        has a
        resolution of 0.000001 then this is a good value to use.
        >
        > For meters, you might use 0.5 or 0.9, the best way to
        evaluate this
        is to look the the coordinates at the ends of to segments that
        should be
        connected. So:
        > abs(Xa - Xb) < tolerance
        > to match the two ends to the same node. If you set tolerance
        too big
        then it will merge close by nodes that should not be
        connected. If you
        set the tolerance too small it will not match nodes that should be
        connected. We use tolerance to deal with floating point
        numbers not
        being exact like integers and can have rounding errors in the
        numbers.
        >
        > -Steve
        >
        >> Miroslav
        >>
        >> 2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
        <mailto:woodbri@swoodbridge.com>
        <mailto:woodbri@swoodbridge.com>
        >> <mailto:woodbri@swoodbridge.com
        <mailto:woodbri@swoodbridge.com>
        <mailto:woodbri@swoodbridge.com
        <mailto:woodbri@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 <http://r.id/>
        <http://r.id/>>
        <http://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 <http://r.id/> <http://r.id/>>
        <http://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@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>
        <mailto:Pgrouting-users@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>>
        >> <mailto:Pgrouting-users@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>
        <mailto:Pgrouting-users@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>>>
        >> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
        >>
        >> _______________________________________________
        >> Pgrouting-users mailing list
        >> Pgrouting-users@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>
        <mailto:Pgrouting-users@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>>
        <mailto:Pgrouting-users@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>
        <mailto:Pgrouting-users@lists.osgeo.org
        <mailto:Pgrouting-users@lists.osgeo.org>>>
        >> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
        >>
        >> --
        >> Miroslav Novta
        >

        --
        Miroslav Novta

--
Miroslav Novta
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org <mailto:Pgrouting-users@lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Miroslav_Novta · April 12, 2011, 10:01pm

Hi all,

Thanks you for your advices, the issue is fixed.
It was just a type of apostrophe, copy/paste from html howto guide gives wrong type of apostrophe. I have missed it because I’ve used phpPgAdmin without syntax highlight. After I start using pgAdmin III everything was highlighted and better.

Regards,
Miroslav

2011/3/29 Stephen Woodbridge <woodbri@swoodbridge.com>

The easier way to do this is to liberally add

RAISE NOTICE ‘some text var1=% var2=%’, var1, var2;

to the functions and then these values will get printed as the code is executed.

If the function compute some SQL that then gets executed then print out the SQL and you can then execute the same SQL in psql.

sql := ‘select …’;
RAISE NOTICE ‘SQL: %’, sql
EXECUTE sql;

-Steve

On 3/29/2011 10:22 AM, Charles Galpin wrote:

I don’t know how to get a line number from the function to see where
it’s failing so I’d just try each step by hand and see what fails.

hth
charles

On Mar 28, 2011, at 6:04 PM, Miroslav Novta wrote:

Hi Steve,

It has changed table name to the_network and after that to ways. Error
is same all the time. Is there something wrong in function? Or i am
missing something?

SQL error:

ERROR: column"’the_network’" does not exist
LINE 1: SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid…

^

In statement:
SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’,
‘F_LEVEL’, ‘T_LEVEL’);

Function assign_vertex_id3d is bellow

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 character varying plpgsql
Definition
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

32
33
34
35
36
37
38
39
40
41
42
43
44
45

DECLARE
_r record;
source_idint;
target_idint;

sridinteger;
BEGIN

BEGIN

DROP TABLE vertices_tmp;
EXCEPTION

WHEN UNDEFINED_TABLETHEN
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_idxON vertices_tmpUSING GIST (the_geom);

FOR _rIN 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 <http://r.id/>;

END LOOP;

RETURN ‘OK’;

END;

Miroslav

2011/3/25 Stephen Woodbridge <woodbri@swoodbridge.com

mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)>

network is a postgresql type so it must be quoted. So you can try
doing:

SELECT assign_vertex_id3d(’“network”’, 1, ’the_geom’, ’gid’,
‘F_LEVEL’, ‘T_LEVEL’);

This might work, but in general you might have other issues with
other stored procedures also.

You could also try changing your table name like:

alter table “network” rename to “the_network”;

then reference it by the_network like:

SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’,
‘F_LEVEL’, ‘T_LEVEL’);

-Steve

On 3/24/2011 6:32 PM, Miroslav Novta wrote:

Hello Dear,

I have faced a problem with assign_vertex_id3d. It is a bit
strange
error message i have get. I have explored all code i suspect
and have no
clue. Error report is network is not a column, it is a table
and it
should be a table, i have no idea why it expect it to be a column.

Here is error i get:

ERROR: column “’network’” does not exist
LINE 1: SELECT assign_vertex_id3d(’network’, 1, ’the_geom’,
’gid’, '…
^

In statement:
SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’,
‘F_LEVEL’,
‘T_LEVEL’);

To explain more, network is my ways table defined with iGO
standard. I
have added source and target columns according to guide

– Add “source” and “target” column
ALTER TABLE “network” ADD COLUMN “source” integer;
ALTER TABLE “network” ADD COLUMN “target” integer;

Do you have any idea what is wrong? What have i missed?

Regards,
Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)

<mailto:woodbri@swoodbridge.com mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)>>

On 3/23/2011 10:23 AM, Miroslav Novta wrote:

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?

This depends on your data for example in degrees if you data
has a
resolution of 0.000001 then this is a good value to use.

For meters, you might use 0.5 or 0.9, the best way to
evaluate this
is to look the the coordinates at the ends of to segments that
should be
connected. So:
abs(Xa - Xb) < tolerance
to match the two ends to the same node. If you set tolerance
too big
then it will merge close by nodes that should not be
connected. If you
set the tolerance too small it will not match nodes that should be
connected. We use tolerance to deal with floating point
numbers not
being exact like integers and can have rounding errors in the
numbers.

-Steve

Miroslav

2011/3/23 Stephen Woodbridge <woodbri@swoodbridge.com
mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)
<mailto:woodbri@swoodbridge.com mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)>
<mailto:woodbri@swoodbridge.com
mailto:[woodbri@swoodbridge.com](mailto:woodbri@swoodbridge.com)
<mailto:woodbri@swoodbridge.com
mailto:[woodbri@swoodbridge.com](mailto:woodbri@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:

turn restrictions only work with shooting star

If you have multiple turn restrictions, I think you have
to enter
the related links multiple times, ie: once for each restriction

IMHO, we need to rework the turn restrictions to make
adding them
more intuitive and easier

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 <http://r.id/>

<http://r.id <http://r.id/>>
<http://r.id <http://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 <http://r.id/> <http://r.id <http://r.id/>>
<http://r.id <http://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@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)>
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)>>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)>
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
<mailto:Pgrouting-users@lists.osgeo.org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)>>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Miroslav Novta

–
Miroslav Novta

–
Miroslav Novta

Pgrouting-users mailing list

Pgrouting-users@lists.osgeo.org mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)

http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Miroslav Novta