sqlite, index for geographical coordinates?

Question

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.

You'd be better off with SpatiaLite, the SQLite extension for spatial data. You'd be even more better off with PostGIS but that's a whole other can of worms... — Spacedman
I have added indexes for Lat/Long and performance are better. I will try (again) to integrate spatialite — sebastien

CL. CL. · Accepted Answer · 2013-07-25T07:32:00

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).

sqlite, index for geographical coordinates?

1 Answers