[postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?

[Sandro Santilli]

Thanks Chris for your experience, comment below

On Tue, Mar 15, 2022 at 08:27:37PM -0700, Chris Tooley wrote:

> so I made sure
> I copied the original `proj4text` string and bit the bullet and modified
> the table with the following query:
> 
> > update spatial_ref_sys set proj4text = '+proj=aea +lat_1=34 +lat_2=40.5
> +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80
> +towgs84=0,0,0,0,0,0,0 +units=m +no_defs' where srid=3310;

So what you did was modifying what we call a "system entry"
(srid=3310) to fix a (possible) bug you found in PostGIS itself.

Now I guess you'll want YOUR version of the proj4text value to survive upgrades.

At the moment PostGIS soft upgrades are NOT going to revert your change,
but someone considers it a bug:

    https://trac.osgeo.org/postgis/ticket/5024

Your experience suggests it is NOT necessarely a bug, so a comment in
that ticket might be good to have too.

Still, PostGIS *hard* upgrades (implying dump/restore) will instead
get rid of your updates because srid=3310 is "BETWEEN 3174 AND 3791"
as found in `extcondition` column of pg_extension for extname='postgis'
and thus the record will NOT be included in the dump.

This discrepancy between hard and soft upgrades is what I'd like to
see fixed with a new simplified method of dealing with upgrades.

The solution I suggested was to keep separate tables for "system entries"
and "user entries", so that "user entries" would always "shadow"
"system entries" (allowing overrides) and would always be carried between
upgrades.

Another solution proposed was to still keep a single table but NEVER
automatically upgrade it, providing a function to select "system
entries" to use for populating the single table, when needed.

--strk;