<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Hi,<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">I tried and confirm</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"> <span style="font-family:monospace,monospace">SELECT postgis_full_version();</span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"> 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></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">....<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">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></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">...<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">AND a.blockid = 720<br>AND ST_Intersects(a.geom, b.geom);</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"> 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></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">I do ignore what is wrong</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div></div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr">On Tue, Dec 4, 2018 at 8:32 AM Tom van Tilburg <<a href="mailto:tom.van.tilburg@gmail.com">tom.van.tilburg@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">Considering the following data and query:<br></div><span style="font-family:monospace,monospace">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)</span></div><div dir="ltr"><br><div dir="ltr">
it gives
me 3 records when using
`a.blockid::int = 720`and 5 records when using
`a.blockid = 720`</div><div dir="ltr"><br></div><div>I have the following additional observations:<br>
</div>- Both answers are incorrect because all 3 planes are intersecting in 2D space.</div><div>- Plane with cid 10890 is on purpose vertical and therefore seen as 'invalid' by GEOS<br></div><div dir="ltr">- When replacing the ST_Intersects with ST_3DIntersects, I get 4 records, which is correct.</div><div>- Removing the Intersects function completele yields a predictible 6 records, regardless of casting the blockid to integer</div><div>- 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></div><div dir="ltr"><br></div><div>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></div><div><br></div><div>I am lost at how to further triage this problem. Anyone an idea on how to dig further?</div><div>Can people confirm this on other versions?<br></div><div><br></div><div>For completeness:</div><div><span style="font-family:monospace,monospace">SELECT postgis_full_version();<br></span></div><div><span style="font-family:monospace,monospace">"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"</span></div><div><br></div><div>Best,</div><div> Tom<br></div></div></div></div>
_______________________________________________<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><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><pre>Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany
Vicky Vergara
Operations Research
eMail: vicky@<a href="http://georepublic.de" target="_blank">georepublic.de</a>
Web: <a href="https://georepublic.info" target="_blank">https://georepublic.info</a>
Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9
Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
<span></span></pre></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>