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.

can you guarantee that you'll only ever perform this type of spatial query? If you ever need even a simple addition like finding the real world (great circle) distance between two points all those spatial libraries will come in real handy... Also, consider what will happen if the bounding box crosses the +/- longitude e.g. 170,0 -170,10 (this is a valid bounding box in the Pacific). perhaps consider asking in gis.stackexchange.com — tomfumb
– tomfumb, Commented Feb 8, 2012 at 20:50
Yes, 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). 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). — Laurent Grégoire
– Laurent Grégoire, Commented Feb 9, 2012 at 7:50

user731136 · Accepted Answer · 2012-02-09 12:28:00Z

2

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.

answered Feb 9, 2012 at 12:28

user731136

Sign up to request clarification or add additional context in comments.

2 Comments

Laurent Grégoire Over a year ago

My version is 8.4.10. You say faster, how much? I'm willing to trade speed against simplicity and portability.

user731136 Over a year ago

Hi @IOranger, how much is relative. In a table with 20000 records, when I extract 465 records using lat and lon: (cost=22.59..190.13 rows=266 width=28) (actual time=0.260..0.406 rows=465 loops=1) With coor: (cost=4.41..60.17 rows=20 width=28) (actual time=0.165..0.250 rows=465 loops=1). When I extract 8515 records using lat and lon: (cost=0.00..545.00 rows=8270 width=28) (actual time=0.732..5.331 rows=8515 loops=1). With coord: (cost=4.41..60.17 rows=20 width=28) (actual time=1.699..2.684 rows=8515 loops=1).

Collectives™ on Stack Overflow

Spatial index vs two coordinates indexes

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related