strange behaviour in PostGIS strictly below operator?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

strange behaviour in PostGIS strictly below operator?

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: strange behaviour in PostGIS strictly below operator?

Darafei "Komяpa" Praliaskouski
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 <[hidden email]>:
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: strange behaviour in PostGIS strictly below operator?

Thijs van den Berg
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 <[hidden email]> 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 <[hidden email]>:
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: strange behaviour in PostGIS strictly below operator?

Sandro Santilli-3
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: strange behaviour in PostGIS strictly below operator?

Thijs van den Berg

> On 11 Aug 2017, at 16:28, Sandro Santilli <[hidden email]> 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
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: strange behaviour in PostGIS strictly below operator?

Thijs van den Berg

On 11 Aug 2017, at 16:30, Thijs van den Berg <[hidden email]> wrote:


On 11 Aug 2017, at 16:28, Sandro Santilli <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...