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
