Valid SRIDs

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Valid SRIDs

Kevin Neufeld
Not to long ago in the bug tracker, a user suggested that
ST_AddGeometryColumns() should error out if the supplied SRID is not in
spatial_ref_sys.

What does the team here think?  Should PostGIS be responsible for
ensuring correct parameters?  (We currently raise exceptions for all
other invalid parameters). If so, the solutions I see are:
1. Modify the plpgsql function to error if the SRID does not exist
2. Put a foreign key constrain on geometry_columns referencing
spatial_ref_sys.

Essentially, if we do this, loading spatial_ref_sys into PostGIS will no
longer be an optional part of the install process.  This also means that
we'll have to add a dummy record for an SRID of -1 and perhaps 0.

Personally, I'm undecided.  I like enforcing data integrity - it has
saved me many times in the past ... not to mention it's good practice.  
But putting the onus on the user also has it's pros.

Thoughts?
-- Kevin

Reply | Threaded
Open this post in threaded view
|

Re: Valid SRIDs

Stephen Frost
* Kevin Neufeld ([hidden email]) wrote:
> Not to long ago in the bug tracker, a user suggested that  
> ST_AddGeometryColumns() should error out if the supplied SRID is not in  
> spatial_ref_sys.

I'm not convinced that's the best approach, though I can understand the
desire to have FKs, etc.

> What does the team here think?  Should PostGIS be responsible for  
> ensuring correct parameters?  (We currently raise exceptions for all  
> other invalid parameters). If so, the solutions I see are:
> 1. Modify the plpgsql function to error if the SRID does not exist
> 2. Put a foreign key constrain on geometry_columns referencing  
> spatial_ref_sys.

Things, in my view anyway, get a fair bit more complicated when you
consider the changes we've been talking about to use PG's user-defined
typmods.  It feels like it'd be 'awkward' to enforce that check in the
typmod and would also mean, I believe, more difficulty when dealing with
dump/restores unless things are done in the right order.

I guess I just don't like the idea of having the addition of a column to
a table using the 'ALTER TABLE ... ADD COLUMN' syntax fail for a
less-than-critical error.  I'm also not a fan of having that code have
to go look up in a table what the valid values are, performance and
locking issues being my concern there.

On the other hand, I do feel differently about ST_AddGeometryColumns(),
and wouldn't be against adding a check in that function, possibly
capable of being overriden.  Adding such a check there strikes me as
less 'invasive' if you will, though if we add it there and people start
to depend on it, they'll be upset if we yank it out later when we move
to using user-defined typmods...

        Thanks!

                Stephen

