Efficient Sorted Bounding Box Query

How would I create indexes in PostgresSQL 8.3 which would make a sorted bounding box query efficient? The table I'm querying has quite a few rows. That is I want the create indexes

Solution 1:

To create a GiST index on a point attribute so that we can efficiently use box operators on the result of the conversion function:

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT*FROM points
    WHERE box(location,location) &&'(0,0),(1,1)'::box;

http://www.postgresql.org/docs/9.0/static/sql-createindex.html

This is the example in 9.0 docs. It should work for 8.3 though as these are features that have been around for ages.

Solution 2:

You could try using a GiST index to implement an R-Tree. This type of index is poorly documented, so you might have to trawl through example code in the source distribution.

(Note: My prior advice to use R-Tree indexes appears to be out of date; they are deprecated.)

Solution 3:

Sounds like you'd want to take a look at PostGIS, a PostgreSQL module for spatial data types and queries. It supports quick lookups using GiST indexes. Unfortunately I can't guide you further as I haven't used PostGIS myself.