<div dir="ltr"><div>Somehow I feel there is no need for plpgsql in what you are doing and that indeed the ST_AsEwkt is too much.<br></div>I can't wrap my head around what this script is exactly trying to do though. Can you elaborate a bit on your use-case? What is the original data and what would you like to know from it?<div><br><div><br></div><div>Best,</div><div> Tom</div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Jul 5, 2017 at 7:54 PM, Olivier Leprêtre <span dir="ltr"><<a href="mailto:o.lepretre@gmail.com" target="_blank">o.lepretre@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="FR" link="blue" vlink="purple"><div class="m_-6220950655889643972WordSection1"><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif"">Hi,<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif"">I'm using pgplsql to calculate shortest distance between points. This works but I'm wondering if my code is optimized because I'm using many ST_AsEwkt.<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif"">sqls='SELECT id code,ST_AsEwkt(ST_StartPoint(<wbr>the_geom)) as geom1,ST_AsEwkt(ST_EndPoint(<wbr>the_geom)) as geom2 FROM ' || schem || '.trench';<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif"">FOR row IN <u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""> EXECUTE (sqls)<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif"">LOOP<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""> sqls1='SELECT id code1,ST_Distance(ST_AsEwkt(<wbr>the_geom),St_AsEwkt(''%2$s'')) FROM %1$s.nodes order by ST_Distance(ST_AsEwkt(the_<wbr>geom),ST_AsEwkt(''%2$s'')) LIMIT 1';<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""> sqls1=format(sqls1,schem,row.<wbr>geom1);<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""> </span><span style="font-size:8.0pt;font-family:"Verdana","sans-serif"">FOR row1 IN<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Verdana","sans-serif""> EXECUTE (sqls1)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Verdana","sans-serif"">...<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Verdana","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif"">Would it be possible to have a smarter syntax with less ST_AsEwkt ?<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif"">Thanks, <u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:8.0pt;font-family:"Verdana","sans-serif"">Olivier<u></u><u></u></span></p></div><div id="m_-6220950655889643972DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br> <table style="border-top:1px solid #d3d4de">
<tbody><tr>
<td style="width:55px;padding-top:18px"><a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank"><img src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif" alt="" width="46" height="29" style="width:46px;height:29px"></a></td>
<td style="width:470px;padding-top:17px;color:#41424e;font-size:13px;font-family:Arial,Helvetica,sans-serif;line-height:18px">Garanti sans virus. <a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" style="color:#4453ea" target="_blank">www.avast.com</a> </td>
</tr>
</tbody></table>
<a href="#m_-6220950655889643972_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"> </a></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>