[postgis-tickets] [PostGIS] #1831: ability to separate user defined spatial_ref_sys from installed

[PostGIS]

#1831: ability to separate user defined spatial_ref_sys from installed
-------------------------+--------------------------------------------------
 Reporter:  robe         |       Owner:  dustymugs    
     Type:  enhancement  |      Status:  assigned     
 Priority:  medium       |   Milestone:  PostGIS 2.2.0
Component:  postgis      |     Version:  trunk        
 Keywords:               |  
-------------------------+--------------------------------------------------
Changes (by dustymugs):

  * type:  defect => enhancement


Comment:

 I've attached a patch for upgrading the '''spatial_ref_sys''' table to a
 partitioned scheme.

 !New/Modified tables (in ''postgis/postgis.sql.in'') are:

 1. '''spatial_ref_sys''' is now a "template" table with no constraints

 2. '''spatial_ref_sys_dist''' is an inherited table of
 '''spatial_ref_sys''' and contains the spatial references systems provided
 by PostGIS. Has constraints restricting srid > 0 AND srid <= 900913

 3. '''spatial_ref_sys_user''' is an inherited table of
 '''spatial_ref_sys''' and contains all user-specified spatial reference
 systems. Has constraints restricting srid > 900913 AND srid < SRID_USR_MAX

 Better table names are welcome for *_dist and *_user.

 INSERT, UPDATE, DELETE and TRUNCATE triggers are set on
 '''spatial_ref_sys''' and '''spatial_ref_sys_dist'''. All INSERT, UPDATE,
 DELETE and TRUNCATE statements to '''spatial_ref_sys''' are applied to
 '''spatial_ref_sys_user''' instead. All INSERT, UPDATE, DELETE and
 TRUNCATE statements to '''spatial_ref_sys_dist''' raise a NOTICE and do
 nothing.

 ''spatial_ref_sys.sql'' temporarily disables the triggers above, uses a
 rule to redirect the INSERT statements to spatial_ref_sys_dist, and
 enables the triggers at the very end.

 The files ''postgis/spatial_ref_sys_upgrade.sql.in'' and
 ''utils/spatial_ref_sys_srid.pl'' have been added to provide an easy
 mechanism to upgrade an existing '''spatial_ref_sys''' table to the
 partitioned scheme. ''utils/spatial_ref_sys_srid.pl'' parses
 '''spatial_ref_sys.sql''' for the list of SRIDs provided by PostGIS and
 burns the list into ''postgis/spatial_ref_sys_upgrade.sql''.

 ''postgis/spatial_ref_sys_upgrade.sql'' does the following:

 1. Rename the existing ''spatial_ref_sys' to '''spatial_ref_sys_old'''

 2. Creates the partitioned scheme

 3. Copy the appropriate SRIDs to '''spatial_ref_sys_dist''' or
 '''spatial_ref_sys_user'''.

 4. Delete '''spatial_ref_sys_old''' if all records in it have been copied.
 Otherwise, it is left for the user to resolve those records that couldn't
 be imported.

 To apply the patch (assuming the patch is in the base directory)

 {{{
 patch -p1 -i srs.patch
 }}}

 Regressions currently fail becase there are tests inserting SRIDs not in
 the approved user-range or NOTICE messages are being emitted when triggers
 are fired.

 I've not played with adding support for EXTENSIONs as that's greek to me.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/1831#comment:15>
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.