sqlite, index for geographical coordinates?

问题:

I have a SQLite database that contains geographical data. My table is defined like this:

     CREATE TABLE GEO_OBJECTS (ID VARCHAR(30) PRIMARY KEY     NOT NULL,
                LATITUDE       NUMERIC(6,3)      NOT NULL,  
                LONGITUDE      NUMERIC(6,3)      NOT NULL)  

Next my software (Java) is looking for GEO_OBJECTS using a simple SQL request like this:

SELECT * FROM GEO_OBJECTS WHERE latitude <= 123.4 AND latitude >= 26.32 AND longitude <= 12.41 AND longitude >= 6.23;

Will it improve the performances if I create an INDEX on latitude and longitude?

Next, when I get the result of this query I'm using the haversine formula to find the objects in great circle distance.

回答1:

A normal index might help for some one-dimensional interval queries, but in two or more dimensions, you need an index designed for such queries, such as an R-tree.

SQLite does have an R-tree module, but it might not have been compiled into your DB driver.

You might consider SpatiaLite, which is derived from SQLite and has many useful functions (such as computing the great circle distance).