Spatial Index vs. Two Code Indexes

Question

I have a table with two columns: latitude and longitude. I want all the objects to be in a rectangular (well, rectangular in the lat / lon coordinate metric): max-max and long-max. It basically boils down to the following pseudo-SQL:

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

What is the best solution to index my table? Two column index? Two indexes on two columns? Spatial Index?

I would like to know if a spatial index is really necessary in this case, since you need a special column, specific libraries, all due to portability and simplicity of the database.

Note. I want to keep this database agnostic, but for the sake of completeness, I mention the fact that I work with PostGreSQL 8, without it (at the moment) PostGIS.

Answer 1

What is your version of PostgreSQL: 8.0, 8.1, etc.? If you have a “high version”, you can try including lat and lon columns as a unique point type column. Like this:

 create table MyTable ( ... lat integer, lon integer, coor point, ... ); insert MyTable (..., lat, lon, coor, ...) values (..., lat1, lon1, '(lat1, lon1)', ...) ... 

And create the indices needed for testing:

 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 check which type of request 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 tested PostgreSQL 9 (with 20,000 entries), and the second is faster.