<div dir="ltr"><div><div><div><div>Sorry this it out of question.<br></div>You must be aware that I help people on the mailing list on my free time.<br></div><div>(I'm a researcher)<br></div><div><br></div>In my opinion, rewriting your function, then debugging it is awfully close to proper work and not help.<br><br></div>This is particularly a problem because your question is not related to postgis improvement or other open source project.<br><br></div><div>I consider I already did more than help by giving you tools to understand your problem and solve it, then even rewrite your function.<br>There are many people on this mailing list that do this kind of function writing for a living (not my case).<br></div><div><br></div><div>If you have a specific question I may answer it.<br></div><div><br></div>Cheers,<br>RémiC<br></div><div class="gmail_extra"><br><div class="gmail_quote">2015-02-18 23:29 GMT+01:00 Miller, Stephan <span dir="ltr"><<a href="mailto:smille12@harris.com" target="_blank">smille12@harris.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div link="blue" vlink="purple" lang="EN-US">
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Here is the SQL.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thanks.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Steve<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Rémi Cura [mailto:<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>]
<br><span class="">
<b>Sent:</b> Wednesday, February 18, 2015 2:57 PM<br>
</span><b>To:</b> Miller, Stephan; PostGIS Users Discussion</span></p><div><div class="h5"><br>
<b>Subject:</b> Re: [postgis-users] operator is not unique: text || geometry<u></u><u></u></div></div><p></p>
</div><div><div class="h5">
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<div>
<div>
<p class="MsoNormal">(better to stay on list )<br>
I meant<br>
---<br>
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">RAISE EXCEPTION '%',_q ;
</span><br>
---<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">You must understand that plpgsql function fabricate on the fly SQL statement (meaning, at execution time).<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">That means that without actually executing the function, there is no way to know exactly what it does.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Now i I __<b>can't</b>__ execute your function, not having your table<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Now at execution,<u></u><u></u></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt">it will stop you function there, and print the UPDATE query that should have been executed.<u></u><u></u></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Then you can analyse the UPDATE query that have been printed, and test it to see why it doesn't work and how you could make it work (how which I have no idea without the query).<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">You should see something like (I put xxx because I don' have the value.)<br>
---------<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">sql NOTICE : <br>
UPDATE fgcm.xxxx SET (x,x,x,x)::topogeometry<br>
= topology.toTopoGeom(ST_Transform(xxxxx::geometry,32648),'xxxx', 1, 1.0) <u></u><u></u></p>
<p class="MsoNormal">WHERE objectid = xxxx'<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">---------<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Maybe you need to replace the <br>
-------<br>
= topology.toTopoGeom(ST_Transform($1::geometry,32648), %I, 1, 1.0)<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">-------<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">with<br>
---------<br>
= topology.toTopoGeom(ST_Transform($1::geometry,32648), %L, 1, 1.0)<br>
---------<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">I can't know.<u></u><u></u></p>
</div>
<p class="MsoNormal">Cheers,<br>
Rémi-C<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">2015-02-18 20:43 GMT+01:00 Miller, Stephan <<a href="mailto:smille12@harris.com" target="_blank">smille12@harris.com</a>>:<u></u><u></u></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Remi –</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">I didn’t understand.</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Adding
</span>RAISE EXCEPTION '%',-q ; before the EXECUTE <span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
generates a syntax error. Did you mean perhaps</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> RAISE EXCEPTION '%',_q ;
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Instead of
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> EXECUTE _q USING r.shape, cleantopo;</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">How do I specify the r.shape and cleantopo parameters?</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Sorry to be so dense.</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thanks,</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Steve</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Rémi Cura [mailto:<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>]
<br>
<b>Sent:</b> Wednesday, February 18, 2015 1:59 PM</span><u></u><u></u></p>
<div>
<div>
<p class="MsoNormal"><br>
<b>To:</b> Miller, Stephan<br>
<b>Subject:</b> Re: [postgis-users] operator is not unique: text || geometry<u></u><u></u></p>
</div>
</div>
</div>
<div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<div>
<div>
<div>
<p class="MsoNormal">As I wrote before,<u></u><u></u></p>
</div>
<p class="MsoNormal">simply print the update query (and don't execute it)<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">You can do this by adding a RAISE EXCEPTION '%',-q ; before the EXECUTE<u></u><u></u></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt">then test it !
<u></u><u></u></p>
</div>
<p class="MsoNormal">Cheers,<br>
Rémi-C<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<p class="MsoNormal">2015-02-18 19:57 GMT+01:00 Miller, Stephan <<a href="mailto:smille12@harris.com" target="_blank">smille12@harris.com</a>>:<u></u><u></u></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Remi –</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">I forced the transform as you suggested using SetSRID. Now I am failing the UPDATE query somehow.
_________________________________________________________________________________________</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> _q := format('SELECT objectid, f_code, shape, topo_shape FROM fgcm.%I',updatedtablename);
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> FOR r IN</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> EXECUTE _q</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> LOOP</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> BEGIN</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> RAISE NOTICE 'Loading % attempt with shape = % and topo_shape = %'
, r.objectid, r.shape, r.topo_shape; </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> RAISE NOTICE 'Table % Shape %', updatedtablename, r.topo_shape;
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> _q :=
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> format('UPDATE fgcm.%I SET %I = topology.toTopoGeom(ST_Transform(ST_SetSRID($1,
4326),32648), $2, 1, 1.0) </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> WHERE objectid =
r.objectid' ,updatedtablename, r.topo_shape);</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> EXECUTE _q USING r.shape, cleantopo;</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> raise NOTICE 'After % Shape %',updatedtablename,r.topo_shape;
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> RAISE NOTICE 'Object % after conversion from shape = % to topo_shape = %', r.objectid,
(ST_AsText(r.shape)), (ST_AsText(r.topo_shape));</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> EXCEPTION</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> WHEN OTHERS THEN</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> RAISE WARNING 'Loading of record % failed: % %', r.objectid, SQLSTATE, SQLERRM;</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> END;
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> END LOOP;</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> RETURN;</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">END</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">$BODY$</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> LANGUAGE plpgsql VOLATILE</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> COST 100</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> ROWS 2000;
</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">SELECT * FROM fgcm.hc_check_gaps_in_linear_topology('vnroadsclipped', 'VNclippedroadscleantopo');</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">______________________________________________________________________________________</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">The results for the first feature is shown below.</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">NOTICE: Loading 1 attempt with shape = 01020000E0E610000002000000380952E7B97B5A40F074DD1774CD344000000000006AE8C0000000000000F87FE825AB94B17B5A40F013885085CD344000000000006AE8C0000000000000F87F
and topo_shape = <NULL></span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">NOTICE: Table updatedvnroadsclipped Shape <NULL></span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">WARNING: Loading of record 1 failed: 22004 null values cannot be formatted as an SQL identifier</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">The absence of the two RAISE NOTICE prints means the UPDATE is failing somehow. Any suggestions?</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thanks,</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Steve</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Rémi Cura [mailto:<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>]
<br>
<b>Sent:</b> Wednesday, February 18, 2015 4:47 AM<br>
<b>To:</b> Miller, Stephan</span><u></u><u></u></p>
<div>
<div>
<p class="MsoNormal"><br>
<b>Subject:</b> Re: [postgis-users] operator is not unique: text || geometry<u></u><u></u></p>
</div>
</div>
</div>
<div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Good,<u></u><u></u></p>
</div>
<p class="MsoNormal">maybe the srid of "<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">$1::geometry</span>" is not what it should be, you could try to force it (ST_SetSRID(<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">$1::geometry,your_srid</span>)<br>
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">ST_Transform($1::geometry, 32468) ---> </span>
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">ST_Transform(ST_SetSRID($1::geometry,your_srid), 32468)</span><br>
Cheers,<br>
Rémi-C<u></u><u></u></p>
</div>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<p class="MsoNormal">2015-02-17 20:52 GMT+01:00 Miller, Stephan <<a href="mailto:smille12@harris.com" target="_blank">smille12@harris.com</a>>:<u></u><u></u></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Remi –</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">I have it working with one exception: my embedded ST_Transform($1::geometry, 32468) has stopped working.
It is not transforming lat/lon to a local UTM coordinate.</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thanks for your help!</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Steve</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Rémi Cura [mailto:<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>]
<br>
<b>Sent:</b> Tuesday, February 17, 2015 11:37 AM<br>
<b>To:</b> Miller, Stephan<br>
<b>Cc:</b> PostGIS Users Discussion<br>
<b>Subject:</b> Re: [postgis-users] operator is not unique: text || geometry</span><u></u><u></u></p>
</div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<div>
<p class="MsoNormal">Here is the "cleaned" version, still no good tough.<u></u><u></u></p>
</div>
<div>
<div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">I don't understand what you want to do, I don't have your table structure nor your data, so you will need to work from that.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">CREATE OR REPLACE FUNCTION hc_check_gaps_in_linear_topology(IN tablename text, IN cleantopo text)<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> RETURNS TABLE(objectid integer, f_code character varying, topo_shape topogeometry) AS<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">$BODY$<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">declare<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> updatedtablename text;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">DECLARE<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> r record;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> _q text; <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">BEGIN<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> -- SELECT sde_set_current_version(10.2.1);<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> updatedtablename = 'updated' || tablename;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> _q := format('CREATE TABLE %I AS SELECT objectid, f_code, shape FROM %I ; ' ,updatedtablename,tablename) ;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> EXECUTE _q ; <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> PERFORM topology.DropTopology(cleantopo );<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> -- Create a new topology<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> -- Note need to generalize the SRID calculation to select the best fit UTM zone based on longitude extents<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> PERFORM topology.CreateTopology(cleantopo ,32648, 0.000001, TRUE); <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> PERFORM topology.AddTopoGeometryColumn( cleantopo , fgcm , updatedtablename ,'topo_shape','LINESTRING');<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> PERFORM topology.TopologySummary(cleantopo );<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> _q := format('SELECT objectid, f_code, shape, topo_shape FROM fgcm.%I',updatedtablename); <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> FOR r IN<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> EXECUTE _q<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> LOOP<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> BEGIN<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> RAISE NOTICE 'Loading % attempt with shape = % and topo_shape = %' , r.objectid, r.shape, r.topo_shape; <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> _q := <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> format('UPDATE fgcm.%I SET %I::topogeometry<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> = topology.toTopoGeom(ST_Transform($1::geometry,32648), %I, 1, 1.0)<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> WHERE objectid = $2' ,updatedtablename,topo_shape,cleantopo);<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> EXECUTE _q USING r.shape, r.objectid ;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> RAISE NOTICE 'Object % after conversion from shape = % to topo_shape = %', r.objectid, (ST_AsText(r.shape)), (ST_AsText(r.topo_shape)); <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> EXCEPTION<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> WHEN OTHERS THEN<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> RAISE WARNING 'Loading of record % failed: % %', r.objectid, SQLSTATE, SQLERRM;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> END;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> END LOOP;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> RETURN;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">END<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">$BODY$<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> LANGUAGE plpgsql VOLATILE<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> COST 100<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> ROWS 2000; <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Cheers,<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Rémi-C<u></u><u></u></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div></div></div>
</div>
</blockquote></div><br></div>