Error using ST_Intersects in WHERE clause

[tsc]

After an upgrade from PSQL11 with PostGIS 2.53 to PSQL12 with PostGIS 3.0.1 I am encountering a bug. The following query is minimal, reproducible example of the problem:

SELECT * FROM 
(VALUES 
 ('POLYGON((30 10, 40 40, 20 40, 30 10))'::geography), 
 ('POLYGON((81 6,140 35,-70 18,-51 0,-60 -46,106 -6,81 6))'::geography)
) AS t (v)
WHERE (ST_INTERSECTS(t.v, 'POLYGON ((30 10, 40 40, 20 40, 30 10))'::geography))

This outputs:

ERROR:  CircTreePIP: Unable to generate outside point!
SQL state: XX000

Version on system producing the error:

PostgreSQL 12.2 (Ubuntu 12.2-4) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-8ubuntu1) 9.3.0, 64-bit POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"

Running the same query with following versions works as expected:

PostgreSQL 11.6 (Ubuntu 11.6-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER

More details:

• The problem only occurs when multiple rows are selected - select each of the rows in separate queries works.
• The error only occurs when the ST_INTERSECT is in the WHERE-clause, having it in the SELECT doesn't result in the error.
• The error only occurs with geography, using geometry instead works fine.

[tsc]

PostGIS 2.5.4 on (at least) Ubuntu 18.04 now produces the exact same problem.

If the 2.5.4 /usr/lib/postgresql/11/lib/postgis-2.5.so is exchanged with the 2.5.3 version the problem goes away.

Ubuntu only supplies PostGIS 2.5.4 now so this will potentially break a lot of installations without an easy fallback.

[robe]

Well I confirmed this is still an issue on 2.5.5dev, 3.0.1, and 3.1.0 on my windows installs On PostgreSQL 12.

Haven't verified that it worked in 2.5.3 but I will assume so. Would be nice to fix this before I release 2.5.5 this week.

[Paul Ramsey <pramsey@…>]

In 2c4618a/git:

Be more aggressive about obtaining an outside point from gbox. References #4689

[Paul Ramsey <pramsey@…>]

In aa4642f/git:

Be more aggressive about obtaining an outside point from gbox. References #4689

[Paul Ramsey <pramsey@…>]

In ddd41fd/git:

Be more aggressive about obtaining an outside point from gbox. References #4689

[pramsey]

So, this change should help, and hopefully not break something else. The whole geodetic thing is a massive rickety tower :/

[pramsey]

Re-open if it doesn't get better.

[robe]

It appears this broke berrie64's regress - see #4740

Berrie64 is a Raspberry-pi 64-bit ARM.

[robe]

okay that was weird berrie64 seems to be passing again.

[robe]

I was mistaken still failing on berrie64. However since this is a new test it might have always been an issue and just exposed with the new test added by this commit.

[robe]

bah I'll just keep this as closed. As I think the ticket in berrie64 might be soemthing else. It just passed rerunning the same exact job.

[komzpa]

It exploded in #4800