[pgrouting-users] assign_vertices_3d source code & docs
Miroslav Novta
miroslav.novta at gmail.com
Tue Apr 12 18:01:28 EDT 2011
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 at 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 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
>>>
>>
>>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
--
Miroslav Novta
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110413/9041dc8e/attachment-0001.html
More information about the Pgrouting-users
mailing list