[postgis-users] st_intersects and st_disjoint inconsistent results

[Emily Gouge]

I have a linear dataset on which I was building a query to find edges 
that are “very close” but don’t touch. While working on this query I 
found some unexpected results with the st_intersects and st_disjoint 
functions. As outlined below, the query returned true for both 
st_instersects and st_disjoint for a few geometries comparisons, but 
ONLY when a where clause was used to filter the geometries spatially. 
When unique identifiers were used to filter geometries only st_disjoint 
returns true.

Versions:
Except where noted otherwise the results below reference testing on 
these versions:
POSTGIS="3.2.0 3.2.0" [EXTENSION] PGSQL="140" GEOS="3.10.1-CAPI-1.16.0" 
PROJ="7.2.1" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" 
WAGYU="0.5.0 (Internal)"
PostgreSQL 14.1, compiled by Visual C++ build 1914, 64-bit

When I run this query:
select a.id, b.id,
st_intersects(a.geometry, b.geometry),
st_intersects(b.geometry, a.geometry),
st_disjoint(a.geometry, b.geometry),
st_disjoint(b.geometry, a.geometry)
from test.eflowpath a, test.eflowpath b
where a.id != b.id
and st_dwithin(a.geometry, b.geometry, 0.00001)
and st_disjoint(a.geometry, b.geometry);

PostGIS 3.2: 50 rows were returned, but there are three rows that return 
true for both st_disjoint and st_intersects. Given the query this in 
itself is a bit odd as you'd expect reciprocal results for the pairs of 
the geometry (so at least 4 rows).
-------
889105be-5782-43f1-b50c-5a5825c83875
e5703673-a995-472e-b4b4-0280143eba0c
true
true
true
true
-------
0d05aabb-9ff3-4d8f-b6c7-b2b44b0868c8
3a09b2af-5932-4e36-9e3e-d8109e5463fa
true
true
true
true
-------
3a09b2af-5932-4e36-9e3e-d8109e5463fa
0d05aabb-9ff3-4d8f-b6c7-b2b44b0868c8
true
true
true
true
-------
...

Note: In PostGIS 3.1.2 50 rows were returned by only two rows returned 
true for both disjoint and intersects.

HOWEVER,
When I compare one pair of those edges specifically using the ids they 
are only disjoint (which is the result I would expect to see)

select a.id, b.id,
st_intersects(a.geometry, b.geometry),
st_intersects(b.geometry, a.geometry),
st_disjoint(a.geometry, b.geometry),
st_disjoint(b.geometry, a.geometry)
from test.eflowpath a, test.eflowpath b
where b.id = '889105be-5782-43f1-b50c-5a5825c83875' and a.id = 
'e5703673-a995-472e-b4b4-0280143eba0c'

Results:
-------
e5703673-a995-472e-b4b4-0280143eba0c
889105be-5782-43f1-b50c-5a5825c83875
false
false
true
true
-------

I thought perhaps this had something to do with the indexes so I removed 
all geometry indexes from the table and re-ran the initial query. In 
postgis 3.2 this returned the same results as the indexed query.
Note: In PostGIS 3.1.2 this also returned three rows with intersects and 
disjoint true. As noted above with indexes in 3.1.2 only 2 rows were 
returned where intersects and disjoint were true.

Similar results occurred if st_dwithin from the where statement was 
increased to 0.01.

Test Data: When I made a table with only the edges in question all 
queries returned expected results: st_intersects is false and 
st_disjoint is true. As a result providing a small test case for this 
issue doesn’t seem possible. But I am happy to provide all the data - 
there are 27,444 rows.

While this isn’t a problem for me, I find it unexpected that the results 
from st_intersects and st_disjoint of two geometries would be different 
based on the where clause in the query (and the data in the table).

Thoughts?

Thanks,
Emily