Postgres spatial indexing

Question

I can not find a lot of documentation about this. The easiest way to create a database / table for postgres supporting a query such as this table SELECT * FROM WHERE distance (POINT (0,0), table.location) <= 1000m; Where POINT (0,0) and table.location should be a pair of latitude / longitude, and 1000 m - 1000 meters. And how should I index this table? Thank.

Answer 1

PostgreSQL support indexes for expressions, as well as partial indexes, you can mix them.

This is just a random guess, I don't know if it works, but give it a try:

CREATE INDEX foobar ON table (distance(POINT(0,0), location))
 WHERE distance(POINT(0,0), location) <= 1000;

http://www.postgresql.org/docs/9.0/interactive/indexes-expressional.html

http://www.postgresql.org/docs/9.0/interactive/indexes-partial.html

Answer 2

Postgis contrib/earthdistance?

http://www.postgis.org/

http://www.postgresql.org/docs/9.0/interactive/earthdistance.html