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