<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=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
span.E-MailFormatvorlage17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.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;}
/* List Definitions */
@list l0
{mso-list-id:2067291490;
mso-list-type:hybrid;
mso-list-template-ids:-1575423006 67567617 67567619 67567621 67567617 67567619 67567621 67567617 67567619 67567621;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></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="MsoNormal">Hello List,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">I observed a strange behavior when I implemented a trigger function.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Given: Table “fcp_4258_kls_addr” with address points, Table “fcp_4258_candidates” with some other points.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">After playing around with the statements I came up with this:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New"">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></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">This works fine when I run the statement manually in the console:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New""> id | geo_kls_dist2candidate_m<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New"">----------+--------------------------<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New"">17553866 | 6.85436569<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New"">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;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New"">NEW.geo_kls_id = klsid;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New"">NEW.geo_kls_distance2candidate_m = klsdistancetocandidatem;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Some findings:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<ul style="margin-top:0cm" type="disc">
<li class="MsoListParagraph" style="margin-left:0cm;mso-list:l0 level1 lfo1"><span lang="EN-US">The id returned is within 0.01 degree radius for the candidate<o:p></o:p></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l0 level1 lfo1"><span lang="EN-US">The address point with the given id has correctly referenced distance to the candidate (but is not the closest)<o:p></o:p></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l0 level1 lfo1"><span lang="EN-US">The address point returned does neither belong to the preceding candidate point nor to the successive candidate (when sorted by primary key)<o:p></o:p></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l0 level1 lfo1"><span lang="EN-US">The update trigger is fired and the procedure is executed (Tested with various update statements)<o:p></o:p></span></li></ul>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">The wkb_geometry doesn’t change with the update. I have no idea why the address points are different.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Can anybody help?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#00B050;mso-fareast-language:DE">Michael</span><span style="color:#1F497D;mso-fareast-language:DE">
</span><span style="mso-fareast-language:DE"><o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>