[pgrouting-users] assign_vertices_3d source code & docs
Miroslav Novta
miroslav.novta at gmail.com
Mon Mar 28 18:04:08 EDT 2011
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;
END LOOP;
RETURN 'OK';
END;
- Miroslav
2011/3/25 Stephen Woodbridge <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>>
>> >
>> > 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>>>
>> >>
>> >> 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 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>>
>> >> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>> >>
>> >>
>> >>
>> >>
>> >> --
>> >> Miroslav Novta
>> >
>>
>>
>>
>> --
>> Miroslav Novta
>>
>
>
--
Miroslav Novta
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110329/864edae8/attachment-0001.html
More information about the Pgrouting-users
mailing list