Hello Marcin, Regina,
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.
Marcin automatically avoided my mistake without probably even thing about it.
He wrote:
BEGIN
SELECT s.id, ST_DistanceSphere(s.geom, NEW.geom) dist
INTO kid, kdist
FROM t_rand_pnts s
WHERE ST_DWithin(s.geom, NEW.geom, 0.2)
ORDER BY dist ASC LIMIT 1;
NEW.pid = kid;
NEW.dist = kdist;
return new;
END;
My code was:
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 was ordering by the variable klsdistancetocandidatem 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.
I changed my function and now it works as expected.
Thank you very much for your help.
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?
But my original question has been answered, thanks again!
Regards,
Michael
-----Ursprüngliche Nachricht-----
Von: postgis-users <[email protected]> Im Auftrag von
Regina Obe
Gesendet: Freitag, 15. März 2019 21:21
An: 'PostGIS Users Discussion' <[email protected]>
Betreff: Re: [postgis-users] select statement returns different results when
fired manually or by update trigger
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.
Try:
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;
From: postgis-users [mailto:[email protected]] On Behalf Of
Marcin Mionskowski
Sent: Friday, March 15, 2019 3:59 PM
To: PostGIS Users Discussion
<[email protected]<mailto:[email protected]>>
Subject: Re: [postgis-users] select statement returns different results when
fired manually or by update trigger
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
<[email protected]<mailto:[email protected]>>
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
[email protected]<mailto:[email protected]>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]<mailto:[email protected]>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
