Spatial index vs two coordinates indexes

Question

I have a table with two columns: latitude and longitude. I want to get all objects inside a "rectangular" (well, rectangular in a lat/lon coordinate metric) bounding box: min-max latitude and min-max longitude. Basically that boils down to the following pseudo-SQL:

SELECT * FROM MyTable WHERE lat < :maxlat AND lat > :minlat 
    AND lon < :maxlon AND lon > :minlon

What's the best solution for indexing my table? A two-column index? Two indexes on the two columns? A spatial index?

I would like to know if a spatial index is really needed in that case, as you need a special column, specific libraries, all that at the expense of database portability and simplicity.

Note: I'd like to keep this question database-agnostic, but for the sake of completeness I mention the fact that I'm working with PostGreSQL 8, w/o (for now) PostGIS.

Answer 1

What's your version of PostgreSQL: 8.0, 8.1, etc? If you have a "high version", you could try include lat and lon column as a unique column of point type. Like this:

create table MyTable (
   ...
   lat  integer,
   lon  integer,
   coor point,
   ...
);

insert MyTable (..., lat, lon, coor, ...) values (..., lat1, lon1, '(lat1, lon1)', ...)
...

And create the indexes needed to test:

create index MyTable_lat on MyTable (lat);
create index MyTable_lon on MyTable (lon);
create index MyTable_coor on MyTable using gist (coor);

Now you can test what type of query is faster:

explain analyze
select * 
from MyTable 
where lat < :maxlat and lat > :minlat 
and lon < :maxlon and lon > :minlon

Or:

explain analyze
select * 
from MyTable 
where coor <@ box '((:minlat,:minlon),(:maxlat,:maxlon))'

I did the test on PostgreSQL 9 (with 20000 records) and the second option is more faster.