2

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?

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.