[postgis-devel] Turning spatial_ref_sys into a view to separate system vs. user entries

[Sandro Santilli]

[postgis-user took off, cross posting can be a problem for many]

On Wed, Feb 02, 2022 at 12:32:06AM -0500, Regina Obe wrote:

> Aside from the reasons you've stated there is also the case of the numerous
> changes happening in proj and many spatial reference systems added these
> days that people may prefer to keep their old (possibly wrong versions) to
> agree with something else.  This does give them the option to do so.

Speaking of this, I'm thinking if we should add a "keep_on_upgrade"
columnn in the spatial_ref_sys_user with values expressing in
which case to keep the custom entry. Possible cases in which a custom
entry may be in:

  - Custom matches 100% system entry
  - Custom has same SRID of system entry but some different column value
  - Custom has SRID not found in system table

In the current code the very first upgrade from non-view based PostGIS
version will result in custom entries being 100% matching the content
of the "spatial_ref_sys" table as it was before the upgrade, but
possibly very few or zero would really be needed by the user, so I was
thinking we could run, as part of the upgrade, a *cleanup* of custom
entries, but which entries to drop ? Possible options are:

  - Keep only 100% matching entries (all column values being the same)
  - Only not entries with SRID not found in system table

I can think of a reason why one would want to also keep an entry which
is 100% matching, to avoid future upgrades to possibly change that
value, for that reason a "keep_on_upgrade" column may be useful.

What do you think ?

--strk;