<div dir="ltr">Hi Steve,<br><br>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?<br><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: 'Times New Roman'; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; font-size: medium;"><span class="Apple-style-span" style="font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: 13px;"><p>
<b>SQL error:</b><br></p><pre class="data" style="font-size: 13px; font-family: arial,tahoma,verdana,helvetica,sans-serif,serif;">ERROR: column "’the_network’" does not exist<br>LINE 1: SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid...<br>
^</pre><p><b>In statement:</b><br>SELECT assign_vertex_id3d(’the_network’, 1, ’the_geom’, ’gid’, 'F_LEVEL', 'T_LEVEL');</p><p>Function <span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: 'Times New Roman'; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; font-size: medium;"><span class="Apple-style-span" style="font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: 13px;">assign_vertex_id3d </span></span>is bellow<br>
</p></span></span><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: 'Times New Roman'; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; font-size: medium;"><span class="Apple-style-span" style="font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: 13px;"><table width="90%">
<tbody><tr><td class="data1" style="background-color: rgb(243, 243, 233); margin: 4px; font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; padding: 0px; font-size: smaller; color: rgb(0, 0, 0); text-align: left;">
assign_vertex_id3d</td><td class="data1" style="background-color: rgb(243, 243, 233); margin: 4px; font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; padding: 0px; font-size: smaller; color: rgb(0, 0, 0); text-align: left;">
"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</td>
<td class="data1" style="background-color: rgb(243, 243, 233); margin: 4px; font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; padding: 0px; font-size: smaller; color: rgb(0, 0, 0); text-align: left;">character varying</td>
<td class="data1" style="background-color: rgb(243, 243, 233); margin: 4px; font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; padding: 0px; font-size: smaller; color: rgb(0, 0, 0); text-align: left;">plpgsql</td>
</tr><tr><th class="data" colspan="4" style="color: rgb(0, 0, 0); background-color: rgb(230, 230, 204); font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: smaller;">Definition</th></tr><tr><td class="data1" colspan="4" style="background-color: rgb(243, 243, 233); margin: 4px; font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; padding: 0px; font-size: smaller; color: rgb(0, 0, 0); text-align: left;">
<table><tbody><tr><td class="data1" style="background-color: rgb(243, 243, 233); margin: 4px; font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; padding: 0px 10px 0px 0px; font-size: smaller; color: rgb(0, 0, 0); text-align: left; vertical-align: top;">
<pre class="data1" style="font-size: 12px;">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>
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></pre></td><td class="data1" style="background-color: rgb(243, 243, 233); margin: 4px; font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; padding: 0px; font-size: smaller; color: rgb(0, 0, 0); text-align: left; vertical-align: top;">
<pre class="data1" style="font-size: 12px;"><span class="keyword" style="color: rgb(255, 128, 0);">DECLARE</span><br> _r record;<br> source_id <span class="keyword" style="color: rgb(255, 128, 0);">int</span>;<br> target_id <span class="keyword" style="color: rgb(255, 128, 0);">int</span>;<br>
srid <span class="keyword" style="color: rgb(255, 128, 0);">integer</span>;<br><span class="keyword" style="color: rgb(255, 128, 0);">BEGIN</span><br><br> <span class="keyword" style="color: rgb(255, 128, 0);">BEGIN</span><br>
<span class="keyword" style="color: rgb(255, 128, 0);">DROP</span> <span class="keyword" style="color: rgb(255, 128, 0);">TABLE</span> vertices_tmp;<br> <span class="keyword" style="color: rgb(255, 128, 0);">EXCEPTION</span><br>
<span class="keyword" style="color: rgb(255, 128, 0);">WHEN</span> UNDEFINED_TABLE <span class="keyword" style="color: rgb(255, 128, 0);">THEN</span><br> <span class="keyword" style="color: rgb(255, 128, 0);">END</span>;<br>
<br> <span class="keyword" style="color: rgb(255, 128, 0);">EXECUTE</span> <span class="literal" style="color: rgb(128, 128, 128);">'CREATE TABLE vertices_tmp (id serial)'</span>;<br><br><span class="comment" style="color: rgb(0, 128, 128);">-- FOR _r IN EXECUTE 'SELECT srid FROM geometry_columns WHERE f_table_name='''|| quote_ident(geom_table)||''';' LOOP<br>
</span><span class="comment" style="color: rgb(0, 128, 128);">-- srid := _r.srid;<br></span><span class="comment" style="color: rgb(0, 128, 128);">-- END LOOP;<br></span><br> srid := Find_SRID(<span class="literal" style="color: rgb(128, 128, 128);">'public'</span>,quote_ident(geom_table),quote_ident(geo_cname));<br>
<br><br> <span class="keyword" style="color: rgb(255, 128, 0);">EXECUTE</span> <span class="literal" style="color: rgb(128, 128, 128);">'SELECT addGeometryColumn('</span><span class="literal" style="color: rgb(128, 128, 128);">'vertices_tmp'</span><span class="literal" style="color: rgb(128, 128, 128);">', '</span><span class="literal" style="color: rgb(128, 128, 128);">'the_geom'</span><span class="literal" style="color: rgb(128, 128, 128);">', '</span>||srid||<span class="literal" style="color: rgb(128, 128, 128);">', '</span><span class="literal" style="color: rgb(128, 128, 128);">'POINT'</span><span class="literal" style="color: rgb(128, 128, 128);">', 3)'</span>;<br>
<span class="keyword" style="color: rgb(255, 128, 0);">CREATE</span> <span class="keyword" style="color: rgb(255, 128, 0);">INDEX</span> vertices_tmp_idx <span class="keyword" style="color: rgb(255, 128, 0);">ON</span> vertices_tmp <span class="keyword" style="color: rgb(255, 128, 0);">USING</span> GIST (the_geom);<br>
<br> <span class="keyword" style="color: rgb(255, 128, 0);">FOR</span> _r <span class="keyword" style="color: rgb(255, 128, 0);">IN</span> <span class="keyword" style="color: rgb(255, 128, 0);">EXECUTE</span> <span class="literal" style="color: rgb(128, 128, 128);">'SELECT '</span> || quote_ident(gid_cname) || <span class="literal" style="color: rgb(128, 128, 128);">' AS id,'</span><br>
|| <span class="literal" style="color: rgb(128, 128, 128);">' ST_MakePoint(ST_X(StartPoint('</span>|| quote_ident(geo_cname) ||<span class="literal" style="color: rgb(128, 128, 128);">')), ST_Y(StartPoint('</span>|| quote_ident(geo_cname) ||<span class="literal" style="color: rgb(128, 128, 128);">')), '</span>|| quote_ident(source_zlevel_cname) ||<span class="literal" style="color: rgb(128, 128, 128);">') AS source,'</span><br>
|| <span class="literal" style="color: rgb(128, 128, 128);">' ST_MakePoint(ST_X(EndPoint('</span>|| quote_ident(geo_cname) ||<span class="literal" style="color: rgb(128, 128, 128);">')), ST_Y(EndPoint('</span>|| quote_ident(geo_cname) ||<span class="literal" style="color: rgb(128, 128, 128);">')), '</span>|| quote_ident(target_zlevel_cname) ||<span class="literal" style="color: rgb(128, 128, 128);">') AS target'</span><br>
|| <span class="literal" style="color: rgb(128, 128, 128);">' FROM '</span> || quote_ident(geom_table)<br> LOOP<br><br> source_id := point_to_id3d(setsrid(_r.<span class="keyword" style="color: rgb(255, 128, 0);">source</span>, srid), tolerance);<br>
target_id := point_to_id3d(setsrid(_r.target, srid), tolerance);<br><br> <span class="keyword" style="color: rgb(255, 128, 0);">EXECUTE</span> <span class="literal" style="color: rgb(128, 128, 128);">'update '</span> || quote_ident(geom_table) ||<br>
<span class="literal" style="color: rgb(128, 128, 128);">' SET source = '</span> || source_id ||<br> <span class="literal" style="color: rgb(128, 128, 128);">', target = '</span> || target_id ||<br>
<span class="literal" style="color: rgb(128, 128, 128);">' WHERE '</span> || quote_ident(gid_cname) || <span class="literal" style="color: rgb(128, 128, 128);">' = '</span> || _<a href="http://r.id">r.id</a>;<br>
<span class="keyword" style="color: rgb(255, 128, 0);">END</span> LOOP;<br><br> <span class="keyword" style="color: rgb(255, 128, 0);">RETURN</span> <span class="literal" style="color: rgb(128, 128, 128);">'OK'</span>;<br>
<br><span class="keyword" style="color: rgb(255, 128, 0);">END</span>;</pre></td></tr></tbody></table></td></tr></tbody></table></span></span><br class="Apple-interchange-newline">- Miroslav<br><br><div class="gmail_quote">
2011/3/25 Stephen Woodbridge <span dir="ltr"><<a href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a>></span><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
network is a postgresql type so it must be quoted. So you can try doing:<div class="im"><br>
<br>
SELECT assign_vertex_id3d(’"network"’, 1, ’the_geom’, ’gid’, 'F_LEVEL', 'T_LEVEL');<br>
<br></div>
This might work, but in general you might have other issues with 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’, 'F_LEVEL', 'T_LEVEL');<br>
<br>
-Steve<div class="im"><br>
<br>
On 3/24/2011 6:32 PM, Miroslav Novta wrote:<br>
</div><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im">
Hello Dear,<br>
<br>
I have faced a problem with assign_vertex_id3d. It is a bit strange<br>
error message i have get. I have explored all code i suspect and have no<br>
clue. Error report is network is not a column, it is a table 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’, ’gid’, '...<br>
^<br>
<br>
In statement:<br>
SELECT assign_vertex_id3d(’network’, 1, ’the_geom’, ’gid’, 'F_LEVEL',<br>
'T_LEVEL');<br>
<br>
To explain more, network is my ways table defined with iGO 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></div><div><div></div><div class="h5">
<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 a lot of<br>
>> problems with turn restrictions because maneuvers table is 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 meters not<br>
>> degrees in assign vertex?<br>
><br>
> This depends on your data for example in degrees if you data 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 evaluate this<br>
is to look the the coordinates at the ends of to segments that should be<br>
connected. So:<br>
> abs(Xa - Xb) < tolerance<br>
> to match the two ends to the same node. If you set tolerance too big<br>
then it will merge close by nodes that should not be 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 numbers not<br>
being exact like integers and can have rounding errors in the 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></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>
>> 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 off to<br>
>> post. There is no need for that as your implementation accurately<br>
>> reflects mine.<br>
>><br>
>> Miroslav,<br>
>><br>
>> Regarding turn restrictions, you should search the archives for this<br>
>> as I have been fairly vocal about the limitations. To summarize:<br>
>><br>
>> 1. turn restrictions only work with shooting star<br>
>> 2. If you have multiple turn restrictions, I think you have 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 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 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 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 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'', ''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>
>> 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), tolerance);<br>
>> target_id := point_to_id3d(setsrid(_r.target, srid), 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>
<<a href="http://r.id" target="_blank">http://r.id</a>>; END LOOP;<br>
>><br>
>> RETURN 'OK';<br>
>><br>
>> END; $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT COST 100; ALTER<br>
>> FUNCTION assign_vertex_id3d(character varying, double 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></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>>;<div class="im"><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>
>> <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>
>> <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>
</div></blockquote>
<br>
</blockquote></div><br><br clear="all"><br>-- <br>Miroslav Novta<br>
</div>