[pgrouting-users] assign_vertices_3d source code & docs
Stephen Woodbridge
woodbri at swoodbridge.com
Tue Mar 29 11:29:42 EDT 2011
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 at swoodbridge.com
>> <mailto:woodbri at 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 at swoodbridge.com
>> <mailto:woodbri at swoodbridge.com>
>> <mailto:woodbri at swoodbridge.com <mailto:woodbri at 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 at swoodbridge.com
>> <mailto:woodbri at swoodbridge.com>
>> <mailto:woodbri at swoodbridge.com <mailto:woodbri at swoodbridge.com>>
>> >> <mailto:woodbri at swoodbridge.com
>> <mailto:woodbri at swoodbridge.com>
>> <mailto:woodbri at swoodbridge.com
>> <mailto:woodbri at swoodbridge.com>>>>
>> >>
>> >> Charles,
>> >>
>> >> Thank you for posting that. I am glad I read the whole thread
>> >> because I was just about to did up my old code and dust it
>> off to
>> >> post. There is no need for that as your implementation
>> accurately
>> >> reflects mine.
>> >>
>> >> Miroslav,
>> >>
>> >> Regarding turn restrictions, you should search the archives
>> for this
>> >> as I have been fairly vocal about the limitations. To
>> summarize:
>> >>
>> >> 1. turn restrictions only work with shooting star
>> >> 2. If you have multiple turn restrictions, I think you have
>> to enter
>> >> the related links multiple times, ie: once for each restriction
>> >> 3. IMHO, we need to rework the turn restrictions to make
>> adding them
>> >> more intuitive and easier
>> >> 4. before you try to implement a large number of them, I would
>> >> verify that you can in fact add multiple turn restrictions in a
>> >> trivial graph that is easy to verify if it works as expected,
>> >> because it is my sense that this area has not been used to
>> any great
>> >> extent.
>> >>
>> >> -Steve
>> >>
>> >>
>> >> On 3/23/2011 7:43 AM, Charles Galpin wrote:
>> >>
>> >> Hi Miroslav
>> >>
>> >> This is an implementation I have been using which I made
>> after a
>> >> discussion with Stephen on this subject. It is essentially an
>> >> implementation of his ideas and works fine for me, but ymmv.
>> >>
>> >> Otherwise search the email archives as I believe he posted
>> his here
>> >> at some point as well.
>> >>
>> >> hth charles
>> >>
>> >> On Mar 23, 2011, at 6:44 AM, Miroslav Novta wrote:
>> >>
>> >> Hello list,
>> >>
>> >> I want to use the assign_vertices_3d() function developed by
>> >> Stephen Woodbridge. I want to use it for iGO shp map
>> format with
>> >> "turn restrictions" too.
>> >>
>> >> Is there source code and documentation available on any
>> public
>> >> repository I may access?
>> >>
>> >>
>> >> -- Function: assign_vertex_id3d(character varying, double
>> precision,
>> >> character varying, character varying, character varying,
>> character
>> >> varying)
>> >>
>> >> -- DROP FUNCTION assign_vertex_id3d(character varying, double
>> >> precision, character varying, character varying, character
>> varying,
>> >> character varying);
>> >>
>> >> CREATE OR REPLACE FUNCTION assign_vertex_id3d(geom_table
>> character
>> >> varying, tolerance double precision, geo_cname character
>> varying,
>> >> gid_cname character varying, source_zlevel_cname character
>> varying,
>> >> target_zlevel_cname character varying) RETURNS character
>> varying AS
>> >> $BODY$ DECLARE _r record; source_id int; target_id int; srid
>> >> integer; BEGIN
>> >>
>> >> BEGIN DROP TABLE vertices_tmp; EXCEPTION WHEN UNDEFINED_TABLE
>> THEN
>> >> END;
>> >>
>> >> EXECUTE 'CREATE TABLE vertices_tmp (id serial)';
>> >>
>> >> -- FOR _r IN EXECUTE 'SELECT srid FROM geometry_columns WHERE
>> >> f_table_name='''|| quote_ident(geom_table)||''';' LOOP --
>> srid
>> >> := _r.srid; -- END LOOP;
>> >>
>> >> srid :=
>> >>
>> Find_SRID('public',quote_ident(geom_table),quote_ident(geo_cname));
>> >>
>> >>
>> >> EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'',
>> ''the_geom'',
>> >> '||srid||', ''POINT'', 3)'; CREATE INDEX vertices_tmp_idx ON
>> >> vertices_tmp USING GIST (the_geom);
>> >>
>> >> FOR _r IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS
>> >> id,' ||
>> >> ' ST_MakePoint(ST_X(StartPoint('|| quote_ident(geo_cname)
>> ||')),
>> >> ST_Y(StartPoint('|| quote_ident(geo_cname) ||')), '||
>> >> quote_ident(source_zlevel_cname) ||') AS source,' || '
>> >> ST_MakePoint(ST_X(EndPoint('|| quote_ident(geo_cname) ||')),
>> >> ST_Y(EndPoint('|| quote_ident(geo_cname) ||')), '||
>> >> quote_ident(target_zlevel_cname) ||') AS target' || ' FROM ' ||
>> >> quote_ident(geom_table) LOOP
>> >>
>> >> source_id := point_to_id3d(setsrid(_r.source, srid),
>> tolerance);
>> >> target_id := point_to_id3d(setsrid(_r.target, srid),
>> tolerance);
>> >>
>> >> EXECUTE 'update ' || quote_ident(geom_table) || ' SET source
>> = ' ||
>> >> source_id || ', target = ' || target_id || ' WHERE ' ||
>> >> quote_ident(gid_cname) || ' = ' || _r.id <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 at lists.osgeo.org
>> <mailto:Pgrouting-users at lists.osgeo.org>
>> <mailto:Pgrouting-users at lists.osgeo.org
>> <mailto:Pgrouting-users at lists.osgeo.org>>
>> >> <mailto:Pgrouting-users at lists.osgeo.org
>> <mailto:Pgrouting-users at lists.osgeo.org>
>> <mailto:Pgrouting-users at lists.osgeo.org
>> <mailto:Pgrouting-users at lists.osgeo.org>>>
>> >> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>> >>
>> >>
>> >> _______________________________________________
>> >> Pgrouting-users mailing list
>> >> Pgrouting-users at lists.osgeo.org
>> <mailto:Pgrouting-users at lists.osgeo.org>
>> <mailto:Pgrouting-users at lists.osgeo.org
>> <mailto:Pgrouting-users at lists.osgeo.org>>
>> <mailto:Pgrouting-users at lists.osgeo.org
>> <mailto:Pgrouting-users at lists.osgeo.org>
>> <mailto:Pgrouting-users at lists.osgeo.org
>> <mailto:Pgrouting-users at lists.osgeo.org>>>
>> >> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>> >>
>> >>
>> >>
>> >>
>> >> --
>> >> Miroslav Novta
>> >
>>
>>
>>
>> --
>> Miroslav Novta
>>
>>
>>
>>
>>
>> --
>> Miroslav Novta
>> _______________________________________________
>> Pgrouting-users mailing list
>> Pgrouting-users at lists.osgeo.org <mailto:Pgrouting-users at lists.osgeo.org>
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
More information about the Pgrouting-users
mailing list