A ST_Intersect row is missed when using JOIN.

[Wenjing]

Consider there are 3 geometries in table t.

DROP TABLE IF EXISTS t; 
CREATE TABLE t (id int, geom geometry);
INSERT INTO t (id, geom) VALUES (1,ST_GeomFromText('MULTILINESTRING((-1 0,1 0))'));
INSERT INTO t (id, geom) VALUES (2,ST_GeomFromText('LINESTRING(-1 0,0 0)'));
INSERT INTO t (id, geom) VALUES (3,ST_GeomFromText('GEOMETRYCOLLECTION(MULTIPOINT((-1 0)),LINESTRING(0 -1,1 0))'));

They intersect each other.

SELECT ST_Intersects(a1.geom, a2.geom), ST_Intersects(a2.geom, a1.geom) FROM t As a1, t As a2 WHERE a1.id = 1 and a2.id = 2;
SELECT ST_Intersects(a1.geom, a2.geom), ST_Intersects(a2.geom, a1.geom) FROM t As a1, t As a2 WHERE a1.id = 1 and a2.id = 3;
SELECT ST_Intersects(a1.geom, a2.geom), ST_Intersects(a2.geom, a1.geom) FROM t As a1, t As a2 WHERE a1.id = 2 and a2.id = 3;
-- result{t, t; t, t; t, t}

But when I query how many geometries intersect, I get the result 5. However, the expected result is 6 because all of them intersect each other.

SELECT COUNT(*) FROM t As a1 JOIN t As a2 ON ST_Intersects(a1.geom, a2.geom)  WHERE a1.id <> a2.id;
--expected{6}; actual{5}

[Wenjing]

The (2, 3) row is missed.

SELECT a1.id, a2.id FROM t As a1 JOIN t As a2 ON ST_Intersects(a1.geom, a2.geom)  WHERE a1.id <> a2.id;
--  id | id 
-- ----+----
--   1 |  2
--   1 |  3
--   2 |  1
--   3 |  1
--   3 |  2

[robe]

I get the expected 6.

 SELECT COUNT(*) FROM t As a1 JOIN t As a2 ON ST_Intersects(a1.geom, a2.geom)  WHERE a1.id <> a2.id;

output: 6

SELECT a1.id, a2.id FROM t As a1 JOIN t As a2 ON ST_Intersects(a1.geom, a2.geom)  WHERE a1.id <> a2.id;
 id | id
----+----
  1 |  2
  1 |  3
  2 |  1
  2 |  3
  3 |  1
  3 |  2
(6 rows)

What does this return for you?

SELECT postgis_full_version();

I have:

POSTGIS="3.4.0 3.4.0" [EXTENSION] PGSQL="160" GEOS="3.12.0-CAPI-1.18.0" SFCGAL="SFCGAL 1.4.1, CGAL 5.3, BOOST 1.78.0" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=C:\Windows\ServiceProfiles\NetworkService\AppData\Local/proj DATABASE_PATH=C:\Program Files\PostgreSQL\16\share\contrib\postgis-3.4\proj\proj.db" LIBXML="2.9.14" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"

I suspect this is a GEOS bug you are experiencing since most ST_Intersects except the simplest ones is handled by GEOS and I'm not seeing it on my end.

[Wenjing]

Sorry for forgetting to add the version I use the version is the newest in GitHub:

POSTGIS="3.5.0dev 3.4.0rc1-705-g5c3ec8392" [EXTENSION] PGSQL="170" GEOS="3.13.0dev-CAPI-1.18.0" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org/ USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" LIBXML="2.9.13"

It seems a regression issue?

[robe]

ah possibly let me update my geos

[pramsey]

This is a GEOS issue, in versions 3.11 and lower. The following test shows the failure in GEOS.

diff --git a/tests/unit/geom/prep/PreparedGeometryTest.cpp b/tests/unit/geom/prep/PreparedGeometryTest.cpp
index efc5c4b82..ffdd90faf 100644
--- a/tests/unit/geom/prep/PreparedGeometryTest.cpp
+++ b/tests/unit/geom/prep/PreparedGeometryTest.cpp
@@ -68,4 +68,21 @@ void object::test<1>
     ensure( pg1->covers(g2.get()));
 }
 
+
+// See https://trac.osgeo.org/postgis/ticket/5601
+template<>
+template<>
+void object::test<2>
+()
+{
+    g1 = reader.read( "LINESTRING(-1 0,0 0)" );
+    g2 = reader.read( "GEOMETRYCOLLECTION(MULTIPOINT(-1 0),LINESTRING(0 -1,1 0))" );
+
+    pg1 = prep::PreparedGeometryFactory::prepare(g1.get());
+
+    ensure(  g1->intersects(g2.get()) );
+    ensure( pg1->intersects(g2.get()) );
+}
+
+

[mdavis]

The fix for this in GEOS 3.12 was in ​GEOS 774. I'll be backporting it to 3.11 and 3.10.

To be clear, this only affects mixed-type geometry collections with point elements. So the problem should be fairly rare in practice.

[Wenjing]

Oh, Sorry again for giving the wrong version.

You are right. The goes version is GEOS="3.11.3dev-CAPI-1.17.3"

I switched back to this version because crash #5595 is fixed on this version. I will be more careful next time.

Could you help take a look at 3DDistance​https://trac.osgeo.org/postgis/ticket/5589#comment:2 ticket?

[Wenjing]

Emmm, I am curious about why

SELECT ST_Intersects(a1, a2) FROM t As a1, t As a2 WHERE a1.id = 2 and a2.id = 3;
--{t}

gives the correct answer but

SELECT COUNT(*) FROM t As a1 JOIN t As a2 ON ST_Intersects(a1.geom, a2.geom)  WHERE a1.id <> a2.id;
--{5}

gives the wrong one in this version.

[mdavis]

The fix for this issue will be in GEOS 3.11.3 and GEOS 3.10.6.