Please forgive a very, very elementary question.
I am using a PostgreSQL 9.3.5 database, and one of the tables stores longitude and latitude. (These are two separate columns.) I would like to do efficient searches of the form "find all rows with (latitude,longitude) within 20 km of a given point".
I understand that this version of PostgreSQL has an "sp-gist" indexing method for addressing exactly this sort of situation. However, I can't seem to find an example or tutorial of how I go about actually building the index. In particular, are (latitude,longitude) distance handled natively, or am I somehow supposed to provide my own C code to implement the haversine formula? Do I need to convert the (latitude,longitude) into some sort of joint spatial variable?
Best Answer
AFAIK you want to use a regular GIST index (PostGIS has a special R-Tree index on top of GIST).
Usually it's easier to have one column represent your lat/longs. See http://postgis.net/docs/manual-1.5/ch04.html#PostGIS_Geography
For indexed radius search see ST_DWithin