[postgis-tickets] [PostGIS] #4987: Reorganize spatial_ref_sys

[PostGIS]

#4987: Reorganize spatial_ref_sys
-------------------------+---------------------------
 Reporter:  strk         |      Owner:  pramsey
     Type:  enhancement  |     Status:  new
 Priority:  medium       |  Milestone:  PostGIS 3.3.0
Component:  postgis      |    Version:  master
 Keywords:               |
-------------------------+---------------------------
 The current strategy of mixing custom and system records in the same
 spatial_ref_sys table is a bit complex and prone to errors on upgrade,
 whereas users changing a system record would see those records changed on
 upgrade.

 This ticket is to find a cleaner solution.

 My proposal is to turn spatial_ref_sys into a view and provide
 spatial_ref_sys_postgis containing the official entries and a
 spatial_ref_sys_user to contain custom/overridden entries.

 The view would pick records from spatial_ref_sys_user in preference to
 those in spatial_ref_sys_postgis.

 I've given this solution a try and with a non-materialized view the
 performance hit is that a _user table with ~100 overrides makes a query
 for a single SRID 2.5x slower than directly fetching that record from a
 table (but it's still around 2ms on my laptop).

 Test of my implementation is in
 https://gitlab.com/postgis/postgis/-/merge_requests/57

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4987>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.