Opened 6 years ago

Closed 6 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: trunk
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 Changed 6 years ago by robe

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

comment:2 Changed 6 years ago by pramsey

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 Changed 6 years ago by pramsey

Priority: mediumcritical

Any collection type is going to exhibit this, it's pretty non-good.

comment:4 Changed 6 years ago by robe

and ... you'll work on this at sprint right :)

comment:5 Changed 6 years ago by pramsey

Does r12340 do anything for you?

comment:6 Changed 6 years ago by xowap

@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 Changed 6 years ago by pramsey

Resolution: fixed
Status: newclosed

Applied to 2.1 branch at r12343

Note: See TracTickets for help on using tickets.