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.