signature.asc (189 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: Valid SRIDs

Obe, Regina     DND\MIS-3
In reply to this post by Kevin Neufeld
Kevin,

I personally like the idea, but I would throw out the geometry_columns
to spatial_ref_sys constraint check and just do the check in the
addgeometry call.

Eventually geometry_columns should be a view anyway not a table so
putting in a constraint check just to throw it out later seems silly.  I
think Paul also wanted to make 0 the standard rather than -1.  God
forbid we have both those entries in spatial_ref_sys.

 Regina

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of
Kevin Neufeld
Sent: Thursday, June 26, 2008 1:19 AM
To: PostGIS Development Discussion
Subject: [postgis-devel] Valid SRIDs

Not to long ago in the bug tracker, a user suggested that
ST_AddGeometryColumns() should error out if the supplied SRID is not in
spatial_ref_sys.

What does the team here think?  Should PostGIS be responsible for
ensuring correct parameters?  (We currently raise exceptions for all
other invalid parameters). If so, the solutions I see are:
1. Modify the plpgsql function to error if the SRID does not exist
2. Put a foreign key constrain on geometry_columns referencing
spatial_ref_sys.

Essentially, if we do this, loading spatial_ref_sys into PostGIS will no

longer be an optional part of the install process.  This also means that

we'll have to add a dummy record for an SRID of -1 and perhaps 0.

Personally, I'm undecided.  I like enforcing data integrity - it has
saved me many times in the past ... not to mention it's good practice.  
But putting the onus on the user also has it's pros.

Thoughts?
-- Kevin
_______________________________________________
postgis-devel mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-devel
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.


Reply | Threaded
Open this post in threaded view
|

Re: Valid SRIDs

Tom Lane
In reply to this post by Stephen Frost
Stephen Frost <[hidden email]> writes:
> Things, in my view anyway, get a fair bit more complicated when you
> consider the changes we've been talking about to use PG's user-defined
> typmods.  It feels like it'd be 'awkward' to enforce that check in the
> typmod and would also mean, I believe, more difficulty when dealing with
> dump/restores unless things are done in the right order.

Whichever way it's done, you need to worry about whether pg_dump will
understand it and dump the tables in the right order.  It would be very
easy to have a situation where spatial_ref_sys doesn't get populated
until after user tables containing geometry values.  I think only a
foreign key constraint would currently be guaranteed to work properly.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: Valid SRIDs

Stephen Frost
Tom,

* Tom Lane ([hidden email]) wrote:
> I think only a foreign key constraint would currently be guaranteed
> to work properly.

If the geometry columns are using user-defined typmod's, as we've
discussed, and those are done in a pg_dump as 'create table ...' or even
'alter table ... add column ...' statements, I don't see how even
creating a foreign key would help.  The FK could work for
geometry_columns to spatial_ref_sys today, but with user-defined typmods
geometry_columns (hopefully) becomes just a view off of the system
catalogs.

I just don't see any way that during a restore we could ensure that
things work in a situation where the user-defined typmod functions are
checking the spatial_ref_sys table to ensure the srid being added is
valid.  The only thing I'm coming up with would involve another GUC
which would have to be set at the start of the dump as, basically,
"don't check the srid", with it defaulting to "check" for normal
operations.  I'm not sure there's even a way for an add-on module like
PostGIS to add a GUC though, and I don't see enough justification to
even try to have a 'generic'/module-agnostic GUC added to core to
support this.

        Thanks,

                Stephen

signature.asc (189 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Valid SRIDs

Kevin Neufeld
In reply to this post by Tom Lane

Tom Lane wrote:

> Stephen Frost <[hidden email]> writes:
>> Things, in my view anyway, get a fair bit more complicated when you
>> consider the changes we've been talking about to use PG's user-defined
>> typmods.  It feels like it'd be 'awkward' to enforce that check in the
>> typmod and would also mean, I believe, more difficulty when dealing with
>> dump/restores unless things are done in the right order.
>
> Whichever way it's done, you need to worry about whether pg_dump will
> understand it and dump the tables in the right order.  It would be very
> easy to have a situation where spatial_ref_sys doesn't get populated
> until after user tables containing geometry values.  I think only a
> foreign key constraint would currently be guaranteed to work properly.
>
> regards, tom lane

Agreed.  Just thinking out loud here ... What if PostgreSQL offered the
ability to put foreign keys on functional references ... kind of like
functional indexes?

ALTER TABLE foo
   ADD CONSTRAINT foo_func_fkey
   FOREIGN KEY (ST_Srid(the_geom))
   REFERENCES public.spatial_ref_sys (srid);

I think this would pd_dump in the correct order.  But again we have the
problem of the spatial_ref_sys turning into a view down the road.

-- Kevin

Reply | Threaded
Open this post in threaded view
|

Re: Valid SRIDs

Markus Schaber
In reply to this post by Stephen Frost
Hi, Stephen,

Stephen Frost <[hidden email]> wrote:

> On the other hand, I do feel differently about ST_AddGeometryColumns(),
> and wouldn't be against adding a check in that function, possibly
> capable of being overriden.  Adding such a check there strikes me as
> less 'invasive' if you will, though if we add it there and people start
> to depend on it, they'll be upset if we yank it out later when we move
> to using user-defined typmods...

We can always check inside ST_AddGeometryColumn(), whether the
implementation uses geometry_columns or typemod.

Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Reply | Threaded
Open this post in threaded view
|

Re: Valid SRIDs

Stephen Frost
Markus,

* Markus Schaber ([hidden email]) wrote:

> Stephen Frost <[hidden email]> wrote:
> > On the other hand, I do feel differently about ST_AddGeometryColumns(),
> > and wouldn't be against adding a check in that function, possibly
> > capable of being overriden.  Adding such a check there strikes me as
> > less 'invasive' if you will, though if we add it there and people start
> > to depend on it, they'll be upset if we yank it out later when we move
> > to using user-defined typmods...
>
> We can always check inside ST_AddGeometryColumn(), whether the
> implementation uses geometry_columns or typemod.
That's a good point.  An issue there would be the inconsistancy of the
checking, but, honestly, I'm on the fence about if that's really a bad
thing or not.  It would allow us to reload the data using regular
pg_dump syntax, etc, and still do the checking for applications which
are using the standard interfaces.

        Thanks!

                Stephen

signature.asc (189 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Valid SRIDs

Tom Lane
In reply to this post by Stephen Frost
Stephen Frost <[hidden email]> writes:

> I just don't see any way that during a restore we could ensure that
> things work in a situation where the user-defined typmod functions are
> checking the spatial_ref_sys table to ensure the srid being added is
> valid.  The only thing I'm coming up with would involve another GUC
> which would have to be set at the start of the dump as, basically,
> "don't check the srid", with it defaulting to "check" for normal
> operations.  I'm not sure there's even a way for an add-on module like
> PostGIS to add a GUC though, and I don't see enough justification to
> even try to have a 'generic'/module-agnostic GUC added to core to
> support this.

It is possible for an add-on to define its own GUC variable, but the
hard part would be getting consensus for pg_dump to emit commands to
set a variable that might not exist.  There are lots of usage modes
in which errors during restore aren't acceptable, even though they
might be "harmless".

                        regards, tom lane