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]> 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] > https://lists.osgeo.org/mailman/listinfo/postgis-users
MichaelHaertel.sql
Description: application/sql
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
