Postgis ST_Distance with geography always return 0

Last week I upgraded existing PostgreSQL databases on a server running Ubuntu 20.04 LTS

I previously installed PostgreSQL 13 with Postgis 3 from apt.postgresql.org before.

I installed PostgreSQL 16 and Postgis 3 for it and then ran pg_upgradecluster 13 main to upgrade to 16.

After a while, I noticed that ST_Distance with geography always return 0.0.

Can you please help me please what i can try next?

I tried to create a new cluster and run:

pg_createcluster 16 patko
systemctl start postgresql@16-patko.service
postgres createdb -p 5433 patko
sudo -u postgres psql -p 5433 patko
patko=# create extension postgis;
CREATE EXTENSION
patko=# SELECT
    ST_Distance(
        ST_SetSRID(ST_Point(0, 0), 4326)::geography,
        ST_SetSRID(ST_Point(0, 1), 4326)::geography
    ) as distance;
 distance
----------
        0
(1 row)

patko=# select version();
                                                              version
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
(1 row)

patko=# select postgis_full_version();
                                                                         postgis_full_version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="160" 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.5.0 (Internal)"
(1 row)

ST_Distance with geometry works fine.

I tried clean Ubuntu 20.04 installation with exactly the same Postgresql and Postgis (select version() and select postgis_full_version() have exactly the same output as above) and it works fine.

patko=# SELECT
    ST_Distance(
        ST_SetSRID(ST_Point(0, 0), 4326)::geography,
        ST_SetSRID(ST_Point(0, 1), 4326)::geography
    ) as distance;
    distance
----------------
 110574.3885578
(1 row)

So the problem seems to be the upgrade on the database, even if I create a new cluster afterwards.

I have tried:

reinstall postgresql with apt-get --reinstall install postgresql-16 postgis postgresql-16-postgis-3
upgrade extenstion with SELECT postgis_extensions_upgrade(); in my database

Solution

I figured it out.

I do not know how, but I have these 5 packages locally installed (not from apt) which was used by postgis:

gdal-data/now 3.3.2+dfsg-2~focal2 all [installed,local]
libgeotiff5/now 1.7.0-2~focal0 amd64 [installed,local]
libspatialite7/now 5.0.0-1~focal1 amd64 [installed,local]
proj-bin/now 7.2.1-1~focal0 amd64 [installed,local]
proj-data/now 7.2.1-1~focal0 all [installed,local]

Found versions of apt and install latest APT versions

apt-cache madison [package]
apt install [package]=[latest version from madison]

So after that i have these:

gdal-data/focal,focal,now 3.0.4+dfsg-1build3 all [installed,automatic]
libgeotiff5/focal,now 1.5.1-2 amd64 [installed,automatic]
libspatialite7/focal,now 4.3.0a-6build1 amd64 [installed,automatic]
proj-bin/focal,now 6.3.1-1 amd64 [installed,automatic]
proj-data/focal,focal,now 6.3.1-1 all [installed,automatic]

After restarting the postgresql server ST_Distance works fine.