PG::UndefinedFunction: ERROR: operator does not exist: geometry && box

Question

Why does PostgreSQL complain that the && operator does not exist? (I have PostGIS installed - see below).

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.

I have PostGIS installed:

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

And by the way, my table looks like this:

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

Let me know if you need any more info.

score 4 · Accepted Answer · edited Dec 11 '13 at 17:56

4

box is a built-in PostgreSQL primitive geometric type, like point.

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

PostGIS uses its own geometry type, and doesn't generally inter-operate well with the PostgreSQL built-in basic geometric types. These are the supported data type combinations for && with PostGIS 2 on my PostgreSQL 9.3 install:

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)

You'll see that box is supported for box && box but not box && geometry. Since your coord column is a geometry type, you'll need to convert the box to geometry, so as to end up with geometry && geometry.

Example:

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

edited Dec 11 '13 at 17:56

user664833

18,397
19
91
140

answered Dec 10 '13 at 03:14

Craig Ringer

307,061
76
688
778

Thanks a lot for this information. I am able to make the query work with `WHERE (coord && ST_Envelope('LINESTRING(-10 -10, 10 10)'::geometry) )` but I do not know how to *convert the `box` to `geometry`* as you suggested. Could you kindly tell me? – user664833 Dec 10 '13 at 20:46
I have also made it work with `WHERE ST_WITHIN(coord, ST_MakeEnvelope(-10, -10, 10, 10, 3785))` as well as with `WHERE coord @ ST_MakeEnvelope(-10, -10, 10, 10, 3785)` -- but I still cannot figure out how to *convert the `box` to `geometry`*. – user664833 Dec 11 '13 at 00:11
It also works with `WHERE (coord && 'LINESTRING(-10 -10, 10 10)'::geometry)` and `WHERE (coord @ 'LINESTRING(-10 -10, 10 10)'::geometry)` -- which are similar to my first comment, but without `ST_Envelope`. – user664833 Dec 11 '13 at 00:24
1

@user664833 After a bit of testing, found you need to convert to `polygon` then `geometry`. `geometry(polygon(thebox))`. – Craig Ringer Dec 11 '13 at 03:23
1

Thanks a lot for that. I would like to better understand what is happening. I believe we are casting from text/string (not even [WKT](https://en.wikipedia.org/wiki/Well-known_text#Geometric_objects)) to [box](http://www.postgresql.org/docs/9.3/static/datatype-geometric.html), then converting the box via PostgreSQL's [polygon](http://www.postgresql.org/docs/9.3/static/functions-geometry.html) static function into a polygon. But I don't know what is this `geometry` function that converts a `polygon` type into a `geometry` type. Could you kindly provide a link to an authoritative reference to it? – user664833 Dec 11 '13 at 18:31
1

@user664833 That's correct. You can use the `box` constructor `box(point(a,b), point(c,d))` instead of a text-form input, but that's not interesting for this problem. The `geometry` invocation is the function-form of a cast to geometry that is provided by PostGIS; you could equally well write `CAST( polygon(box(point(1,2),point(3,4)) AS geometry)`. See `\dC geometry` for the list of casts involving `geometry`, or `\dC polygon` for polygon. – Craig Ringer Dec 11 '13 at 21:26

score 1 · Answer 2 · edited May 23 '17 at 12:33

1

The simplest explanation would be that you installed the extension into some schema that is not in your current search_path.

Did you know, that you can even "schema-qualify" operators? Like:

SELECT 3 OPERATOR(pg_catalog.+) 4;

Or:

SELECT * FROM public.monuments
WHERE  coord OPERATOR(my_postgis_schema.&&) '-10,-10,10,10'::box);

This way you could make your query independent of the current search_path. Better though, to fix it.

edited May 23 '17 at 12:33

Community

1
1

answered Dec 10 '13 at 02:39

Erwin Brandstetter

605,456
145
1,078
1,228

1

Thank you for your answer and the additional useful information. My search path, as reported by `show search_path ;` is `"$user", public, postgis`. As per [the docs](http://www.postgresql.org/docs/current/static/ddl-schemas.html) I know that *if no such schema exists, the entry is ignored* -- so `"$user"` is ignored, and then it tries `public`, and then `postgis`. I tried both schemas, and in both cases I got `ERROR: operator does not exist`. Just to be sure I put in a bogus schema, and got `ERROR: schema "my_postgis_schema" does not exist` -- so at least I know it's trying. Any other ideas? – user664833 Dec 10 '13 at 20:58
Oh, and I meant to say that my schemas, as listed by `\dn`, are `postgis` and `public` (in that order). – user664833 Dec 10 '13 at 23:11
@user664833: My explanation would have been the simplest, but it doesn't seem to apply to your case. I think Craig has a point. – Erwin Brandstetter Dec 10 '13 at 23:20

PG::UndefinedFunction: ERROR: operator does not exist: geometry && box

2 Answers2