Re: [postgis-users] strange behaviour in PostGIS strictly below operator?
> On 11 Aug 2017, at 16:30, Thijs van den Berg wrote:
>
>>
>> On 11 Aug 2017, at 16:28, Sandro Santilli 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@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] strange behaviour in PostGIS strictly below operator?
> On 11 Aug 2017, at 16:28, Sandro Santilli 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!
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] strange behaviour in PostGIS strictly below operator?
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;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] strange behaviour in PostGIS strictly below operator?
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)'));
> On 11 Aug 2017, at 16:03, Darafei Komяpa Praliaskouski
> wrote:
>
> Hi!
>
> These are bbox operations, and IIRC they operate on float (32bit) bbox of
> geometry. I think conversion of point to bbox adds some epsilon around a
> point.
>
> пт, 11 авг. 2017 г. в 16:57, Thijs van den Berg >:
> Hi All,
>
> I'm getting some strange results that I can’t explain. Maybe some of you know
> whats going on?
>
> I want to use PostGIS for spatial queries on scientific data (2d point sets).
> In the following query I want to select points to the left of some other
> point but it looks like there is some rounding going on? AFAIK the
> coordinates are stored as 64bit doubles and so this should be no problem?
>
> This query gives TRUE as expected:
> SELECT ST_PointFromText('POINT(88605254 0)') <<
> ST_PointFromText('POINT(88605300 0)’);
>
> This query gives FALSE,the only difference is two leading 2’s in the X
> coordinate.
> SELECT ST_PointFromText('POINT(2288605254 0)') <<
> ST_PointFromText('POINT(2288605300 0)');
>
>
> Below is my version info:
> SELECT PostGIS_full_version();
> POSTGIS="2.3.2 r15302" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15
> August 2016" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.9.4"
> LIBJSON="0.12.1” RASTER
>
> Kind regards,
> Thijs
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] strange behaviour in PostGIS strictly below operator?
Hi!
These are bbox operations, and IIRC they operate on float (32bit) bbox of
geometry. I think conversion of point to bbox adds some epsilon around a
point.
пт, 11 авг. 2017 г. в 16:57, Thijs van den Berg :
> Hi All,
>
> I'm getting some strange results that I can’t explain. Maybe some of you
> know whats going on?
>
> I want to use PostGIS for spatial queries on scientific data (2d point
> sets). In the following query I want to select points to the left of some
> other point but it looks like there is some rounding going on? AFAIK the
> coordinates are stored as 64bit doubles and so this should be no problem?
>
> This query gives TRUE as expected:
> SELECT ST_PointFromText('POINT(88605254 0)') <<
> ST_PointFromText('POINT(88605300 0)’);
>
> This query gives FALSE,the only difference is two leading 2’s in the X
> coordinate.
> SELECT ST_PointFromText('POINT(2288605254 0)') <<
> ST_PointFromText('POINT(2288605300 0)');
>
>
> Below is my version info:
> SELECT PostGIS_full_version();
> POSTGIS="2.3.2 r15302" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15
> August 2016" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.9.4"
> LIBJSON="0.12.1” RASTER
>
> Kind regards,
> Thijs
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] strange behaviour in PostGIS strictly below operator?
Hi All,
I'm getting some strange results that I can’t explain. Maybe some of you know
whats going on?
I want to use PostGIS for spatial queries on scientific data (2d point sets).
In the following query I want to select points to the left of some other point
but it looks like there is some rounding going on? AFAIK the coordinates are
stored as 64bit doubles and so this should be no problem?
This query gives TRUE as expected:
SELECT ST_PointFromText('POINT(88605254 0)') <<
ST_PointFromText('POINT(88605300 0)’);
This query gives FALSE,the only difference is two leading 2’s in the X
coordinate.
SELECT ST_PointFromText('POINT(2288605254 0)') <<
ST_PointFromText('POINT(2288605300 0)');
Below is my version info:
SELECT PostGIS_full_version();
POSTGIS="2.3.2 r15302" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15
August 2016" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.9.4"
LIBJSON="0.12.1” RASTER
Kind regards,
Thijs
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
