postgres 空间索引 [英] postgres spatial indexing
问题描述
我似乎找不到太多关于此的文档.在postgres上创建数据库/表支持这样的查询的最简单方法是什么 SELECT * FROM table WHERE distance(POINT(0,0), table.location) <= 1000m;其中 POINT(0,0) 和 table.location 应该是纬度/经度对,1000m 是 1000 米.我应该如何索引该表?谢谢.
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.
推荐答案
PostgreSQL支持表达式索引和部分索引,你可以混合使用.
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/交互式/索引表达式.html
http://www.postgresql.org/docs/9.0/交互式/indexes-partial.html
这篇关于postgres 空间索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!