<div dir="ltr"><div dir="ltr"><div>Hi,</div><div><br></div><div>reproduced with</div><div>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</div><div><br></div><div>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).<br></div></div></div><br><div class="gmail_quote"><div dir="ltr">Le mer. 5 déc. 2018 à 09:58, Raúl Marín Rodríguez <<a href="mailto:rmrodriguez@carto.com">rmrodriguez@carto.com</a>> a écrit :<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
Not sure if this is an issue in Postgresql or Postgis, but it's definitely a bug<br>
which manifest depending on the plan.<br>
<br>
I've created <a href="https://trac.osgeo.org/postgis/ticket/4269" rel="noreferrer" target="_blank">https://trac.osgeo.org/postgis/ticket/4269</a> to track it.<br>
On Wed, Dec 5, 2018 at 2:24 AM Vicky Vergara <<a href="mailto:vicky@georepublic.de" target="_blank">vicky@georepublic.de</a>> wrote:<br>
><br>
> Hi,<br>
> I tried and confirm<br>
><br>
> SELECT postgis_full_version();<br>
><br>
> postgis_full_version<br>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
> 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<br>
> (1 row)<br>
><br>
> ....<br>
> AND a.blockid::int = 720<br>
> AND ST_Intersects(a.geom, b.geom);<br>
> cid | blockid | blockid<br>
> -----+---------+---------<br>
> 316 | 720 | 720<br>
> 134 | 720 | 720<br>
> 134 | 720 | 720<br>
><br>
> ...<br>
> AND a.blockid = 720<br>
> AND ST_Intersects(a.geom, b.geom);<br>
> cid | blockid | blockid<br>
> -------+---------+---------<br>
> 134 | 720 | 720<br>
> 134 | 720 | 720<br>
> 316 | 720 | 720<br>
> 316 | 720 | 720<br>
> 10890 | 720 | 720<br>
> (5 rows)<br>
><br>
> I do ignore what is wrong<br>
><br>
><br>
> On Tue, Dec 4, 2018 at 8:32 AM Tom van Tilburg <<a href="mailto:tom.van.tilburg@gmail.com" target="_blank">tom.van.tilburg@gmail.com</a>> wrote:<br>
>><br>
>> Considering the following data and query:<br>
>> WITH data AS (<br>
>> 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<br>
>> UNION ALL<br>
>> 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))')<br>
>> UNION ALL<br>
>> 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))')<br>
>> )<br>
>> SELECT<br>
>> a.cid,<br>
>> a.blockid,b.blockid<br>
>> FROM data a, data b<br>
>> WHERE a.cid != b.cid<br>
>> AND a.blockid = b.blockid<br>
>> AND a.blockid::int = 720<br>
>> AND ST_Intersects(a.geom, b.geom)<br>
>><br>
>> it gives me 3 records when using `a.blockid::int = 720`and 5 records when using `a.blockid = 720`<br>
>><br>
>> I have the following additional observations:<br>
>> - Both answers are incorrect because all 3 planes are intersecting in 2D space.<br>
>> - Plane with cid 10890 is on purpose vertical and therefore seen as 'invalid' by GEOS<br>
>> - When replacing the ST_Intersects with ST_3DIntersects, I get 4 records, which is correct.<br>
>> - Removing the Intersects function completele yields a predictible 6 records, regardless of casting the blockid to integer<br>
>> - 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`)<br>
>><br>
>> 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).<br>
>><br>
>> I am lost at how to further triage this problem. Anyone an idea on how to dig further?<br>
>> Can people confirm this on other versions?<br>
>><br>
>> For completeness:<br>
>> SELECT postgis_full_version();<br>
>> "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"<br>
>><br>
>> Best,<br>
>> Tom<br>
>> _______________________________________________<br>
>> postgis-devel mailing list<br>
>> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
><br>
><br>
><br>
> --<br>
><br>
> Georepublic UG (haftungsbeschränkt)<br>
> Salzmannstraße 44,<br>
> 81739 München, Germany<br>
><br>
> Vicky Vergara<br>
> Operations Research<br>
><br>
> eMail: <a href="mailto:vicky@georepublic.de" target="_blank">vicky@georepublic.de</a><br>
> Web: <a href="https://georepublic.info" rel="noreferrer" target="_blank">https://georepublic.info</a><br>
><br>
> Tel: +49 (089) 4161 7698-1<br>
> Fax: +49 (089) 4161 7698-9<br>
><br>
> Commercial register: Amtsgericht München, HRB 181428<br>
> CEO: Daniel Kastl<br>
><br>
> _______________________________________________<br>
> postgis-devel mailing list<br>
> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
<br>
<br>
<br>
-- <br>
Raúl Marín Rodríguez<br>
<a href="http://carto.com" rel="noreferrer" target="_blank">carto.com</a><br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>