[postgis-users] select statement returns different results when fired manually or by update trigger

[Michael.Haertel at t-systems.com]

Hello List,

I observed a strange behavior when I implemented a trigger function.

Given: Table "fcp_4258_kls_addr" with address points, Table "fcp_4258_candidates" with some other points.
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).

After playing around with the statements I came up with this:

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;

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.

This works fine when I run the statement manually in the console:

    id    | geo_kls_dist2candidate_m
----------+--------------------------
17553866 |               6.85436569


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:

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;

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.

Some findings:


  *   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)

The wkb_geometry doesn't change with the update. I have no idea why the address points are different.

Can anybody help?

Michael

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190315/77f6200a/attachment.html>