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
