[postgis-users] strange behaviour in PostGIS strictly below operator?

[Thijs van den Berg]

> On 11 Aug 2017, at 16:30, Thijs van den Berg <thijs at sitmo.com> wrote:
> 
>> 
>> On 11 Aug 2017, at 16:28, Sandro Santilli <strk at kbt.io> wrote:
>> 
>> On Fri, Aug 11, 2017 at 04:16:44PM +0200, Thijs van den Berg wrote:
>>> Excellent! 
>>> Thanks for helping me understand, very clear. 
>>> 
>>> That makes me think there is no way around this, considering I want/need to use GIST indices to speedup these type of queries? If I did the following type of query (which *does* give the intended result) then it looks like it’s doing a full table scan instead of using the GIST.
>>> 
>>> SELECT ST_X(ST_PointFromText('POINT(2288605254 0)')) < ST_X(ST_PointFromText('POINT(2288605300 0)'));
>> 
>> You can use the << operator on geometry to use the index and the <
>> operator on X to refine as a filter.
>> 
>> EXPLAIN
>> SELECT g << ST_PointFromText('POINT(2288605300 0)')
>>    AND ST_X(g) < 2288605300
>> FROM test_table;
>> 
>> —strk;
> 
> Thanks Sandro!
> 
> That’s what I’ll do, I was thinking along those lines. I would need to use &< to include the rounding error, ..and then I think it will work!
> 
> A great mailing list this is. Thanks both!
> 

Turns out that the “@contains" operator uses double precision for bounding boxes. So I can do this (the makeLine creates a bounding box):

SELECT ST_PointFromText('POINT(2288605254 0)') @ ST_MakeLine(ST_MakePoint(0,-1), ST_MakePoint(2288605300,1))


>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>> https://lists.osgeo.org/mailman/listinfo/postgis-users <https://lists.osgeo.org/mailman/listinfo/postgis-users>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170811/bcaa074c/attachment.html>