Opened 14 months ago

Closed 6 months ago

Last modified 5 months ago

#5024 closed defect (fixed)

spatial_ref_sys table not updated with new entries

Reported by: robe Owned by: strk
Priority: medium Milestone: PostGIS 3.3.0
Component: postgis Version: 3.1.x
Keywords: Cc:

Description

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)

Change History (21)

comment:1 by Regina Obe <lr@…>, 12 months ago

In 964971c/git:

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

comment:2 by Regina Obe <lr@…>, 12 months ago

In 964971c/git:

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

comment:3 by robe, 12 months ago

Milestone: PostGIS 3.1.5PostGIS 3.3.0

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.

comment:4 by robe, 12 months ago

Owner: changed from pramsey to strk

comment:5 by Regina Obe <lr@…>, 12 months ago

In 421720a/git:

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

comment:6 by strk, 12 months ago

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.

comment:9 by strk, 12 months ago

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

comment:21 by robe, 12 months ago

Status: newreopened

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

comment:22 by pramsey, 12 months ago

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".

in reply to:  22 comment:23 by robe, 12 months ago

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.

comment:24 by pramsey, 12 months ago

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.

in reply to:  24 comment:25 by robe, 12 months ago

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.

comment:26 by strk, 12 months ago

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)

comment:27 by strk, 12 months ago

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.

comment:28 by robe, 12 months ago

Milestone: PostGIS 3.3.0PostGIS 3.1.6

comment:29 by Paul Ramsey <pramsey@…>, 12 months ago

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

comment:30 by strk, 11 months ago

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

comment:31 by robe, 6 months ago

Milestone: PostGIS 3.1.6PostGIS 3.3.0
Resolution: fixed
Status: reopenedclosed

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

comment:32 by Regina Obe <lr@…>, 6 months ago

In a31a43a7/git:

Add missing item References #5024 for PostGIS 3.3.0

comment:33 by strk, 5 months ago

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.

Note: See TracTickets for help on using tickets.