[GIS] Spatial index vs two coordinates indexes

coordinatesdatabasepostgispostgresqlspatial-index

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.

Best Answer

A standard multicolumn b-tree index on the two columns is probably the most effective solution provided that:

  1. both columns are used together in the where expression. A multicolumn index will not be used when only the second column is present

A multicolumn index is more efficient than two indexes on both columns because it will use less storage and will be less costly to update when the table changes (only one index to update vs two).

Related Question