<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Nur Text Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
span.NurTextZchn
{mso-style-name:"Nur Text Zchn";
mso-style-priority:99;
mso-style-link:"Nur Text";
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoPlainText">Hello Marcin, Regina,<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><span lang="EN-US">thank you very much for your answers and especially to Marcin for sending the SQL. I first only read Marcins mail and investigated the script until I found out what Regina was pointing out as well.
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">Marcin automatically avoided my mistake without probably even thing about it. He wrote:<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New"">BEGIN<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""> SELECT s.id, ST_DistanceSphere(s.geom, NEW.geom)
<b><span style="color:red">dist</span></b><o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""> INTO kid, kdist
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""> FROM t_rand_pnts s
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""> WHERE ST_DWithin(s.geom, NEW.geom, 0.2)
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""> ORDER BY
<b><span style="color:red">dist</span></b><span style="color:red"> </span>ASC LIMIT 1;<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""> NEW.pid = kid;<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""> NEW.dist = kdist;<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New""> return new;<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New"">END;</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">My code was: <o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New"">SELECT s.id, ST_DistanceSphere(s.wkb_geometry, NEW.wkb_geometry) INTO klsid,
<b><span style="color:red">klsdistancetocandidatem</span></b><span style="color:red">
</span>FROM kddb.fcp_4258_kls_addr s WHERE ST_DWithin(s.wkb_geometry, NEW.wkb_geometry, 0.01) ORDER BY
<b><span style="color:red">klsdistancetocandidatem</span></b><span style="color:red">
</span>ASC LIMIT 1; <o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New"">NEW.geo_kls_id = klsid;
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier New"">NEW.geo_kls_distance2candidate_m = klsdistancetocandidatem;<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">I was ordering by the variable </span>
<span lang="EN-US" style="font-family:"Courier New"">klsdistancetocandidatem </span>
<span lang="EN-US">which apparently doesn’t work (For some reason it works if manually executed!) in the trigger function while Marcin introduced the alias “dist” and sorted by the column alias.<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">I changed my function and now it works as expected.
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">Thank you very much for your help. <o:p>
</o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">I am by far no SQL expert and it seems that I made an obvious mistake which for some reason doesn’t count when the SQL is executed manually. Do you have an explanation why my SQL works when executed manually? Is there
a difference in how variables are handled?<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">But my original question has been answered, thanks again!<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">Regards,<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">Michael<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span style="mso-fareast-language:DE">-----Ursprüngliche Nachricht-----<br>
Von: postgis-users <postgis-users-bounces@lists.osgeo.org> Im Auftrag von Regina Obe<br>
Gesendet: Freitag, 15. März 2019 21:21<br>
An: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br>
Betreff: Re: [postgis-users] select statement returns different results when fired manually or by update trigger</span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">At a glance, I suspect your klsdistancetocandidatem is not referencing what you think it is as that's a variable in the trigger and not a column alias so all results within the 0.01 would have the same value so your result would be
arbitrary.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Try:<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">SELECT s.id, ST_DistanceSphere(s.wkb_geometry, NEW.wkb_geometry) AS kdistsp INTO klsid, klsdistancetocandidatem FROM kddb.fcp_4258_kls_addr s WHERE ST_DWithin(s.wkb_geometry, NEW.wkb_geometry, 0.01) ORDER BY kdistsp ASC LIMIT 1;<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">From: postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org"><span style="color:windowtext;text-decoration:none">mailto:postgis-users-bounces@lists.osgeo.org</span></a>] On Behalf Of Marcin Mionskowski<o:p></o:p></p>
<p class="MsoPlainText">Sent: Friday, March 15, 2019 3:59 PM<o:p></o:p></p>
<p class="MsoPlainText">To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org"><span style="color:windowtext;text-decoration:none">postgis-users@lists.osgeo.org</span></a>><o:p></o:p></p>
<p class="MsoPlainText">Subject: Re: [postgis-users] select statement returns different results when fired manually or by update trigger<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">I'm not sure I understood you correctly, but it looks like it works on my postgres-postgis installation. See the attached file.<o:p></o:p></p>
<p class="MsoPlainText">"PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit"<o:p></o:p></p>
<p class="MsoPlainText">"POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="110" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER"<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">pt., 15 mar 2019 o 16:30 <<a href="mailto:Michael.Haertel@t-systems.com"><span style="color:windowtext;text-decoration:none">Michael.Haertel@t-systems.com</span></a>> napisał(a):<o:p></o:p></p>
<p class="MsoPlainText">Hello List,<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">I observed a strange behavior when I implemented a trigger function.<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">Given: Table “fcp_4258_kls_addr” with address points, Table “fcp_4258_candidates” with some other points.<o:p></o:p></p>
<p class="MsoPlainText">Objective: find closest point in fcp_4258_kls_addr to a point in fcp_4258_candidates (from all found address points in a radius of 0.01 degrees around the candidate).<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">After playing around with the statements I came up with this:<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">select s.id, ST_DistanceSphere(ST_SetSRID(ST_Point(6.951468, 50.93651),4258), s.wkb_geometry) as klsdistancetocandidatem FROM kddb.fcp_4258_kls_addr s WHERE ST_DWithin(s.wkb_geometry, ST_SetSRID(ST_Point(6.951468, 50.93651),4258), 0.01)
ORDER BY klsdistancetocandidatem ASC LIMIT 1;<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">I hope that the function does what I want: First select all addresses within 0.01 degrees distance to the point 6.951468, 50.93651 and then calculate the distance to all of them. Order by distance in ascending order and select the first
which should be the closest to the questioned candidate. <o:p></o:p></p>
<p class="MsoPlainText"> <o:p></o:p></p>
<p class="MsoPlainText">This works fine when I run the statement manually in the console:<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText"> id | geo_kls_dist2candidate_m<o:p></o:p></p>
<p class="MsoPlainText">----------+--------------------------<o:p></o:p></p>
<p class="MsoPlainText">17553866 | 6.85436569<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText"> <o:p></o:p></p>
<p class="MsoPlainText">My goal was to save the closest address point (id) and the distance to the table as soon as the candidate table is updated. The relevant line in the trigger function for the trigger “BEFORE UPDATE ON candidates FOR EACH ROW EXECUTE PROCEDURE”
looks like this:<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">SELECT s.id, ST_DistanceSphere(s.wkb_geometry, NEW.wkb_geometry) INTO klsid, klsdistancetocandidatem FROM kddb.fcp_4258_kls_addr s WHERE ST_DWithin(s.wkb_geometry, NEW.wkb_geometry, 0.01) ORDER BY klsdistancetocandidatem ASC LIMIT 1;
NEW.geo_kls_id = klsid; NEW.geo_kls_distance2candidate_m = klsdistancetocandidatem;<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">I assumed that the trigger does the same as the select statement but I get a different result for a candidate. The address point referenced in the candidates table for the candidate with the coordinates 6.951468, 50.93651 is 497 meters
away and not 6.8 meters. <o:p></o:p></p>
<p class="MsoPlainText"> <o:p></o:p></p>
<p class="MsoPlainText">Some findings:<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">• The id returned is within 0.01 degree radius for the candidate • The address point with the given id has correctly referenced distance to the candidate (but is not the closest) • The address point returned does neither belong to the
preceding candidate point nor to the successive candidate (when sorted by primary key) • The update trigger is fired and the procedure is executed (Tested with various update statements)<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">The wkb_geometry doesn’t change with the update. I have no idea why the address points are different.
<o:p></o:p></p>
<p class="MsoPlainText"> <o:p></o:p></p>
<p class="MsoPlainText">Can anybody help?<o:p></o:p></p>
<p class="MsoPlainText"><o:p></o:p></p>
<p class="MsoPlainText">Michael <o:p></o:p></p>
<p class="MsoPlainText"> <o:p></o:p></p>
<p class="MsoPlainText">_______________________________________________<o:p></o:p></p>
<p class="MsoPlainText">postgis-users mailing list<o:p></o:p></p>
<p class="MsoPlainText"><a href="mailto:postgis-users@lists.osgeo.org"><span style="color:windowtext;text-decoration:none">postgis-users@lists.osgeo.org</span></a><o:p></o:p></p>
<p class="MsoPlainText"><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users"><span style="color:windowtext;text-decoration:none">https://lists.osgeo.org/mailman/listinfo/postgis-users</span></a><o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">_______________________________________________<o:p></o:p></p>
<p class="MsoPlainText">postgis-users mailing list<o:p></o:p></p>
<p class="MsoPlainText"><a href="mailto:postgis-users@lists.osgeo.org"><span style="color:windowtext;text-decoration:none">postgis-users@lists.osgeo.org</span></a><o:p></o:p></p>
<p class="MsoPlainText"><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users"><span style="color:windowtext;text-decoration:none">https://lists.osgeo.org/mailman/listinfo/postgis-users</span></a><o:p></o:p></p>
</div>
</body>
</html>