I can't seem to find much documentation on this. What's the simplest way to create a database/table on postgres supporting a query like this SELECT * FROM table WHERE distance(POINT(0,0), table.location) <= 1000m; Where POINT(0,0) and table.location should be latitude/longitude pair, and 1000m is 1000 meters. And how should I go about indexing that table? Thanks.
2 Answers
PostgreSQL support indexes on expressions and also partial indexes, you can probably 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
1 Comment
skalee
Bear in mind that Earth is not flat. Such planimetric calculations won't work well in general case.
Have you looked into Postgis and contrib/earthdistance?
http://www.postgresql.org/docs/9.0/interactive/earthdistance.html
answered Oct 16, 2010 at 12:26
Denis de Bernardy
79.1k14 gold badges138 silver badges158 bronze badges