高效的排序边界框查询 [英] Efficient sorted bounding box query

问题描述

我如何在PostgresSQL 8.3中创建索引，以使排序后的边界框查询更高效？我要查询的表有很多行。

How would I create indexes in PostgresSQL 8.3 which would make a sorted bounding box query efficient? The table I'm querying has quite a few rows.

我想要创建索引，以使以下查询尽可能高效：

That is I want the create indexes that makes the following query as efficient as possible:

SELECT * FROM features 
WHERE lat BETWEEN ? AND ?
AND lng BETWEEN ? AND ?
ORDER BY score DESC

功能表如下：

   Column   |          Type          |   
------------+------------------------+
 id         | integer                |
 name       | character varying(255) | 
 type       | character varying(255) | 
 lat        | double precision       | 
 lng        | double precision       | 
 score      | double precision       | 
 html       | text                   | 

推荐答案

创建GiST点属性上的索引，以便我们可以在转换函数的结果上有效使用框运算符：

To create a GiST index on a point attribute so that we can efficiently use box operators on the result of the conversion function:

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

http://www.postgresql.org/docs/9.0/static/sql-createindex.html

这是9.0文档中的示例。它应该适用于8.3，因为这些功能已经存在了很长时间了。

This is the example in 9.0 docs. It should work for 8.3 though as these are features that have been around for ages.

这篇关于高效的排序边界框查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！