[pgrouting-users] assign_vertices_3d source code & docs
Charles Galpin
cgalpin at lhsw.com
Tue Mar 29 10:22:48 EDT 2011
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;
>
>
> 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
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110329/de14d04b/attachment-0001.html
More information about the Pgrouting-users
mailing list