[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] strange behaviour in PostGIS strictly below operator?
From: Thijs van den Berg <thijs () sitmo ! com>
Date: 2017-08-11 14:52:30
Message-ID: 7E8B595C-E1AA-4B80-905F-FDE69F1EBCD3 () sitmo ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
> On 11 Aug 2017, at 16:30, Thijs van den Berg <thijs@sitmo.com> wrote:
>
> >
> > On 11 Aug 2017, at 16:28, Sandro Santilli <strk@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@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
> > https://lists.osgeo.org/mailman/listinfo/postgis-users \
> > <https://lists.osgeo.org/mailman/listinfo/postgis-users>
[Attachment #5 (unknown)]
<html><head><meta http-equiv="Content-Type" content="text/html \
charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; \
-webkit-line-break: after-white-space;" class=""><br class=""><div><blockquote \
type="cite" class=""><div class="">On 11 Aug 2017, at 16:30, Thijs van den Berg \
<<a href="mailto:thijs@sitmo.com" class="">thijs@sitmo.com</a>> wrote:</div><br \
class="Apple-interchange-newline"><div class=""><blockquote type="cite" \
style="font-family: Helvetica; font-size: 12px; font-style: normal; \
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: \
auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; \
widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; \
-webkit-text-stroke-width: 0px;" class=""><br class="Apple-interchange-newline">On 11 \
Aug 2017, at 16:28, Sandro Santilli <<a href="mailto:strk@kbt.io" \
class="">strk@kbt.io</a>> wrote:<br class=""><br class="">On Fri, Aug 11, 2017 at \
04:16:44PM +0200, Thijs van den Berg wrote:<br class=""><blockquote type="cite" \
class="">Excellent!<span class="Apple-converted-space"> </span><br \
class="">Thanks for helping me understand, very clear.<span \
class="Apple-converted-space"> </span><br class=""><br class="">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.<br class=""><br class="">SELECT \
ST_X(ST_PointFromText('POINT(2288605254 0)')) < \
ST_X(ST_PointFromText('POINT(2288605300 0)'));<br class=""></blockquote><br \
class="">You can use the << operator on geometry to use the index and the \
<<br class="">operator on X to refine as a filter.<br class=""><br \
class="">EXPLAIN<br class="">SELECT g << ST_PointFromText('POINT(2288605300 \
0)')<br class=""> AND ST_X(g) < 2288605300<br class="">FROM \
test_table;<br class=""><br class="">—strk;<br class=""></blockquote><br \
style="font-family: Helvetica; font-size: 12px; font-style: normal; \
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: \
start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: \
0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; \
font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; \
letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; \
white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; \
display: inline !important;" class="">Thanks Sandro!</span><br style="font-family: \
Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; \
font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; word-spacing: 0px; \
-webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; \
font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; \
letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; \
white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" \
class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; \
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: \
start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: \
0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" \
class="">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!</span><br \
style="font-family: Helvetica; font-size: 12px; font-style: normal; \
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: \
start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: \
0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; \
font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; \
letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; \
white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" \
class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; \
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: \
start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: \
0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" \
class="">A great mailing list this is. Thanks both!</span><br style="font-family: \
Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; \
font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; word-spacing: 0px; \
-webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; \
font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; \
letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; \
white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" \
class=""></div></blockquote><div><br class=""></div>Turns out that the "@contains" \
operator uses double precision for bounding boxes. So I can do this (the makeLine \
creates a bounding box):</div><div><br class=""></div><div><div>SELECT \
ST_PointFromText('POINT(2288605254 0)') @ ST_MakeLine(ST_MakePoint(0,-1), \
ST_MakePoint(2288605300,1))</div><div><br class=""></div><div><br \
class=""></div><blockquote type="cite" class=""><div class=""><blockquote type="cite" \
style="font-family: Helvetica; font-size: 12px; font-style: normal; \
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: \
auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; \
widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; \
-webkit-text-stroke-width: 0px;" \
class="">_______________________________________________<br class="">postgis-users \
mailing list<br class=""><a href="mailto:postgis-users@lists.osgeo.org" \
class="">postgis-users@lists.osgeo.org</a><br class=""><a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" \
class="">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div></blockquote></div><br \
class=""></body></html>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic