postgresql - PG::UndefinedFunction:错误:运算符不存在:几何&&框

Question

为什么 PostgreSQL 提示 && 运算符不存在？ (我安装了 PostGIS - 见下文)。

mydb=# SELECT "monuments".* FROM "monuments" WHERE
mydb=# (coord && '-10,-10,10,10'::box)
mydb=# ORDER BY created_at DESC ;
ERROR:  operator does not exist: geometry && box
LINE 1: ...LECT "monuments".* FROM "monuments" WHERE (coord && '-10...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

我安装了 PostGIS:

mydb=# select postgis_full_version();
NOTICE:  Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
                                                                      postgis_full_version                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.1.0 r11822" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER

顺便说一句，我的 table 是这样的:

mydb=# \d monuments
 id    | integer              | not null default nextval('monuments_id_seq'::regclass)
 coord | geometry(Point,3785) |

如果您需要更多信息，请告诉我。

Answer 1

box 是一个 built-in PostgreSQL primitive geometric type ，如点。

postgres=> \dT box
                      List of data types
   Schema   | Name |               Description                
------------+------+------------------------------------------
 pg_catalog | box  | geometric box '(lower left,upper right)'
(1 row)

PostGIS 使用 its own geometry 类型，并且通常不能与 PostgreSQL 内置的基本几何类型很好地互操作。这些是 && 支持的数据类型组合在我的 PostgreSQL 9.3 安装上使用 PostGIS 2:

postgres=# \do &&
                                 List of operators
   Schema   | Name | Left arg type | Right arg type | Result type |   Description   
------------+------+---------------+----------------+-------------+-----------------
 pg_catalog | &&   | anyarray      | anyarray       | boolean     | overlaps
 pg_catalog | &&   | anyrange      | anyrange       | boolean     | overlaps
 pg_catalog | &&   | box           | box            | boolean     | overlaps
 pg_catalog | &&   | circle        | circle         | boolean     | overlaps
 pg_catalog | &&   | polygon       | polygon        | boolean     | overlaps
 pg_catalog | &&   | tinterval     | tinterval      | boolean     | overlaps
 pg_catalog | &&   | tsquery       | tsquery        | tsquery     | AND-concatenate
 public     | &&   | geography     | geography      | boolean     | 
 public     | &&   | geometry      | geometry       | boolean     | 
 public     | &&   | geometry      | raster         | boolean     | 
 public     | &&   | raster        | geometry       | boolean     | 
 public     | &&   | raster        | raster         | boolean     | 
(12 rows)

您会看到 box && box 支持 box 但 box && geometry 不支持。由于您的 coord 列是 geometry 类型，因此您需要将 box 转换为 geometry，以便以 geometry && geometry 结束。

例子:

WHERE (coord && geometry(polygon('((-10, -10), (10, 10))'::box)))