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

[Sandro Santilli]

On Wed, Feb 02, 2022 at 09:55:33AM -0800, Paul Ramsey wrote:
> > On Feb 2, 2022, at 9:22 AM, Raúl Marín <raul at rmr.ninja> wrote:
> > 
> > On 2022-02-02 17:19, Sandro Santilli wrote:
> >> Are you basically saying we should just NEVER need "system"
> >> spatial_ref_sys entries but ONLY custom and overridden ones?
> > 
> > Yes, IMO system spatial_ref_sys shoudn't exist and it should be
> > "provided" by PROJ, and thus immutable from the Postgis side. Then
> > spatial_ref_sys would only contain the custom SRSs added by the
> > user. 
> 
> This is kind of the "one true path" to a non-database spatial ref
> sys. It provides an immutable source of projection info (yay for
> st_transform indexes) and fully up-to-date info. Unfortunately the
> definitions would then become quite opaque to the user.

>From reading https://blog.cleverelephant.ca/2019/02/proj4-postgis.html
I seem to understand we are already quite opaque, UNLESS for cases in
which the "auth_name"/"auth_srid" pair of values associated with a
SRID are not known by PROJ (which we cannot tell in advance, right?).

Maybe I should ensure both "auth_name" and "auth_srid" are then
returned NULL for records coming from the spatial_ref_sys_user, to
ensure full transparency of at least those ones ? Or should we use
a special value for "auth_name" to mean "dont' even try to ask PROJ
for a match" ?

> WRT the spatial_ref_sys_usr/postgis tables, the need for SRID to remain
> a unique key and also the auth_srid/auth_name pairing to remain unique
> have to be there.

Note right now we're not enforcing unique auth_srid/auth_name at all
(worth a ticket ?). In my current "view" implementation I'm enforcing
a unique SRID in both the user and the system tables.

> I also consider this a relatively low-value improvement (is our current
> set-up really a major pain point to a lot of users?) with a relatively
> high-risk profile (this table sits at the center of a lot of machinery
> both in the code base and in peoples applications). I'm fairly loath to
> say "go for it".

The only pain in current setup is that we're currently never upgrading
the spatial_ref_sys: https://trac.osgeo.org/postgis/ticket/5024
If we had to update them on postgis upgrade we'd need to know which
entries are really *expected* from user to be upgraded and which
entries are NOT expected to be upgraded. There's currently no way
to tell, and this is what the user/system split is trying to solve.

The other alternative which came up was a proposal from Regina to
add an additional column to the table to distinguish which entries
should be considered "system entries" (thus upgraded and not dumped)
and which entries should be considered "user entries" (thus dumped
and not upgraded).

--strk;