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

Wed Dec 5 02:32:17 PST 2018

Hi,

reproduced with
POSTGIS="2.4.1 r16012" PGSQL="100" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel.
4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1"
LIBJSON="0.11.99" TOPOLOGY RASTER

Dumb thing but I tried  to invert the types between the column definition
and the cast and it yields the same results (resp. 3 rows with cast, 5
without).

Le mer. 5 déc. 2018 à 09:58, Raúl Marín Rodríguez <rmrodriguez at carto.com> a
écrit :

> 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
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20181205/d95cfc12/attachment-0001.html>