[postgis-devel] spatial_ref_sys view

[Regina Obe]

> I've been trying to figure out my objections to this, it worries my
greatly but
> for amorphous reasons. I think, as I noted earlier, I cannot perceive the
value
> of the improvement outweighing the complexity of the extra machinery.
> Some bullet point thoughts:
> 
> * Most users don't have custom entries, most users live in the space of
long-
> existing EPSG codes. They get nothing out of this.
[Regina Obe] 
The reason we started this was because we discovered we are not pushing new
spatial_ref_sys to users 
and we are not updating existing ones.

Having our shipped versions separate from user entered ones, serves two
purposes I see:

1) Allows us up to date entries we ship without accidentally overwriting
user ones
2) Makes keeping our versions as simple as

TRUNCATE spatial_ref_sys_postgis;
INSERT INTO spatial_ref_sys_postgis

No crazy compare or excluding ranges etc to insure this.

I had thought about how adding is_custom could work, but the fact that many
do

INSERT INTO spatial_ref_sys
VALUES (a....)

Without specifying columns, makes adding an is_custom a breaking user-facing
change.


> * Those users who do have custom entries, have largely figured out how to
> exist in the current regime. They use ranges that are out of the way of
the
> system entries. Maybe they follow our documented recommendations (we
> have those, right?)
[Regina Obe] 
No they don't.  A lot of them I suspect are inserting values e.g from ESRI
or some other authority.
Many of them are doing it possibly cause they noticed they are missing new
ones we have added over the years.


> * Updates to EPSG definitions with new better measurements, etc, for
> existing SRS definitions will happen magically now anyways, since the
> AUTH_NAME/AUTH_SRID pair is what's used to map from an SRID to an
> actual projection transformation. Update your proj: get the new state-of-
> the-art for all existing systems.
> 
[Regina Obe] 
Exactly but we are not adding new ones to our system for users upgrading.
We need to fix this issue somehow.  We also need to think about all those
users on PROJ < 6
There are I think still quite a few and our PostGIS 3.3.0 I think is still
going to support < 6


> So we're really down to just folks who absolutely need net-new EPSG
> definitions, but ... cannot figure out to load spatial_ref_sys.sql? More
docs /
> better docs feels like a reasonable approach to this.
> 
[Regina Obe] 
That's an option though I don't think people will know to look at the docs.
I have little faith people read docs except possibly for specific function
signatures.

> Basically, if you're sophisticated enough to use custom SRS definitions,
> you're sophisticated enough to read whatever best practices we want to
> provide to make sure those definitions easily survive an upgrade.
> 
> Something like that? I just cannot get comfortable with a complex re-
> working of a core piece that mostly doesn't make things any better for
most
> users.
> 
> P
[Regina Obe] 
On the surface I thought it was a complex reworking, but after thinking
about it more, I feel like it's not that complex
and simplifies things.

1) It would make spatial_ref_sys a view in line with our geometry_columns,
geography_columns
So all our metadata catalogs would now all be views.

2) Thinking about how we can insure users get new spatial_ref_sys entries
during upgrade (which I thought they were getting)
Hurts my head to think "What if we overwrite their entry, how will they get
that back".  We can't easily do this with INSERT INTO
We need to change to  FROM VALUES (....)

3) It's also simpler I think for a user to know, all my entries are in
spatial_ref_sys_user rather than having to wade thru all the entries in
spatial_ref_sys to figure out which ones are theirs.