[postgis-users] 3D index in postgis
kneufeld at refractions.net
Tue May 20 12:31:32 PDT 2008
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.
>> 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.
a.poly && b.point AS bbox,
(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
> 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
More information about the postgis-users