[postgis-devel] PSC Vote: Make spatial_ref_sys as a view

[Paul Ramsey]

> On Feb 7, 2022, at 3:46 PM, Sandro Santilli <strk at kbt.io> wrote:
> 
> On Mon, Feb 07, 2022 at 03:15:35PM -0800, Paul Ramsey wrote:
>> 
>> 
>>> On Feb 7, 2022, at 3:02 PM, Sandro Santilli <strk at kbt.io> wrote:
>>> 
>>> On Mon, Feb 07, 2022 at 02:35:27PM -0800, Paul Ramsey wrote:
>>>>> On Feb 7, 2022, at 2:27 PM, Sandro Santilli <strk at kbt.io> wrote:
>>>>> 
>>>>> 3.2.1dev 3.2.0-28-g4443d6989
>>>> 
>>>> POSTGIS="3.2.1dev 3.2.0-339-gafaebf921" [EXTENSION] PGSQL="140" GEOS="3.11.0dev-CAPI-1.16.0" PROJ="8.1.0" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)"
>>> 
>>> There seem to be NO CHANGE between mine and yours spatial_ref_sys.sql
>>> in the source repository:
>>> 
>>> git diff afaebf921 4443d6989 spatial_ref_sys.sql # none
>>> 
>>>> But I "see" now they are in fact different. There's two one-character differences in the SRTEXT.
>>>> 
>>>> So mystery solved.
>>> 
>>> Well, next mystery is WHY there are those one-character
>>> differences if the spatial_ref_sys.sql in the repository
>>> if it hadn't changed between your version and my version ?
>> 
>> Checking the spatial_ref_sys.sql in the 3.2 branch and master, the difference is right there in the files.
> 
> Uhm, are you sure ? The following command doesn't show any difference:
> 
>      git diff origin/stable-3.2 origin/master spatial_ref_sys.sql
> 
>> Someone clever re-generated the spatial_ref_sys in master and changed
>> the multiple INSERTS into one huge multi-valued insert.
> 
> Nobody changed spatial_ref_sys.sql in master branch (currently
> pointing at 1052dfb9f689a8cca8ba038a00bf9377846df1cb) but the change
> you describe is in the "spatial-ref-sys-view" branch as of
> 1caa46edfbffad4d94ff3cf02c3250a7fa68df9c.

OK, right, but that is effectively the "master" I am testing. (Hard to test your changes without using your branch.) Replace my mentions of "master" with "spatial_ref_sys", so I'm testing "stable-3.2" against "spatial-ref-sys-view".

Compare the entries for EPSG:4893, there are those tiny changes, for whatever reason.


> So... the update is detecting the system-side changes as user changes
>> and preserving them?
> 
> The update is aiming at not loosing any data, so if some entries
> are not in the *new* system spatial_ref_sys_postgis table they are
> retained in spatial_ref_sys_user. The retained records having SRID
> value which are ALSO in the system table are reported as "overrides"
> in the postgis_full_version() output (did you try that?).
> 
>> I mean, that seems impossible to do practically, if
>> the user actually ALTERS the system codes, there's no way to distinguish
>> between changes the user created and changes the EPSG has introduced.
> 
> Can you fully describe the scenario you are concerned about ?

The one I just ran into. I have system entries showing up in my spatial_ref_sys_user table, not because I changed anything but because the EPSG entries in the 3.2 version of postgis are oh-so-slightly different from the ones in the 3.3 (currently spatial-ref-sys-view) version of postgis.

P


> 
>  - UPDATE spatial_ref_sys table before the upgrade ?
> 
>    In that case the updated rows will be found in
>    spatial_ref_sys_user after the upgrade
> 
>  - UPDATE spatial_ref_sys_postgis table after the upgrade ?
> 
>    In that case the updated rows will just *disappear*
>    on next upgrade
> 
>  - UPDATE spatial_ref_sys view after the upgrade ?
> 
>    In that case the updated rows will be put in
>    spatial_ref_sys_user (and user will be WARNED that
>    editing spatial_ref_sys is deprecated)
> 
> Changes introduced by EPSG and not yet found in the spatial_ref_sys_postgis
> table can be put by users in the spatial_ref_sys_user table, with
> auth_name/auth_srid of their choice. Upon upgrading PostGIS to next
> release they can decide whether or not to keep their manually added
> entries, comparing theirs with the system ones.
> 
> As an added bonus, a 1:1 matching comparison is performed by the
> upgrade procedure itself, which in that case will drop the manually
> added entries.
> 
> --strk;
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel