6

I'm trying to upgrade to Postgres 12 with PostGIS 3.0. Unfortunately, I can't get my spatial queries to work. There seems to be an issue using ST_Covers on a geography column with a GiST index.

Proof of Concept:

CREATE TABLE test (id SERIAL, the_geog GEOGRAPHY);
CREATE INDEX test_geog_idx ON test USING GIST(the_geog);
INSERT INTO test (the_geog) values (
    ST_Buffer(ST_GeogFromText('POINT(-0.1 51.5)'), 10)
);
SELECT id FROM test WHERE ST_Covers(the_geog, ST_GeogFromText('POINT(-0.1 51.5)'));

ERROR:  no spatial operator found for opfamily 19745 strategy 7

I imagine this is because GiST indexes work in a different way in PostGIS 3.0. How can I solve this issue?

Here is my Postgres version:

test=# select version(),postgis_full_version();
                                                   version                                                   |                                                                        postgis_full_version
-------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.1 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit | POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.2.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.2" WAGYU="0.4.3 (Internal)"
(1 row)

Here is the equivalent code with geometry:

CREATE TABLE geom (id SERIAL, the_geom GEOMETRY(Polygon, 4326));
CREATE INDEX test_geom_idx ON geom USING GIST(the_geom);
INSERT INTO geom (the_geom) values (
    ST_Buffer(ST_GeomFromText('POINT(-0.1 51.5)'), 10)
);
SELECT id FROM geom WHERE ST_Covers(the_geom, ST_GeomFromText('POINT(-0.1 51.5)',4326));

 id
----
  1

EDIT: Here is a snippet with a table named test1:

sx=# CREATE TABLE test1 (id SERIAL, the_geog GEOGRAPHY);
CREATE TABLE
sx=# CREATE INDEX test1_geog_idx ON test1 USING GIST(the_geog);
CREATE INDEX
sx=# SELECT id FROM test1 WHERE ST_Covers(the_geog, ST_GeogFromText('POINT(-0.1 51.5)'));
ERROR:  no spatial operator found for opfamily 19505 strategy 7
7
  • can you add the output from select version(),postgis_full_version(); using the edit button please
    – Ian Turton
    Dec 9, 2019 at 12:52
  • Does the query work without index?
    – user30184
    Dec 9, 2019 at 12:53
  • Yes, the query work without index Dec 9, 2019 at 13:06
  • @IanTurton amended, thanks Dec 9, 2019 at 15:10
  • 3
    It is a bug
    – JGH
    Dec 29, 2019 at 13:42

0

Browse other questions tagged or ask your own question.