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

Tue Dec 4 17:23:52 PST 2018

Hi,
I tried and confirm

SELECT postgis_full_version();

postgis_full_version

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.4.4 r16526" PGSQL="95" GEOS="3.6.2-CAPI-1.10.2 4d2925d6"
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20"
LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER
(1 row)

....
AND a.blockid::int = 720
AND ST_Intersects(a.geom, b.geom);
 cid | blockid | blockid
-----+---------+---------
 316 |     720 |     720
 134 |     720 |     720
 134 |     720 |     720

...
AND a.blockid = 720
AND ST_Intersects(a.geom, b.geom);
  cid  | blockid | blockid
-------+---------+---------
   134 |     720 |     720
   134 |     720 |     720
   316 |     720 |     720
   316 |     720 |     720
 10890 |     720 |     720
(5 rows)

I do ignore what is wrong

On Tue, Dec 4, 2018 at 8:32 AM Tom van Tilburg <tom.van.tilburg at gmail.com>
wrote:

> 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
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel

-- 

Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky at georepublic.de
Web: https://georepublic.info

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20181204/50c1583d/attachment.html>