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

[Marcin Mionskowski]

I'm not sure I understood you correctly, but it looks like it works on my
postgres-postgis installation. See the attached file.
"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"
"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"

pt., 15 mar 2019 o 16:30 <Michael.Haertel at t-systems.com> napisał(a):

> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190315/bd7c35d4/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: MichaelHaertel.sql
Type: application/sql
Size: 1918 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190315/bd7c35d4/attachment.bin>