spatial_ref_sys table not updated with new entries

[robe]

As noted on mailing list excerpt from list ​https://lists.osgeo.org/pipermail/postgis-users/2021-November/045068.html

postgres=> create extension postgis version '2.4.5';
CREATE EXTENSION
postgres=> select count(*) from spatial_ref_sys;
 count
-------
  5757
(1 row)

postgres=> alter extension postgis update to '3.1.4';
WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
ALTER EXTENSION
postgres=> select count(*) from spatial_ref_sys;
 count
-------
  5757
(1 row)

whereas...

postgres=> create extension postgis version '3.1.4';
CREATE EXTENSION
postgres=> select count(*) from spatial_ref_sys;
 count
-------
  8500
(1 row)

[Regina Obe <lr@…>]

In 964971c/git:

Include count of entries in spatial ref sys. References #5024 for PostGIS 3.3

[Regina Obe <lr@…>]

In 964971c/git:

Include count of entries in spatial ref sys. References #5024 for PostGIS 3.3

[robe]

it looks like a good chunk of the new ones are ESRI. So that is why the count more than doubled. There are quite a few new EPSG ones too though.

Given that I suspect many of these are obscure, might be better to wait for PostGIS 3.3, where we are planning to convert spatial_ref_sys to a view to better track.

[Regina Obe <lr@…>]

In 421720a/git:

Take out spatial_ref_sys check. We know it fails. References #5024

[strk]

This is a long standing issue. EXTENSION_UPGRADE_SCRIPTS in extension/postgis/Makefile.in does not even include the config_dump file so the conditions of dumping spatial_ref_sys are ALSO immutable, not just the spatial_ref_sys contents.

[strk]

I've re-enabled the spatial_ref_sys check in the spatial-ref-sys-view branch on official repository, which is attached to gitlab merge request ​https://gitlab.com/postgis/postgis/-/merge_requests/57 (for no good reason, to be honest)

You can see Dronie testing upgrades in ​https://dronie.osgeo.org/postgis/postgis/branches

[strk]

For reference, SRID ranges proposed on 2012: ​https://lists.osgeo.org/pipermail/postgis-devel/2012-February/018440.html

[robe]

I'm reopening this because it is not yet committed in master.

[pramsey]

It seems to me that a far simpler and easier to reason about change would be to convert spatial_ref_sys.sql into a CSV file and have two build targets, one for spatial_ref_sys_create.sql that gets bundled into the CREATE EXTENSION script and one for spatial_ref_sys_update.sql that consists up upserts and gets bundled into the ALTER EXTENSION UPDATE script.

People always have the latest entries per the amazon email, and the situation is very simple and easily documented: create your custom entries out of the way of EPSG (<32600) and out of the way of ESRI. The table remains very simple and the logic is very easy to explain.

I think the latter point (simplicity of impact) is underappreciated WRT the proposed view, as even I, a lover of spatial reference systems, feel my eyes glaze over as proponents talk about the various cases and possible flags and intermediate states of "systems in both tables with a warning in the script".

[robe]

Replying to pramsey:

It seems to me that a far simpler and easier to reason about change would be to convert spatial_ref_sys.sql into a CSV file and have two build targets, one for spatial_ref_sys_create.sql that gets bundled into the CREATE EXTENSION script and one for spatial_ref_sys_update.sql that consists up upserts and gets bundled into the ALTER EXTENSION UPDATE script.

pramsey how about this — you put in your code and backport it to 3.2 since your code is backward compatible. Make sure you have tests please.

I still don't like your solution though. This whole keep in X range of SRIDs I've always found difficult to stomach especially when I'm getting SRIDs from other sources and we've never clearly stated that requirement.

People always have the latest entries per the amazon email, and the situation is very simple and easily documented: create your custom entries out of the way of EPSG (<32600) and out of the way of ESRI. The table remains very simple and the logic is very easy to explain.

What amazon email?

I think the latter point (simplicity of impact) is underappreciated WRT the proposed view, as even I, a lover of spatial reference systems, feel my eyes glaze over as proponents talk about the various cases and possible flags and intermediate states of "systems in both tables with a warning in the script".

I'm more annoyed I can't easily distinguish my entries from system distributed ones. So you are right I'm under appreciating this simplicity.

[pramsey]

If you are not going to like my blunt solution and are unlikely to accept it, should I *also* invest time in this? What are my odds of success here?

The user email you cite in the ticket is ​this one. So far the only data point on "users really want to muck with our current handling of spatial_ref_sys".

I would think that using auth_name of "ROBE" would distinguish your entries quite clearly.

[robe]

Replying to pramsey:

If you are not going to like my blunt solution and are unlikely to accept it, should I *also* invest time in this? What are my odds of success here?

The user email you cite in the ticket is ​this one. So far the only data point on "users really want to muck with our current handling of spatial_ref_sys".

I would think that using auth_name of "ROBE" would distinguish your entries quite clearly.

Your solution doesn't need acceptance. It is what I thought was the status quo. It solves the problem. So no I will not reject it.

[strk]

Another option would be still NOT including the spatial_ref_sys in the upgrade procedure but change it to always work when loaded into an existing database. This would mean using upsert strategy, which means resetting distributed SRID records while keeping user records outside that range (which is what postgis_restore.pl currently does)

[strk]

What I'm not sure about is whether we can REALLY clearly document and stick to SRID ranges, which is what you're basically saying with:

out of the way of EPSG (<32600) and out of the way of ESRI

This is my current set of max srids per auth_name:

strk=# select max(srid), auth_name from spatial_ref_sys group by auth_name;
  max   |       auth_name        
--------+------------------------
 900913 | spatialreferencing.org
  32766 | EPSG
 104992 | ESRI
(3 rows)

In my 2012 mail I tried to discuss this but nobody followed up: ​https://lists.osgeo.org/pipermail/postgis-devel/2012-February/018440.html

Sticking with a fixed system range means simplifying the pg_extension record as to what needs to be dumped from spatial_ref_sys. It still doesn't save "tweaks to system SRIDs" but so far I think nobody came up with that use case.

[Paul Ramsey <pramsey@…>]

In 9d58887/git:

On update, apply the latest spatial_ref_sys. Also, use a multi-valued insert with a no-op on conflict for spatial_ref_sys. References #5024

[strk]

A new mail from a user shows why users may want to keep their own version of spatial_ref_sys records while keeping system SRIDs: ​https://lists.osgeo.org/pipermail/postgis-users/2022-March/045373.html

[robe]

This is done in 3.3 and I think it's a big enough change not worth backporting.

[Regina Obe <lr@…>]

In a31a43a7/git:

Add missing item References #5024 for PostGIS 3.3.0

[strk]

The NEWS entry for this ticket is wrong: Update spatial_ref_sys as part of ALTER EXTENSION update postgis. We are NOT updating it, just inserting rows with a SRID that doesn't exist already.