[postgis-users] ST_Union on empty polygon loses SRID

[Jonathan Schultz]

Hello,

I have found with a query that makes a union of geometries which
includes an empty polygon produces a result without an SRID. For a
trivial example:

> => select ST_SRID(ST_Union(ST_GeomFromText('POLYGON EMPTY', 4283)));
> st_srid ---------
> 0

This seems like a bug to me but maybe there is a good explanation?

Thanks,
Jonathan
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Darafei "Komяpa" Praliaskouski]

Does not reproduce on my 3.2 and 3.1.4 installs:

10:17:37 [gis] > select postgis_full_version();
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                                                      postgis_full_version                                                                                                                       │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ POSTGIS="3.2.0dev 3.2.0beta1-23-g94d4588ac" [EXTENSION] PGSQL="140" GEOS="3.10.0dev-CAPI-1.15.0" SFCGAL="1.3.8" PROJ="7.2.1" GDAL="GDAL 3.2.2, released 2021/03/05" LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

Time: 47,743 ms
10:17:43 [gis] > select ST_SRID(ST_Union(ST_GeomFromText('POLYGON EMPTY', 4283)));
┌─────────┐
│ st_srid │
├─────────┤
│    4283 │
└─────────┘
(1 row)

Time: 1,531 ms

[Jonathan Schultz]

Thanks for the quick response!

> => select postgis_full_version();
> postgis_full_version
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> POSTGIS="3.1.0alpha1 f7a3648" [EXTENSION] PGSQL="120" GEOS="3.10.1-CAPI-1.16.0" PROJ="7.2.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)" (core procs from "3.0.2 2fb2a18" need upgrade)

This is from Debian/unstable. Looks like I just need to wait for an
update and the problem will go away.

[Regina Obe]

You sure that is fresh from debian unstable.

PostGIS 3.1.0alpha1 was more than a year ago. Latest stable release for 3.1
is PostGIS 3.1.4 which is newer than what you are showing below.

I think debian unstable is carrying 3.2.0beta1 and will be carrying
3.2.0beta2 soon.

Also your procs are out of date.

Try running

SELECT postgis_extensions_upgrade();

And then test again to see if things are better.

[Jonathan Schultz]

This is actually in response to Regina Obe's email which I didn't
receive but only saw on the list archives. Maybe there is another way of
replying to her message but I don't know it.

I have definitely upgraded my debian/unstable installation.

> $ dpkg-query -W postgis
> postgis 3.1.4+dfsg-3+b1

but still I get the previously mentioned response to postgis_full_version()

I had presumed that this was just due to debian and postgis maintaining
different versioning systems. But is it possible that postgresql is for
some reason not registering the upgrade to postgis?

One possibly related problem I found was:

> SELECT postgis_extensions_upgrade();
> NOTICE: Updating extension postgis from 3.0.2 to 3.1.0alpha1
> ERROR: extension "postgis" has no update path from version "3.0.2" to version "3.1.0alpha1"
> CONTEXT: SQL statement "ALTER EXTENSION postgis UPDATE TO "3.1.0alpha1";"
> PL/pgSQL function postgis_extensions_upgrade() line 68 at EXECUTE

Any suggestions?

Thanks!

> postgi...@lists.osgeo.org <mailto:postgi...@lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
> <https://lists.osgeo.org/mailman/listinfo/postgis-users>

[Regina Obe]

All the upgrade scripts are the same. We just delete the pre-release scripts to reduce disk space.

To upgrade yours do the following
-- this changes all your installed versions to 3.1.0 so you have an upgrade path
UPDATE pg_catalog.pg_extension SET extversion = '3.1.0' WHERE extname IN('postgis', 'postgis_raster','postgis_sfcgal', 'postgis_topology', 'postgis_tiger_geocoder');

SELECT postgis_extensions_upgrade();

> -----Original Message-----
> From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf
> Of Jonathan Schultz
> Sent: Sunday, November 28, 2021 7:50 AM
> To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
> Subject: Re: [postgis-users] ST_Union on empty polygon loses SRID
>

> This is actually in response to Regina Obe's email which I didn't receive but
> only saw on the list archives. Maybe there is another way of replying to her
> message but I don't know it.
>
> I have definitely upgraded my debian/unstable installation.
>
> > $ dpkg-query -W postgis
> > postgis 3.1.4+dfsg-3+b1
>
> but still I get the previously mentioned response to postgis_full_version()
>
> I had presumed that this was just due to debian and postgis maintaining
> different versioning systems. But is it possible that postgresql is for some
> reason not registering the upgrade to postgis?
>
> One possibly related problem I found was:
>
> > SELECT postgis_extensions_upgrade();
> > NOTICE: Updating extension postgis from 3.0.2 to 3.1.0alpha1
> > ERROR: extension "postgis" has no update path from version "3.0.2" to
> version "3.1.0alpha1"
> > CONTEXT: SQL statement "ALTER EXTENSION postgis UPDATE TO
> "3.1.0alpha1";"
> > PL/pgSQL function postgis_extensions_upgrade() line 68 at EXECUTE
>
> Any suggestions?
>
> Thanks!
>
>
> On 27/11/21 15:18, Darafei "Kom?pa" Praliaskouski wrote:
> > Does not reproduce on my 3.2 and 3.1.4 installs:
> >
> > 10:17:37[gis] > select postgis_full_version();

