<div dir="ltr"><div>Hi all,</div><div><br></div><div>Thanks you for your advices, the issue is fixed.</div><div>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 <meta http-equiv="content-type" content="text/html; charset=utf-8">highlighted and better.</div>
<div><br></div><div>Regards,</div><div>Miroslav</div><div><br></div><div><br></div><div><div class="gmail_quote">2011/3/29 Stephen Woodbridge <span dir="ltr"><<a href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">The easier way to do this is to liberally add<br>
<br>
RAISE NOTICE 'some text var1=% var2=%', var1, var2;<br>
<br>
to the functions and then these values will get printed as the code is executed.<br>
<br>
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.<br>
<br>
sql := 'select ....';<br>
RAISE NOTICE 'SQL: %', sql<br>
EXECUTE sql;<br>
<br>
-Steve<div><div></div><div class="h5"><br>
<br>
On 3/29/2011 10:22 AM, Charles Galpin wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div class="h5">
I don't know how to get a line number from the function to see where<br>
it's failing so I'd just try each step by hand and see what fails.<br>
<br>
hth<br>
charles<br>
<br>
On Mar 28, 2011, at 6:04 PM, Miroslav Novta wrote:<br>
<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div class="h5">
Hi Steve,<br>
<br>
It has changed table name to the_network and after that to ways. Error<br>
is same all the time. Is there something wrong in function? Or i am<br>
missing something?<br>
<br>
*SQL error:*<br>
<br>
ERROR: column"’the_network’" does not exist<br>
LINE 1: SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid...<br>
<br>
<br>
^<br>
<br>
*In statement:*<br>
SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’,<br>
'F_LEVEL', 'T_LEVEL');<br>
<br>
Function assign_vertex_id3d is bellow<br>
<br>
assign_vertex_id3d "geom_table" character varying, "tolerance" double<br>
precision, "geo_cname" character varying, "gid_cname" character<br>
varying, "source_zlevel_cname" character varying,<br>
"target_zlevel_cname" character varying character varying plpgsql<br>
Definition<br>
1<br>
2<br>
3<br>
4<br>
5<br>
6<br>
7<br>
8<br>
9<br>
10<br>
11<br>
12<br>
13<br>
14<br>
15<br>
16<br>
17<br>
18<br>
19<br>
20<br>
21<br>
22<br>
23<br>
24<br>
25<br>
26<br>
27<br>
28<br>
29<br>
30<br>
31<br>
<br>
<br>
32<br>
33<br>
34<br>
35<br>
36<br>
37<br>
38<br>
39<br>
40<br>
41<br>
42<br>
43<br>
44<br>
45<br>
<br>
DECLARE<br>
_r record;<br>
source_idint;<br>
target_idint;<br>
<br>
<br>
sridinteger;<br>
BEGIN<br>
<br>
BEGIN<br>
<br>
<br>
DROP TABLE vertices_tmp;<br>
EXCEPTION<br>
<br>
<br>
WHEN UNDEFINED_TABLETHEN<br>
END;<br>
<br>
<br>
<br>
EXECUTE 'CREATE TABLE vertices_tmp (id serial)';<br>
<br>
-- FOR _r IN EXECUTE'SELECT srid FROM geometry_columns WHERE f_table_name='''|| quote_ident(geom_table)||''';' LOOP<br>
<br>
<br>
-- srid := _r.srid;<br>
-- END LOOP;<br>
<br>
srid := Find_SRID('public',quote_ident(geom_table),quote_ident(geo_cname));<br>
<br>
<br>
<br>
<br>
EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'',''the_geom'','||srid||',''POINT'', 3)';<br>
<br>
<br>
CREATE INDEX vertices_tmp_idxON vertices_tmpUSING GIST (the_geom);<br>
<br>
<br>
<br></div></div>
FOR _rIN EXECUTE 'SELECT' || quote_ident(gid_cname) ||' AS id,'<div class="im"><br>
<br>
<br>
||' ST_MakePoint(ST_X(StartPoint('|| quote_ident(geo_cname) ||')), ST_Y(StartPoint('|| quote_ident(geo_cname) ||')),'|| quote_ident(source_zlevel_cname) ||') AS source,'<br>
<br>
<br>
||' ST_MakePoint(ST_X(EndPoint('|| quote_ident(geo_cname) ||')), ST_Y(EndPoint('|| quote_ident(geo_cname) ||')),'|| quote_ident(target_zlevel_cname) ||') AS target'<br>
<br>
<br>
||' FROM' || quote_ident(geom_table)<br>
LOOP<br>
<br>
source_id := point_to_id3d(setsrid(_r.source, srid), tolerance);<br>
<br>
<br>
target_id := point_to_id3d(setsrid(_r.target, srid), tolerance);<br>
<br>
EXECUTE 'update' || quote_ident(geom_table) ||<br>
<br>
<br>
' SET source =' || source_id ||<br>
', target =' || target_id ||<br>
<br>
<br></div>
' WHERE' || quote_ident(gid_cname) ||' =' || _<a href="http://r.id" target="_blank">r.id</a> <<a href="http://r.id/" target="_blank">http://r.id/</a>>;<div class="im"><br>
<br>
<br>
END LOOP;<br>
<br>
RETURN 'OK';<br>
<br>
<br>
<br>
END;<br>
<br>
<br>
- Miroslav<br>
<br>
2011/3/25 Stephen Woodbridge <<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a><br></div><div><div></div><div class="h5">
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>>><br>
<br>
network is a postgresql type so it must be quoted. So you can try<br>
doing:<br>
<br>
<br>
SELECT assign_vertex_id3d(’"network"’, 1, ’the_geom’, ’gid’,<br>
'F_LEVEL', 'T_LEVEL');<br>
<br>
This might work, but in general you might have other issues with<br>
other stored procedures also.<br>
<br>
You could also try changing your table name like:<br>
<br>
alter table "network" rename to "the_network";<br>
<br>
then reference it by the_network like:<br>
<br>
SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’,<br>
'F_LEVEL', 'T_LEVEL');<br>
<br>
-Steve<br>
<br>
<br>
On 3/24/2011 6:32 PM, Miroslav Novta wrote:<br>
<br>
Hello Dear,<br>
<br>
I have faced a problem with assign_vertex_id3d. It is a bit<br>
strange<br>
error message i have get. I have explored all code i suspect<br>
and have no<br>
clue. Error report is network is not a column, it is a table<br>
and it<br>
should be a table, i have no idea why it expect it to be a column.<br>
<br>
Here is error i get:<br>
<br>
ERROR: column "’network’" does not exist<br>
LINE 1: SELECT assign_vertex_id3d(’network’, 1, ’the_geom’,<br>
’gid’, '...<br>
^<br>
<br>
In statement:<br>
SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’,<br>
'F_LEVEL',<br>
'T_LEVEL');<br>
<br>
To explain more, network is my ways table defined with iGO<br>
standard. I<br>
have added source and target columns according to guide<br>
<br>
-- Add "source" and "target" column<br>
ALTER TABLE "network" ADD COLUMN "source" integer;<br>
ALTER TABLE "network" ADD COLUMN "target" integer;<br>
<br>
Do you have any idea what is wrong? What have i missed?<br>
<br>
Regards,<br>
Miroslav<br>
<br>
2011/3/23 Stephen Woodbridge <<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a><br>
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>><br></div></div><div><div></div><div class="h5">
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a> <mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>>>><br>
><br>
> On 3/23/2011 10:23 AM, Miroslav Novta wrote:<br>
>><br>
>> Steve,<br>
>><br>
>> Thank you, I will follow your Instructions. I don't expect<br>
a lot of<br>
>> problems with turn restrictions because maneuvers table is<br>
still empty<br>
>> and will be filled after routing become functional.<br>
>> I still have some baby problems to make pgRouting functional.<br>
>><br>
>> For example what is correct range of tolerance if i use<br>
meters not<br>
>> degrees in assign vertex?<br>
><br>
> This depends on your data for example in degrees if you data<br>
has a<br>
resolution of 0.000001 then this is a good value to use.<br>
><br>
> For meters, you might use 0.5 or 0.9, the best way to<br>
evaluate this<br>
is to look the the coordinates at the ends of to segments that<br>
should be<br>
connected. So:<br>
> abs(Xa - Xb) < tolerance<br>
> to match the two ends to the same node. If you set tolerance<br>
too big<br>
then it will merge close by nodes that should not be<br>
connected. If you<br>
set the tolerance too small it will not match nodes that should be<br>
connected. We use tolerance to deal with floating point<br>
numbers not<br>
being exact like integers and can have rounding errors in the<br>
numbers.<br>
><br>
> -Steve<br>
><br>
>> Miroslav<br>
>><br>
>> 2011/3/23 Stephen Woodbridge <<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a><br>
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>><br>
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a> <mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>>><br>
>> <mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a><br>
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>><br>
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a><br>
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>>>>><br>
>><br>
>> Charles,<br>
>><br>
>> Thank you for posting that. I am glad I read the whole thread<br>
>> because I was just about to did up my old code and dust it<br>
off to<br>
>> post. There is no need for that as your implementation<br>
accurately<br>
>> reflects mine.<br>
>><br>
>> Miroslav,<br>
>><br>
>> Regarding turn restrictions, you should search the archives<br>
for this<br>
>> as I have been fairly vocal about the limitations. To<br>
summarize:<br>
>><br>
>> 1. turn restrictions only work with shooting star<br>
>> 2. If you have multiple turn restrictions, I think you have<br>
to enter<br>
>> the related links multiple times, ie: once for each restriction<br>
>> 3. IMHO, we need to rework the turn restrictions to make<br>
adding them<br>
>> more intuitive and easier<br>
>> 4. before you try to implement a large number of them, I would<br>
>> verify that you can in fact add multiple turn restrictions in a<br>
>> trivial graph that is easy to verify if it works as expected,<br>
>> because it is my sense that this area has not been used to<br>
any great<br>
>> extent.<br>
>><br>
>> -Steve<br>
>><br>
>><br>
>> On 3/23/2011 7:43 AM, Charles Galpin wrote:<br>
>><br>
>> Hi Miroslav<br>
>><br>
>> This is an implementation I have been using which I made<br>
after a<br>
>> discussion with Stephen on this subject. It is essentially an<br>
>> implementation of his ideas and works fine for me, but ymmv.<br>
>><br>
>> Otherwise search the email archives as I believe he posted<br>
his here<br>
>> at some point as well.<br>
>><br>
>> hth charles<br>
>><br>
>> On Mar 23, 2011, at 6:44 AM, Miroslav Novta wrote:<br>
>><br>
>> Hello list,<br>
>><br>
>> I want to use the assign_vertices_3d() function developed by<br>
>> Stephen Woodbridge. I want to use it for iGO shp map<br>
format with<br>
>> "turn restrictions" too.<br>
>><br>
>> Is there source code and documentation available on any<br>
public<br>
>> repository I may access?<br>
>><br>
>><br>
>> -- Function: assign_vertex_id3d(character varying, double<br>
precision,<br>
>> character varying, character varying, character varying,<br>
character<br>
>> varying)<br>
>><br>
>> -- DROP FUNCTION assign_vertex_id3d(character varying, double<br>
>> precision, character varying, character varying, character<br>
varying,<br>
>> character varying);<br>
>><br>
>> CREATE OR REPLACE FUNCTION assign_vertex_id3d(geom_table<br>
character<br>
>> varying, tolerance double precision, geo_cname character<br>
varying,<br>
>> gid_cname character varying, source_zlevel_cname character<br>
varying,<br>
>> target_zlevel_cname character varying) RETURNS character<br>
varying AS<br>
>> $BODY$ DECLARE _r record; source_id int; target_id int; srid<br>
>> integer; BEGIN<br>
>><br>
>> BEGIN DROP TABLE vertices_tmp; EXCEPTION WHEN UNDEFINED_TABLE<br>
THEN<br>
>> END;<br>
>><br>
>> EXECUTE 'CREATE TABLE vertices_tmp (id serial)';<br>
>><br>
>> -- FOR _r IN EXECUTE 'SELECT srid FROM geometry_columns WHERE<br>
>> f_table_name='''|| quote_ident(geom_table)||''';' LOOP --<br>
srid<br>
>> := _r.srid; -- END LOOP;<br>
>><br>
>> srid :=<br>
>><br>
Find_SRID('public',quote_ident(geom_table),quote_ident(geo_cname));<br>
>><br>
>><br>
>> EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'',<br>
''the_geom'',<br>
>> '||srid||', ''POINT'', 3)'; CREATE INDEX vertices_tmp_idx ON<br>
>> vertices_tmp USING GIST (the_geom);<br>
>><br>
>> FOR _r IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS<br>
>> id,' ||<br>
>> ' ST_MakePoint(ST_X(StartPoint('|| quote_ident(geo_cname)<br>
||')),<br>
>> ST_Y(StartPoint('|| quote_ident(geo_cname) ||')), '||<br>
>> quote_ident(source_zlevel_cname) ||') AS source,' || '<br>
>> ST_MakePoint(ST_X(EndPoint('|| quote_ident(geo_cname) ||')),<br>
>> ST_Y(EndPoint('|| quote_ident(geo_cname) ||')), '||<br>
>> quote_ident(target_zlevel_cname) ||') AS target' || ' FROM ' ||<br>
>> quote_ident(geom_table) LOOP<br>
>><br>
>> source_id := point_to_id3d(setsrid(_r.source, srid),<br>
tolerance);<br>
>> target_id := point_to_id3d(setsrid(_r.target, srid),<br>
tolerance);<br>
>><br>
>> EXECUTE 'update ' || quote_ident(geom_table) || ' SET source<br>
= ' ||<br>
>> source_id || ', target = ' || target_id || ' WHERE ' ||<br>
>> quote_ident(gid_cname) || ' = ' || _<a href="http://r.id" target="_blank">r.id</a> <<a href="http://r.id/" target="_blank">http://r.id/</a>><br></div></div>
<<a href="http://r.id" target="_blank">http://r.id</a> <<a href="http://r.id/" target="_blank">http://r.id/</a>>><br>
<<a href="http://r.id" target="_blank">http://r.id</a> <<a href="http://r.id/" target="_blank">http://r.id/</a>>>; END LOOP;<div class="im"><br>
>><br>
>> RETURN 'OK';<br>
>><br>
>> END; $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT COST 100; ALTER<br>
>> FUNCTION assign_vertex_id3d(character varying, double<br>
precision,<br>
>> character varying, character varying, character varying,<br>
character<br>
>> varying) OWNER TO postgres;<br>
>><br>
>><br>
>> -- Function: point_to_id3d(geometry, double precision)<br>
>><br>
>> -- DROP FUNCTION point_to_id3d(geometry, double precision);<br>
>><br>
>> CREATE OR REPLACE FUNCTION point_to_id3d(p geometry, tolerance<br>
>> double<br>
>> precision) RETURNS bigint AS $BODY$<br>
>><br>
>> DECLARE _r record; _id bigint; _srid integer;<br>
>><br>
>> BEGIN<br>
>><br>
>> _srid := Find_SRID('public','vertices_tmp','the_geom');<br>
>><br>
>> SELECT ST_Length3D(ST_MakeLine(the_geom,p)) AS d, id<br>
>><br>
>> INTO _r FROM vertices_tmp WHERE<br>
>><br>
>> the_geom&& Expand(p, tolerance) AND<br>
>> ST_Length3D(ST_MakeLine(the_geom,p))< tolerance<br>
>><br>
>> ORDER BY d LIMIT 1;<br>
>><br>
>> IF FOUND THEN<br>
>><br></div>
>> _id:= _<a href="http://r.id" target="_blank">r.id</a> <<a href="http://r.id/" target="_blank">http://r.id/</a>> <<a href="http://r.id" target="_blank">http://r.id</a> <<a href="http://r.id/" target="_blank">http://r.id/</a>>><br>
<<a href="http://r.id" target="_blank">http://r.id</a> <<a href="http://r.id/" target="_blank">http://r.id/</a>>>;<div><div></div><div class="h5"><br>
<br>
>><br>
>> ELSE<br>
>><br>
>> INSERT INTO vertices_tmp(the_geom) VALUES (SetSRID(p,_srid));<br>
>> _id:=lastval();<br>
>><br>
>> END IF;<br>
>><br>
>> RETURN _id;<br>
>><br>
>> END; $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT COST 100; ALTER<br>
>> FUNCTION point_to_id3d(geometry, double precision) OWNER TO<br>
>> postgres;<br>
>><br>
>><br>
>> _______________________________________________ Pgrouting-users<br>
>> mailing list <a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>>><br>
>> <mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>>>><br>
>> <a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
>><br>
>><br>
>> _______________________________________________<br>
>> Pgrouting-users mailing list<br>
>> <a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>>><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>>>><br>
>> <a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
>><br>
>><br>
>><br>
>><br>
>> --<br>
>> Miroslav Novta<br>
><br>
<br>
<br>
<br>
--<br>
Miroslav Novta<br>
<br>
<br>
<br>
<br>
<br>
--<br>
Miroslav Novta<br>
_______________________________________________<br>
Pgrouting-users mailing list<br>
</div></div><a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a> <mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a>><div class="im">
<br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
</div></blockquote>
<br>
</blockquote><div><div></div><div class="h5">
<br>
_______________________________________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Miroslav Novta<br>
</div></div>