[postgis-devel] Unpredictable results with bigint and invalid (vertical) polygonz

[Tom van Tilburg]

Considering the following data and query:
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
a.cid,
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_Intersects(a.geom, b.geom)

it gives me 3 records when using `a.blockid::int = 720`and 5 records when
using `a.blockid = 720`

I have the following additional observations:
- Both answers are incorrect because all 3 planes are intersecting in 2D
space.
- Plane with cid 10890 is on purpose vertical and therefore seen as
'invalid' by GEOS
- When replacing the ST_Intersects with ST_3DIntersects, I get 4 records,
which is correct.
- Removing the Intersects function completele yields a predictible 6
records, regardless of casting the blockid to integer
- This is part of a much larger query on similar geometries. Most of the
time the results are correct but not always the same, depending how I
select the data (ie. `blockid = 720` is not the same as `blockid > 719 AND
blockid < 721`)

Obviously, it is strange that casting the selector ID from bigint to int
has an influence on the outcome of ST_Intersects. I notive however that the
cast results in a reordering of the data, leading to the data sorted in the
order of the right table (b).

I am lost at how to further triage this problem. Anyone an idea on how to
dig further?
Can people confirm this on other versions?

For completeness:
SELECT postgis_full_version();
"POSTGIS="2.5.0 r16836" [EXTENSION] PGSQL="100" GEOS="3.7.0-CAPI-1.11.0
673b9939" SFCGAL="1.3.5" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL
2.4.0dev-4b763dd896-dirty, released 2018/11/19" LIBXML="2.9.4"
LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER"

Best,
 Tom
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20181204/d689fa15/attachment.html>