Opened 11 years ago
Closed 11 years ago
#2675 closed defect (fixed)
ST_Intersects() in WHERE clause misses results
Reported by: | xowap | Owned by: | pramsey |
---|---|---|---|
Priority: | critical | Milestone: | PostGIS 2.1.2 |
Component: | postgis | Version: | master |
Keywords: | Cc: |
Description
As explained in this stackexchange question, I get inaccurate results when doing a ST_Intersects.
To summarize how to reproduce the issue described in the question, here is a small SQL script
CREATE TABLE "demo" ( "id" serial NOT NULL PRIMARY KEY, "geom" geography(GEOMETRYCOLLECTION,4326), "poly" geography(POLYGON,4326), "point" geography(POINT,4326) ); INSERT INTO "demo" ("geom", "poly", "point") VALUES ('GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography, 'POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05))'::geography, NULL), ('GEOMETRYCOLLECTION(POINT(40.0045845585 70.0036813508))'::geography, NULL, 'POINT(40.0045845585 70.0036813508)'::geography); -- Does not work SELECT id, ST_AsText(geom) FROM demo WHERE ST_Intersects(geom, 'GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography); -- Works SELECT id, ST_AsText(geom) FROM demo WHERE ST_Intersects(geom, 'POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05))'::geography); -- Works SELECT id, ST_AsText(geom) FROM demo WHERE ST_Intersects("point", 'GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography);
As indicated in the comments, I have built PostGIS from branches/2.1 at revision 12308.
Change History (7)
comment:1 by , 11 years ago
comment:2 by , 11 years ago
I've found the source of this problem (yah, plane) but only got part way through the fix. Still unsure what caused the FLIPPING in your example though robe, the problem I found was just a straight up error in logic (poor handling of multi-* things and collections in the tree distance code) so the failures should be deterministic.
comment:3 by , 11 years ago
Priority: | medium → critical |
---|
Any collection type is going to exhibit this, it's pretty non-good.
comment:6 by , 11 years ago
@pramsey: yes, it looks pretty much fixed for me
The test set works, as well as my real-life application that was initially trying to make that query
comment:7 by , 11 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Applied to 2.1 branch at r12343
Yah this looks to still be an issue. I suspect its an uncaught case of our poly in poly case noted in #2556 that is tripped up by the geometry collection casing