[postgis-users] 3D index in postgis

[Kevin Neufeld]

Paul Ramsey wrote:
> On Tue, May 20, 2008 at 8:40 AM, Kevin Neufeld <kneufeld at refractions.net> wrote:
>> a1001800 wrote:
>>> Thanks Kevin,
>>>
>>> It looks like ~= not invoke the index.
>> That's why I suggested the && operator as well.  It does use the index.
>>
>>> Do we have a way to deal with an index with third value?
>>> For example, point (x, y) and userid
>>>
>>> I need to do a query like "select point(x,y) from table where point in
>>> rectangle and userid=xxx"
>> Sure.  Add the userid=xxx to your filter list like you were doing.
>> ie.
>> SELECT ST_MakePoint(x,y)
>> FROM mytable
>> WHERE geom && <insert rectangle geom here>
>> AND userid = xxx;
>>
>> You can additionally add an ST_Contains() filter if you need to have
>> your points exactly inside the rectangle.
> 
> No, don't do that :)  "point && rectangle" is logically the same as
> "st_contains(rectangle, point)" and somewhat cheaper.
> 

:) I disagree Paul.  These are not logically the same - they are very 
close, but not the same.  The bounding box coordinates are stored using 
4 bytes instead of the 8 bytes used to hold the rectangle's actual 
coordinates.  The bbox is rounded up to guarantee that the rectangle is 
contained entirely within it.  So, you could have a point that is 
contained within the bounding box but not the rectangle.

Consider,

SELECT
    a.poly && b.point AS bbox,
    ST_Contains(a.poly, b.point)
FROM
   (SELECT 'POLYGON((0 0, 0 1.0000001, 1 1.0000001, 1 0, 0 
0))'::geometry AS poly) a,
   (SELECT 'POINT(0.5 1.00000011)'::geometry AS point) b;

  bbox | st_contains
------+----------
  t    | f
(1 row)

-- Kevin

> The key is knowing for 100% sure that your polygonal geometry will
> *always* be a rectangle. If there's a chance it won't, you need the
> st_contains.
> 
> P