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

Wed Dec 5 00:57:57 PST 2018

Hi,

Not sure if this is an issue in Postgresql or Postgis, but it's definitely a bug
which manifest depending on the plan.

I've created https://trac.osgeo.org/postgis/ticket/4269 to track it.
On Wed, Dec 5, 2018 at 2:24 AM Vicky Vergara <vicky at georepublic.de> wrote:
>
> 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
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel

-- 
Raúl Marín Rodríguez
carto.com