[postgis-users] 3D index in postgis

[Paul Ramsey]

Bah humbug, right you are :)

P

On Tue, May 20, 2008 at 12:31 PM, Kevin Neufeld
<kneufeld at refractions.net> wrote:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>