0

Context

I'm trying to refine a PostgreSQL (v.10.12) trigger in order to actually execute some computations on point features only if the input points do intersect a convex hull stored in an other table.

warning: Go direct to the EDIT at the bottom!

Input data

I have two tables;
schema1.table1 containing many points which have a geom1 geometry, and
schema2.table2 containing a single multipolygon (the convex hull) as geom2.

This query is giving me an error I'm not able to fully understand:

SELECT ST_Intersects(schema1.table1.geom1, schema2.table2.geom2)
FROM schema1.table1, schema2.table2
WHERE schema1.table1.id = 1;

Errors

The error is as follows:

ERROR:  operator does not exist: geometry schema2.&& geometry
LINE 1: SELECT $1 OPERATOR(schema2.&&) $2 AND schema2._ST_Intersects($1,...
                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT $1 OPERATOR(schema2.&&) $2 AND schema2._ST_Intersects($1,$2)
CONTEXT:  SQL function "st_intersects" during inlining
********** Error **********

ERROR: operator does not exist: geometry schema2.&& geometry
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Context: SQL function "st_intersects" during inlining

Here are some more information;

  • on table1:
SELECT geom1 from schema1.table1 limit 2;
--------------------
geom1
geometry(Point,4326)
--------------------
"0101000020E610000000000000000000000000000000000000"
"0101000020E610000000000000000000000000000000000000"

  • and on table2:
SELECT geom2 from schema2.table2 limit 2;
--------------------
geom2
geometry(MultiPolygon,4326)
--------------------
"0106000020E610000001000000010 (...)"


The really strange thing is that even this simple query:

SELECT ST_AsText('0101000020E610000000000000000000000000000000000000');

is giving me an error (!):

ERROR:  type "schema2.geometry" does not exist
LINE 1:  SELECT schema2.ST_AsText($1::schema2.geometry); 
                                    ^
QUERY:   SELECT schema2.ST_AsText($1::schema2.geometry); 
CONTEXT:  SQL function "st_astext" during startup
********** Error **********

ERROR: type "schema2.geometry" does not exist
SQL state: 42704
Context: SQL function "st_astext" during startup

All these errors are occurring both within pgAdmin or the psql console.
I wish I could narrow down the issue to something I already know but that's unfortunately not the case for the moment.

I'm confused, I really don't get what kind of dark magic is happening there?
Any hint would be much appreciated.

EDIT:

I just figured out I had this code in my DB initialization steps, as I followed this "official" explanation:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS pgrouting WITH SCHEMA postgis;
UPDATE pg_extension SET extrelocatable = TRUE WHERE extname = 'postgis';
ALTER EXTENSION postgis SET SCHEMA postgis;
GRANT ALL ON SCHEMA postgis TO db_user;
GRANT ALL ON SCHEMA topology TO db_user;
ALTER ROLE db_user SET search_path TO postgis, topology, public;

As it stands, I run into the bug.
But if I comment out the ALTER EXTENSION line or if I append WITH SCHEMA postgis; to the first line or if I do both changes, I can execute PostGIS functions without error.
But then, the last line seem redundant, so I can erase it.

The really strange thing, is that PostGIS automatically goes to the postgis schema, but if I do not explicitly specify the name of the schema while loading PostGIS, even if it goes to the postgis schema automatically, then things go wrong if I alter the postgis extension, e.g.

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS pgrouting WITH SCHEMA postgis;
UPDATE pg_extension SET extrelocatable = TRUE WHERE extname = 'postgis';
ALTER EXTENSION postgis SET SCHEMA postgis;
GRANT ALL ON SCHEMA postgis TO db_user;
GRANT ALL ON SCHEMA topology TO db_user;
ALTER ROLE db_user SET search_path TO postgis, topology, public;

=> Error.

But:

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA postgis; -- this is the only diff
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS pgrouting WITH SCHEMA postgis;
UPDATE pg_extension SET extrelocatable = TRUE WHERE extname = 'postgis';
ALTER EXTENSION postgis SET SCHEMA postgis;
GRANT ALL ON SCHEMA postgis TO db_user;
GRANT ALL ON SCHEMA topology TO db_user;
ALTER ROLE db_user SET search_path TO postgis, topology, public;

=> No error.

But in both cases PostGIS actually goes at the same place!

So mabye the documentation is oudated and you can let PostGIS go in its predefined postgis schema on its own. Because it seems it doesn't go into the public schema anmyore, which was the case intially, while the move. I don't know.
But you may run into trouble while also blindly following this, or the linked documentation here above for example.

My PostGIS version is 2.4.4 on Ubuntu 18.04 (from the official repositories).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.