Opened 10 years ago

Closed 10 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 robe, 10 years ago

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 by pramsey, 10 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 pramsey, 10 years ago

Priority: mediumcritical

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

comment:4 by robe, 10 years ago

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

comment:5 by pramsey, 10 years ago

Does r12340 do anything for you?

comment:6 by xowap, 10 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 pramsey, 10 years ago

Resolution: fixed
Status: newclosed

Applied to 2.1 branch at r12343

Note: See TracTickets for help on using tickets.