[postgis-tickets] [PostGIS] #4269: Inconsistent result for ST_Intersects depending on the plan
PostGIS
trac at osgeo.org
Mon Dec 10 01:14:23 PST 2018
#4269: Inconsistent result for ST_Intersects depending on the plan
-------------------------+---------------------------
Reporter: Algunenano | Owner: pramsey
Type: defect | Status: new
Priority: critical | Milestone: PostGIS 2.4.7
Component: postgis | Version: 2.5.x
Resolution: | Keywords:
-------------------------+---------------------------
Comment (by robe):
I thought when ST_IsValid is false the intersection is really unknown?
Anyway it does seem like the caching is at fault here. ST_DWithin returns
all true as expected, but the ST_Intersects flip answers depending on the
order the data is traversed
{{{
WITH data AS (
SELECT 134 as cid,720::bigint as blockid, ST_GeomFromText('POLYGON Z
((122427.759 489209.498 14.5066901703871,122395.957 489222.943
14.3997094723791,122399.649 489231.676 4.98556790825181,122431.452
489218.231 5.09216481886026,122427.759 489209.498 14.5066901703871))')
geom
UNION ALL
SELECT 316, 720, ST_GeomFromText('POLYGON Z ((122427.027 489216.053
5.97863674861823,122419.873 489219.077 6.03923989893278,122421.6
489223.163 1.83027232084998,122428.754 489220.138
1.77054008712955,122427.027 489216.053 5.97863674861823))')
UNION ALL
SELECT 10890, 720, ST_GeomFromText('POLYGON Z ((122422.267 489220.741
0,122424.396 489219.847 0,122424.396 489219.847 30,122422.267 489220.741
30,122422.267 489220.741 0))')
)
SELECT ST_DWithin(a.geom, b.geom,0), ST_Intersects(a.geom,b.geom),
ST_Distance(a.geom, b.geom), ST_ISValid(a.geom) AS isvalid_a,
ST_IsValid(b.geom) AS isvalid_b,
a.cid AS acid, b.cid As bcid,
a.blockid,b.blockid
FROM data a, data b
WHERE a.cid != b.cid
AND a.blockid = b.blockid
AND a.blockid::int = 720
AND ST_DWithin(a.geom, b.geom,0);
}}}
Gives me true for all 6 for ST_DWithin but for ST_Intersects the 3 that
have invalid geoms
would be filtered out
{{{
st_dwithin | st_intersects | st_distance | isvalid_a | isvalid_b | acid
| bcid | blockid | blockid
------------+---------------+-------------+-----------+-----------+-------+-------+---------+---------
t | f | 0 | f | t | 10890
| 134 | 720 | 720
t | t | 0 | t | t | 316
| 134 | 720 | 720
t | f | 0 | f | t | 10890
| 316 | 720 | 720
t | t | 0 | t | t | 134
| 316 | 720 | 720
t | f | 0 | t | f | 316
| 10890 | 720 | 720
t | t | 0 | t | f | 134
| 10890 | 720 | 720
(6 rows)
}}}
{{{
WITH data AS (
SELECT 134 as cid,720::bigint as blockid, ST_GeomFromText('POLYGON Z
((122427.759 489209.498 14.5066901703871,122395.957 489222.943
14.3997094723791,122399.649 489231.676 4.98556790825181,122431.452
489218.231 5.09216481886026,122427.759 489209.498 14.5066901703871))')
geom
UNION ALL
SELECT 316, 720, ST_GeomFromText('POLYGON Z ((122427.027 489216.053
5.97863674861823,122419.873 489219.077 6.03923989893278,122421.6
489223.163 1.83027232084998,122428.754 489220.138
1.77054008712955,122427.027 489216.053 5.97863674861823))')
UNION ALL
SELECT 10890, 720, ST_GeomFromText('POLYGON Z ((122422.267 489220.741
0,122424.396 489219.847 0,122424.396 489219.847 30,122422.267 489220.741
30,122422.267 489220.741 0))')
)
SELECT ST_DWithin(a.geom, b.geom,0), ST_Intersects(a.geom,b.geom),
ST_Distance(a.geom, b.geom), ST_IsValid(a.geom) AS isvalid_a,
ST_IsValid(b.geom) AS isvalid_b,
a.cid AS acid, b.cid AS bcid,
a.blockid,b.blockid
FROM data a, data b
WHERE a.cid != b.cid
AND a.blockid = b.blockid
AND a.blockid = 720
AND ST_DWithin(a.geom, b.geom,0);
}}}
Gives me true for all 6 for ST_DWithin and filters out 1 with invalid
{{{
st_dwithin | st_intersects | st_distance | isvalid_a | isvalid_b | acid
| bcid | blockid | blockid
------------+---------------+-------------+-----------+-----------+-------+-------+---------+---------
t | t | 0 | t | t | 134
| 316 | 720 | 720
t | t | 0 | t | f | 134
| 10890 | 720 | 720
t | t | 0 | t | t | 316
| 134 | 720 | 720
t | t | 0 | t | f | 316
| 10890 | 720 | 720
t | f | 0 | f | t | 10890
| 134 | 720 | 720
t | t | 0 | f | t | 10890
| 316 | 720 | 720
(6 rows)
}}}
I thought maybe the issue was because prepared geometry is assuming
ST_Intersects is a commutative operation. In the case of invalid
geometries maybe it's not.
though doing a standalone and flipping the order of the geometries, I get
false in both cases
So this:
{{{
SELECT ST_Intersects('POLYGON Z ((122422.267 489220.741 0,122424.396
489219.847 0,122424.396 489219.847 30,122422.267 489220.741 30,122422.267
489220.741 0))'::geometry,
'POLYGON Z ((122427.027 489216.053
5.97863674861823,122419.873 489219.077 6.03923989893278,122421.6
489223.163 1.83027232084998,122428.754 489220.138
1.77054008712955,122427.027 489216.053 5.97863674861823))'::geometry
);
returns:
false
}}}
{{{
SELECT ST_Intersects('POLYGON Z ((122427.027 489216.053
5.97863674861823,122419.873 489219.077 6.03923989893278,122421.6
489223.163 1.83027232084998,122428.754 489220.138
1.77054008712955,122427.027 489216.053 5.97863674861823))'::geometry ,
'POLYGON Z ((122422.267 489220.741 0,122424.396 489219.847 0,122424.396
489219.847 30,122422.267 489220.741 30,122422.267 489220.741
0))'::geometry
);
returns: false
}}}
So not sure how it's getting a true for:
134 | 10890
and when I explicitly ask for the ids, I get the expected false answer for
both regardless if I cast or don't cast the integer
{{{
WITH data AS (
SELECT 134 as cid,720::bigint as blockid, ST_GeomFromText('POLYGON Z
((122427.759 489209.498 14.5066901703871,122395.957 489222.943
14.3997094723791,122399.649 489231.676 4.98556790825181,122431.452
489218.231 5.09216481886026,122427.759 489209.498 14.5066901703871))')
geom
UNION ALL
SELECT 316, 720, ST_GeomFromText('POLYGON Z ((122427.027 489216.053
5.97863674861823,122419.873 489219.077 6.03923989893278,122421.6
489223.163 1.83027232084998,122428.754 489220.138
1.77054008712955,122427.027 489216.053 5.97863674861823))')
UNION ALL
SELECT 10890, 720, ST_GeomFromText('POLYGON Z ((122422.267 489220.741
0,122424.396 489219.847 0,122424.396 489219.847 30,122422.267 489220.741
30,122422.267 489220.741 0))')
)
SELECT ST_DWithin(a.geom, b.geom,0), ST_Intersects(a.geom,b.geom),
ST_Distance(a.geom, b.geom), ST_ISValid(a.geom) AS isvalid_a,
ST_IsValid(b.geom) AS isvalid_b,
a.cid AS acid, b.cid As bcid,
a.blockid,b.blockid
FROM data a, data b
WHERE a.cid != b.cid
AND a.blockid = b.blockid
AND a.blockid::int = 720
AND ( (a.cid = 134 AND b.cid =10890)
OR (a.cid = 10890 and b.cid = 134) );
st_dwithin | st_intersects | st_distance | isvalid_a | isvalid_b | acid
| bcid | blockid | blockid
------------+---------------+-------------+-----------+-----------+-------+-------+---------+---------
t | f | 0 | t | f | 134
| 10890 | 720 | 720
t | f | 0 | f | t | 10890
| 134 | 720 | 720
(2 rows)
}}}
{{{
WITH data AS (
SELECT 134 as cid,720::bigint as blockid, ST_GeomFromText('POLYGON Z
((122427.759 489209.498 14.5066901703871,122395.957 489222.943
14.3997094723791,122399.649 489231.676 4.98556790825181,122431.452
489218.231 5.09216481886026,122427.759 489209.498 14.5066901703871))')
geom
UNION ALL
SELECT 316, 720, ST_GeomFromText('POLYGON Z ((122427.027 489216.053
5.97863674861823,122419.873 489219.077 6.03923989893278,122421.6
489223.163 1.83027232084998,122428.754 489220.138
1.77054008712955,122427.027 489216.053 5.97863674861823))')
UNION ALL
SELECT 10890, 720, ST_GeomFromText('POLYGON Z ((122422.267 489220.741
0,122424.396 489219.847 0,122424.396 489219.847 30,122422.267 489220.741
30,122422.267 489220.741 0))')
)
SELECT ST_DWithin(a.geom, b.geom,0), ST_Intersects(a.geom,b.geom),
ST_Distance(a.geom, b.geom), ST_ISValid(a.geom) AS isvalid_a,
ST_IsValid(b.geom) AS isvalid_b,
a.cid AS acid, b.cid As bcid,
a.blockid,b.blockid
FROM data a, data b
WHERE a.cid != b.cid
AND a.blockid = b.blockid
AND a.blockid = 720
AND ( (a.cid = 134 AND b.cid =10890)
OR (a.cid = 10890 and b.cid = 134) );
st_dwithin | st_intersects | st_distance | isvalid_a | isvalid_b | acid
| bcid | blockid | blockid
------------+---------------+-------------+-----------+-----------+-------+-------+---------+---------
t | f | 0 | t | f | 134
| 10890 | 720 | 720
t | f | 0 | f | t | 10890
| 134 | 720 | 720
(2 rows)
}}}
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4269#comment:7>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.
More information about the postgis-tickets
mailing list