Thanks all I got it
From: Paul Ramsey Sent: 2008-05-21 06:04:05 To: PostGIS Users Discussion CC: Subject: Re: [postgis-users] 3D index in postgis Bah humbug, right you are :) P On Tue, May 20, 2008 at 12:31 PM, Kevin Neufeld <[EMAIL PROTECTED] > wrote: > Paul Ramsey wrote: > > > > On Tue, May 20, 2008 at 8:40 AM, Kevin Neufeld <[EMAIL PROTECTED] > > > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
