Thread: Detecting whether a point is in a box.
Hello I am trying to write a query to find all points that fall within a given box. However, I cannot seem to find the functionality for determining whether a point is within a box. e.g. select box '((0,0),(1,1))' @> point '(0.5,0.5)'; operator does not exist: box @> point Is this operator for circles only? Is there a similar operator for boxes? I have not been able to find it in the documentation. Any help would be much appreciated H
HST <helenst@gmail.com> writes: > I am trying to write a query to find all points that fall within a > given box. However, I cannot seem to find the functionality for > determining whether a point is within a box. > e.g. select box '((0,0),(1,1))' @> point '(0.5,0.5)'; > operator does not exist: box @> point psql's \do shows that we have box @> box and polygon @> point, but no box @> point. So one possibility is to change your box data to polygons. Another is to manufacture a zero-area box from the point, using the box(point,point) function, and use @> with that. Also, there is a point <@ box, although I'm not sure you want to use that --- the lack of a commutator suggests that it probably hasn't got any index support. Is this just for a one-shot query, or are you hoping to do indexed searches in a big table? If the latter, which object is being stored in the table? You want to be sure you can create an index that will support the operator you use. regards, tom lane
Helen <helenst@gmail.com> writes: > It's the point that's stored in the table - the query is to find all the points within a given box. I'm not sure what thatmeans for indexing though, would that mean the point <@ box operator could be used with an index? if not, the polygonsolution will probably do. No, a quick look shows that we don't have *any* opclass for points: regression=# select * from pg_opclass where opcintype = 'point'::regtype; opcamid | opcname | opcnamespace | opcowner | opcintype | opcdefault | opckeytype ---------+---------+--------------+----------+-----------+------------+------------ (0 rows) So if you want to make this indexable, you probably have to build a gist index on box(pointcol,pointcol), and be careful to express all your queries using that locution :-(. You might want to look into PostGIS, which has much more extensive geometric support than what you can find in core Postgres. Or, if you're feeling really enterprising, you could build a gist operator class for points. (Please submit it if you do.) regards, tom lane
Dear Madam, I had the same issue tonight. I sorted it out, perhaps it may help you :) Convert BOX to a polygon & insert some values as per the syntax. Than run a query SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)'; let me know if this doesn't work for you ;) Cheers! Rao -- View this message in context: http://postgresql.1045698.n5.nabble.com/Detecting-whether-a-point-is-in-a-box-tp1887472p3422647.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.