Re: [postgis-users] st_intersects and st_disjoint inconsistent results
Lucky me! Thanks for the confirmation. Emily On 2022-02-11 2:19 p.m., Paul Ramsey wrote: This issue has been confirmed on both GEOS and JTS. You have magic geometry that breaks the consistency between prepared and standard results. https://github.com/libgeos/geos/pull/566 P. On Feb 10, 2022, at 6:43 PM, Emily Gouge wrote: Here you go. Thanks! SELECT ST_AsHEXEWKB (geometry) FROM test.eflowpath WHERE id = '889105be-5782-43f1-b50c-5a5825c83875' 01022009120700642F25DC75A24CC0E4DE5740FCB34840A7CEFE9B72A24CC09DA85B2CFBB34840B5519D0E64A24CC091FAA188FBB34840FA449E245DA24CC054C2137AFDB34840F4ACFFCE51A24CC09FEB562A03B448405328C1D144A24CC09A3DD00A0CB44840404C10C03CA24CC0EA07FE6910B44840 SELECT ST_AsHEXEWKB (geometry) FROM test.eflowpath WHERE id = 'e5703673-a995-472e-b4b4-0280143eba0c' 01022009120E0004BE47A23CA24CC098A1F14410B448409871AEBC3FA24CC078341F2114B448400858AB764DA24CC09D0546031DB448406BFD3E2D50A24CC0BEDDEDD522B4484004824AA654A24CC02DC9A60128B44840EE377FB850A24CC0FA18BD642DB44840CCAF8B474EA24CC02CCCE78134B44840D7158E7B4EA24CC01D7C17A53AB44840ACFA01B452A24CC02688BA0F40B44840DB508C8752A24CC006CDF80846B44840A1F31ABB44A24CC0C891730756B44840009AF7EE45A24CC06B7649415CB448408C2ECAC749A24CC0CE57248161B44840A74302A150A24CC07DD00E1368B44840 On 2022-02-10 5:57 p.m., Paul Ramsey wrote: On Feb 10, 2022, at 4:55 PM, Emily Gouge wrote: 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.1) 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? The fact that a single test case returns one result, but results in a larger set returns another says to me that likely the issue in different code lines because of
Re: [postgis-users] st_intersects and st_disjoint inconsistent results
It seems to be predicate robustness failure week! The geometries in this recent post show the same problem (in JTS, and probably GEOS too). https://lists.osgeo.org/pipermail/postgis-users/2022-February/045255.html A: LINESTRING (-29796.696826656284 138522.76848210802, -29804.3911369969 138519.3504205817) B: LINESTRING (-29802.795222153436 138520.05937757515, -29802.23305474065 138518.7938969792) A.disjoint(B) = TRUE A.preparedIntersects(B) = TRUE (Note: the post issue is slightly different - it just happens that the data shows this problem) On Fri, Feb 11, 2022 at 2:19 PM Paul Ramsey wrote: > This issue has been confirmed on both GEOS and JTS. You have magic > geometry that breaks the consistency between prepared and standard results. > > https://github.com/libgeos/geos/pull/566 > > P. > > > On Feb 10, 2022, at 6:43 PM, Emily Gouge wrote: > > > > Here you go. Thanks! > > > > > > SELECT ST_AsHEXEWKB (geometry) FROM test.eflowpath WHERE id = > '889105be-5782-43f1-b50c-5a5825c83875' > > > > > 01022009120700642F25DC75A24CC0E4DE5740FCB34840A7CEFE9B72A24CC09DA85B2CFBB34840B5519D0E64A24CC091FAA188FBB34840FA449E245DA24CC054C2137AFDB34840F4ACFFCE51A24CC09FEB562A03B448405328C1D144A24CC09A3DD00A0CB44840404C10C03CA24CC0EA07FE6910B44840 > > > > SELECT ST_AsHEXEWKB (geometry) FROM test.eflowpath WHERE id = > 'e5703673-a995-472e-b4b4-0280143eba0c' > > > > > 01022009120E0004BE47A23CA24CC098A1F14410B448409871AEBC3FA24CC078341F2114B448400858AB764DA24CC09D0546031DB448406BFD3E2D50A24CC0BEDDEDD522B4484004824AA654A24CC02DC9A60128B44840EE377FB850A24CC0FA18BD642DB44840CCAF8B474EA24CC02CCCE78134B44840D7158E7B4EA24CC01D7C17A53AB44840ACFA01B452A24CC02688BA0F40B44840DB508C8752A24CC006CDF80846B44840A1F31ABB44A24CC0C891730756B44840009AF7EE45A24CC06B7649415CB448408C2ECAC749A24CC0CE57248161B44840A74302A150A24CC07DD00E1368B44840 > > > > On 2022-02-10 5:57 p.m., Paul Ramsey wrote: > >>> On Feb 10, 2022, at 4:55 PM, Emily Gouge > wrote: > >>> > >>> 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.1) > >>> 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
Re: [postgis-users] st_intersects and st_disjoint inconsistent results
This issue has been confirmed on both GEOS and JTS. You have magic geometry that breaks the consistency between prepared and standard results. https://github.com/libgeos/geos/pull/566 P. > On Feb 10, 2022, at 6:43 PM, Emily Gouge wrote: > > Here you go. Thanks! > > > SELECT ST_AsHEXEWKB (geometry) FROM test.eflowpath WHERE id = > '889105be-5782-43f1-b50c-5a5825c83875' > > 01022009120700642F25DC75A24CC0E4DE5740FCB34840A7CEFE9B72A24CC09DA85B2CFBB34840B5519D0E64A24CC091FAA188FBB34840FA449E245DA24CC054C2137AFDB34840F4ACFFCE51A24CC09FEB562A03B448405328C1D144A24CC09A3DD00A0CB44840404C10C03CA24CC0EA07FE6910B44840 > > SELECT ST_AsHEXEWKB (geometry) FROM test.eflowpath WHERE id = > 'e5703673-a995-472e-b4b4-0280143eba0c' > > 01022009120E0004BE47A23CA24CC098A1F14410B448409871AEBC3FA24CC078341F2114B448400858AB764DA24CC09D0546031DB448406BFD3E2D50A24CC0BEDDEDD522B4484004824AA654A24CC02DC9A60128B44840EE377FB850A24CC0FA18BD642DB44840CCAF8B474EA24CC02CCCE78134B44840D7158E7B4EA24CC01D7C17A53AB44840ACFA01B452A24CC02688BA0F40B44840DB508C8752A24CC006CDF80846B44840A1F31ABB44A24CC0C891730756B44840009AF7EE45A24CC06B7649415CB448408C2ECAC749A24CC0CE57248161B44840A74302A150A24CC07DD00E1368B44840 > > On 2022-02-10 5:57 p.m., Paul Ramsey wrote: >>> On Feb 10, 2022, at 4:55 PM, Emily Gouge wrote: >>> >>> 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.1) >>> 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
Re: [postgis-users] st_intersects and st_disjoint inconsistent results
Here you go. Thanks! SELECT ST_AsHEXEWKB (geometry) FROM test.eflowpath WHERE id = '889105be-5782-43f1-b50c-5a5825c83875' 01022009120700642F25DC75A24CC0E4DE5740FCB34840A7CEFE9B72A24CC09DA85B2CFBB34840B5519D0E64A24CC091FAA188FBB34840FA449E245DA24CC054C2137AFDB34840F4ACFFCE51A24CC09FEB562A03B448405328C1D144A24CC09A3DD00A0CB44840404C10C03CA24CC0EA07FE6910B44840 SELECT ST_AsHEXEWKB (geometry) FROM test.eflowpath WHERE id = 'e5703673-a995-472e-b4b4-0280143eba0c' 01022009120E0004BE47A23CA24CC098A1F14410B448409871AEBC3FA24CC078341F2114B448400858AB764DA24CC09D0546031DB448406BFD3E2D50A24CC0BEDDEDD522B4484004824AA654A24CC02DC9A60128B44840EE377FB850A24CC0FA18BD642DB44840CCAF8B474EA24CC02CCCE78134B44840D7158E7B4EA24CC01D7C17A53AB44840ACFA01B452A24CC02688BA0F40B44840DB508C8752A24CC006CDF80846B44840A1F31ABB44A24CC0C891730756B44840009AF7EE45A24CC06B7649415CB448408C2ECAC749A24CC0CE57248161B44840A74302A150A24CC07DD00E1368B44840 On 2022-02-10 5:57 p.m., Paul Ramsey wrote: On Feb 10, 2022, at 4:55 PM, Emily Gouge wrote: 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.1) 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? The fact that a single test case returns one result, but results in a larger set returns another says to me that likely the issue in different code lines because of different cache behaviour. When you do the single test case you get a brute force intersects. When you do several in a batch, you get prepared geometry (at least, for the cases that happen after caching). If you can provide the HEXWKB of the two geometries that showed disagreement (where b.id =
Re: [postgis-users] st_intersects and st_disjoint inconsistent results
> On Feb 10, 2022, at 4:55 PM, Emily Gouge wrote: > > 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.1) > 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? The fact that a single test case returns one result, but results in a larger set returns another says to me that likely the issue in different code lines because of different cache behaviour. When you do the single test case you get a brute force intersects. When you do several in a batch, you get prepared geometry (at least, for the cases that happen after caching). If you can provide the HEXWKB of the two geometries that showed disagreement (where b.id = '889105be-5782-43f1-b50c-5a5825c83875' and a.id = 'e5703673-a995-472e-b4b4-0280143eba0c') we can set up a test case in GEOS that compares the normal and prepared geometry calls and see if that's the problem. P. > > Thanks, > Emily > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] st_intersects and st_disjoint inconsistent results
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.1) 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 ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
