ability to separate user defined spatial_ref_sys from installed

[robe]

I thought we had this in our tracker already, but perhaps we only discussed it.

spatial_ref_sys is a semi-postgis / semi user-defined.

We have been battling the issue of how to separate these two so that upgrade is a bit less painless for as far back as I can remember. It wasn't so much of an issue when we had fewer users but now is becoming more of a major one.

1) strk proposed a while back making it a view and having two tables: spatial_ref_sys_master, spatial_ref_sys_custom and then spatial_ref_sys would be the union of the 2 and people would put all their custom entries in spatial_ref_sys_custom

2) I didn't like that idea too much because it might break apps and proposed we just have a bit switch instead where we would tag all the ones we input is postgis installed. And the remaining ones we would default user input to true so in theory nothing would change except for people being lazy and doing an insert like

INSERT INTO spatial_ref_sys VALUES (… without qualifying the tables.

strk also pointed out it might break the postgis_restore which assumes the structure.

3) I have a third idea which is a bit of a morph of the two. Keep spatial_ref_sys as a table, but add another table for custom spatial ref_sys records. Have custom spatial_ref_sys inherit from spatial_ref_sys but put in a DO INSTEAD OF trigger in spatial_ref_sys that we would put in after we have inserted all our records. The do instead would push updates to the custom_spatial_ref_sys

(It's really a revision of strk's #1)

[chodgson]

I think I like (3). Has the best elements of the other options, and I can't really think of a drawback - except the "overhead" of the extra table, but if we accept that we need to store the information about where a spatial_ref_sys value came from somewhere, this is a pretty minimal and also fairly explicit and clear way to do it. The inheritance and DO INSTEAD trigger are a little bit of magic but there's no reason not to take advantage of the tools Postgres offers us.

I think we need to have spatial_ref_sys inherit from custom_spatial_ref_sys though, right? So that the custom entries show up in the main table?

[dustymugs]

I just hit this issue today. Option 3 sounds the most backwards compatible to me and the most intuitive to the end user.

[robe]

Note it should be a non-issue for extensions since I explicitly list the spatial refs.

[dustymugs]

don't rub it in… it already hurts . Anyone care if I take a stab at option 3?

[strk]

any experiment is good. please consider upgrade paths, both soft and hard.

[dustymugs]

Will do. One question though. Should spatial_ref_sys (since custom_spatial_ref_sys inherits from it) allow duplicate SRIDs? Something like

SELECT * FROM spatial_ref_sys;

can return two 4326, one from each table…

[strk]

I think the operations to be supported are:

1. Add a custom entry giving it a SRID in the "user SRID" space
2. Override/change a system entry because it was found bogus for some reason (includes postgis upgrades)

In this regard, the custom_spatial_ref_sys should only accept SRIDs in the user range, and the spatial_ref_sys only those in the system range. I keep forgetting those range and always have to read them up in the liblwgeom.h file, btw (still lacking a documentation page).

[dustymugs]

Having custom_spatial_ref_sys only accept SRIDs in the user range sounds completely reasonable to me. What is the user range? From looking at my dev box, SRID_USER_MAXIMUM is 998999 but nothing indicating the minimum.

[strk]

Indeed we never defined it, because there's never been a separation of system vs. user srids. All we defined is that the max SRID at storage level and the max SRID in spatial_ref_sys. I remember I made a proposal for a set of ranges but it was never voted upon. Actually now that the mailing list archives moved I seem to only find wild archived message pointers :'(

Ref: http://trac.osgeo.org/postgis/ticket/1505#comment:7 The same list archive reference can be found in postgis_restore.pl

Paul: any idea about the mail archives urls ?

[strk]

Proposal found: ​http://lists.osgeo.org/pipermail/postgis-devel/2012-February/018440.html

It wasn't easy, in all that trac spam…

[strk]

you can see that message went unanswered. I'm really hating trac mail…

[dustymugs]

I remember that email. I didn't answer as I felt it wasn't my place to. Using all 24 bits sure looks nice.

[robe]

don't care to risk this for 2.1.0. Maybe 3.0. For extensions I'm just going to keep my range cludge.

[dustymugs]

Having to deal with separating spatial_ref_sys records between built-in and user is annoying. Hopefully for 2.2

[dustymugs]

Upgrade spatial_ref_sys to a partitioned scheme

[dustymugs]

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.

[dustymugs]

I really should test and update that patch…

[robe]

Milestone renamed

[strk]

Hey Bborie, ping ! I came here looking for some previous work about spatial_ref_sys after having noticed that CREATE EXTENSION postgis FROM unpackaged does not care about spatial_ref_sys being an _empty_ table (not even trying to fill it). Guess I'll file another ticket for that but maybe this work could include such an automatic fill/update of the system SRIDs too ?

3.0 being the current target it may be nice to push this in