> > +-----------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------------------------------
> ----------------------------------------------------------------+
> >
> >
> >
> >
> > postgis_full_version
> >
> >
> > +---------------------------------------------------------------------
> > +---------------------------------------------------------------------
> > +---------------------------------------------------------------------
> > +--------------------------------------------------

> >
> > POSTGIS="3.2.0dev 3.2.0beta1-23-g94d4588ac" [EXTENSION]
> PGSQL="140"
> > GEOS="3.10.0dev-CAPI-1.15.0" SFCGAL="1.3.8" PROJ="7.2.1" GDAL="GDAL
> > 3.2.2, released 2021/03/05" LIBXML="2.9.12" LIBJSON="0.15"
> > LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER

> > +-----------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------------------------------
> ----------------------------------------------------------------+

> >
> > (1 row)
> >
> > Time: 47,743 ms
> > 10:17:43[gis] > select ST_SRID(ST_Union(ST_GeomFromText('POLYGON
> > EMPTY', 4283)));

> > +---------+
> > st_srid
> > +---------
> > 4283
> > +---------+

[Regina Obe]

Okay I didn't completely read the error:

> > > SELECT postgis_extensions_upgrade();
> > > NOTICE: Updating extension postgis from 3.0.2 to 3.1.0alpha1
> > > ERROR: extension "postgis" has no update path from version "3.0.2"
> > > to
> > version "3.1.0alpha1"
> > > CONTEXT: SQL statement "ALTER EXTENSION postgis UPDATE TO
> > "3.1.0alpha1";"
> > > PL/pgSQL function postgis_extensions_upgrade() line 68 at EXECUTE
> >

That error means your new version is 3.1.0alpha1 but you are scripts currently on 3.0.2. So something is wrong with your binary install. It could be you have a postgis installed for a different PG than what you are checking. PostGIS versions are tied to a specific PostgreSQL instance, and debian/ubuntu makes it easy to run multiple.


I would first check how many clusters you have and versions with below command:

pg_lsclusters

Mine for example shows:
Ver Cluster Port Status Owner Data directory Log file
13 main 5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

Whatever you have active, you need to run something like the below replacing the 13 with whatever version of PostgreSQL you have active.

apt install postgresql-13-postgis-3


You shouldn't have to run the below steps I mentioned because your scripts are already on a released version postgis-3.0.2 but are bound to a in-development binary. If you ran it already, it's still okay cause all the upgrade scripts are copies of each other.

> -----Original Message-----
> From: Regina Obe [mailto:l...@pcorp.us]
> Sent: Sunday, November 28, 2021 12:27 PM
> To: 'PostGIS Users Discussion' <postgi...@lists.osgeo.org>;
> 'jona...@schultz.la' <jona...@schultz.la>
> Subject: RE: [postgis-users] ST_Union on empty polygon loses SRID
>
> All the upgrade scripts are the same. We just delete the pre-release scripts to
> reduce disk space.
>
> To upgrade yours do the following
> -- this changes all your installed versions to 3.1.0 so you have an upgrade path
> UPDATE pg_catalog.pg_extension SET extversion = '3.1.0' WHERE extname
> IN('postgis', 'postgis_raster','postgis_sfcgal', 'postgis_topology',
> 'postgis_tiger_geocoder');
>
> SELECT postgis_extensions_upgrade();
>
> > -----Original Message-----
> > From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On
> > Behalf Of Jonathan Schultz
> > Sent: Sunday, November 28, 2021 7:50 AM
> > To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
> > Subject: Re: [postgis-users] ST_Union on empty polygon loses SRID
> >

> > > +-------------------------------------------------------------------
> > > +----------------------------
> > ----------------------------------------------------------------------
> > ----------------------------
> > ----------------------------------------------------------------+
> > >
> > >
> > >
> > >
> > > postgis_full_version
> > >
> > >
> > > +-------------------------------------------------------------------
> > > +--
> > > +-------------------------------------------------------------------
> > > +--
> > > +-------------------------------------------------------------------
> > > +--
> > > +--------------------------------------------------

> > >
> > > POSTGIS="3.2.0dev 3.2.0beta1-23-g94d4588ac" [EXTENSION]
> > PGSQL="140"
> > > GEOS="3.10.0dev-CAPI-1.15.0" SFCGAL="1.3.8" PROJ="7.2.1"
> GDAL="GDAL
> > > 3.2.2, released 2021/03/05" LIBXML="2.9.12" LIBJSON="0.15"
> > > LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER

> > > +-------------------------------------------------------------------
> > > +----------------------------
> > ----------------------------------------------------------------------
> > ----------------------------
> > ----------------------------------------------------------------+

> > >
> > > (1 row)
> > >
> > > Time: 47,743 ms
> > > 10:17:43[gis] > select ST_SRID(ST_Union(ST_GeomFromText('POLYGON
> > > EMPTY', 4283)));

> > > +---------+
> > > st_srid
> > > +---------
> > > 4283
> > > +---------+

[Jonathan Schultz]

> I would first check how many clusters you have and versions with below command:
>
> pg_lsclusters

Ver Cluster Port Status Owner Data directory Log file

12 main 5432 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

No problems there.

Checking out the Debian repositories it looks like
postgresql-12-postgis-3 stopped evolving after version 3.0.0+dfsg-5. I
needed to add apt.postgresql.org to my repositories. I did that, and
everything is fine now.

Thank you so much for your prompt and detailed attention.