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.
I can guarantee that I will perform only this type of spatial query. And I already have functions to compute the great-circle distance between two point (Haversine function), so I do not need a spatial-enabled database. As for crossing the Pacific, it's out of the covered zone (but could be easily handled by a small test that switch minlon and maxlon).
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.