Friday 30 September 2016

postgis - Unexpected behaviour using ST_Equals() with QGIS


Let's say there is a table 'polygon_a' and a table 'polygon_b'. Each table contains three polygon features. The polygon features are spatially equal.


CREATE TABLE polygon_a (

gid serial NOT NULL,
geom geometry(polygon, your_SRID),
CONSTRAINT polygon_a_pkey PRIMARY KEY (gid)
);

CREATE TABLE polygon_b (
gid serial NOT NULL,
geom geometry(polygon, your_SRID),
CONSTRAINT polygon_b_pkey PRIMARY KEY (gid)
);


enter image description here


Now I've created a view 'equals_false' to select all features from table 'polygon_a' where ST_Equals(polygon_a.geom, polygon_b.geom) is false. After using the QGIS 'Split features' tool to split a feature from table 'polygon_a' the view 'equals_false' returns two results.


CREATE VIEW equals_false AS SELECT
polygon_a.gid,
polygon_a.geom
FROM
polygon_a LEFT JOIN polygon_b
ON ST_Equals(polygon_a.geom, polygon_b.geom)
WHERE polygon_b.gid IS NULL;


enter image description here enter image description here


But after using the 'Merge selected features' tool to merge the splitted features 'equals false' still returns a result. What's the reason of this behaviour?


enter image description here



Answer



As mentioned by 'dbaston' ST_Equals() doesn't return the expected results because of the added nodes. But using the hausdorff distance to compare the geometries is working.


CREATE VIEW equals_false AS SELECT DISTINCT
polygon_a.gid, polygon_a.geom
FROM
polygon_a, polygon_b

WHERE ST_Intersects(polygon_a.geom, polygon_b.geom)
AND NOT ST_Touches(polygon_a.geom, polygon_b.geom)
AND ST_HausdorffDistance(polygon_a.geom, polygon_b.geom) > 0.1;

I'm just not sure about the proper threshold.


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...