postgresql - PG::UndefinedFunction:错误:运算符不存在:geometry && box

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

顺便说一下，我的桌子是这样的：

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

如果你需要更多的信息请告诉我。

最佳答案

box是一个built-in PostgreSQL primitive geometric type，类似于point。

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

PostGIS使用its owngeometry类型，并且通常不能与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)))

关于postgresql - PG::UndefinedFunction:错误:运算符不存在:geometry && box，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20484364/