[postgis-users] Mulitple Geometry Columns in One Table

Wed Aug 18 01:32:32 PDT 2004

Mark, I've just committed a change similar to what you're proposing.
Constraints will be named:
	enforce_srid_<fieldname>
	enforce_geomtype_<fieldname>

If you want to help you might try modifying
rename_geometry_table_constraints().
I've spent some time on it and finally I've decided to obsolete it.

No functions currently rely on constraints name so having constraints
with unconsistent names should not be a problem.

The problem in finding out column name from
rename_geometry_table_constraints() is that consrc might be in
the following forms:
	((geometrytype(the_geom) == ....
	((geometrytype("MyGeom") == ....
	((geometrytype(public."MyGeom") == ....
	((geometrytype("MySchema"."MyGeom") == ....
	((geometrytype("MySchema".the_geom) == ....

AFAICS there's no reference to the field being checked in pg_constraint
so I tried using substr (7.1 compatible).
The only viable way I see is using CASE statements, but I'm not sure
we can rely on future values in connsrc for this to work.

Sorry for the inconveniences.

--strk;

On Wed, Aug 18, 2004 at 08:39:54AM +0100, Mark Cave-Ayland wrote:
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net 
> > [mailto:postgis-users-bounces at postgis.refractions.net] On 
> > Behalf Of Steffen Macke
> > Sent: 18 August 2004 07:32
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] Mulitple Geometry Columns in One Table
> > 
> > 
> > Hello All,
> > 
> > previously (up to PostGIS 0.8.0), it was possible to have
> > more than one geometry column in a table. With the current 
> > CVS version this is impossible (postgis.sql.in version 1.74).
> > 
> > AddGeometryColumn() fails reporting an error that the constraint 
> > "enforce_srid"
> > already exists.
> > 
> > After commenting out the following 4 lines in 
> > AddGeometryColumn(), adding a second geometry column works again:
> > 
> > EXECUTE 'ALTER TABLE ' ||
> > 
> >      quote_ident(real_schema) || '.' || quote_ident(table_name)
> > 
> >          || ' ADD CONSTRAINT enforce_srid CHECK (SRID(' || 
> > quote_ident(column_name) ||
> >          ') = ' || new_srid || ')' ;
> > 
> > Is it possible to check for existence of the constraint 
> > before? Or does 
> > it make more sense
> > to include the geometry column name in the constraint name (e.g. 
> > enforce_shape_srid)?
> > 
> > Regards,
> > 
> > Steffen
> 
> 
> Hi Steffen,
> 
> Good spot. I think that the best thing would be to include the geometry
> column in the constraint name, so that when you query the table in psql
> it is easy to spot which constraint belongs to which column. Could you
> try replacing the table checks section in postgis.sql with the following
> instead:
> 
> 
> 	-- Add table checks
> 
> 	EXECUTE ''ALTER TABLE '' || 
> 
> 		quote_ident(real_schema) || ''.'' ||
> quote_ident(table_name)
> 
> 		|| '' ADD CONSTRAINT enforce_'' || column_name ||
> ''_srid CHECK (SRID('' || quote_ident(column_name) ||
> 		'') = '' || new_srid || '')'' ;
> 
> 	IF (not(new_type = ''GEOMETRY'')) THEN
> 		EXECUTE ''ALTER TABLE '' || 
> 
> 		quote_ident(real_schema) || ''.'' ||
> quote_ident(table_name)
> 
> 		|| '' ADD CONSTRAINT enforce_'' || column_name ||
> ''_geotype CHECK (geometrytype('' ||
> 		quote_ident(column_name) || '')='' ||
> 		quote_literal(new_type) || '' OR ('' ||
> 		quote_ident(column_name) || '') is null)'';
> 	END IF;
> 
> 
> If this works for you, I'll commit the change into CVS.
> 
> 
> Kind regards,
> 
> Mark.
> 
> ---
> 
> Mark Cave-Ayland
> Webbased Ltd.
> Tamar Science Park
> Derriford
> Plymouth
> PL6 8BX
> England
> 
> Tel: +44 (0)1752 764445
> Fax: +44 (0)1752 764446
> 
> 
> This email and any attachments are confidential to the intended
> recipient and may also be privileged. If you are not the intended
> recipient please delete it from your system and notify the sender. You
> should not copy it or use it for any purpose nor disclose or distribute
> its contents to any other person.
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

--===============0819578935==--

On Wed, Aug 18, 2004 at 08:39:54AM +0100, Mark Cave-Ayland wrote:
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net 
> > [mailto:postgis-users-bounces at postgis.refractions.net] On 
> > Behalf Of Steffen Macke
> > Sent: 18 August 2004 07:32
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] Mulitple Geometry Columns in One Table
> > 
> > 
> > Hello All,
> > 
> > previously (up to PostGIS 0.8.0), it was possible to have
> > more than one geometry column in a table. With the current 
> > CVS version this is impossible (postgis.sql.in version 1.74).
> > 
> > AddGeometryColumn() fails reporting an error that the constraint 
> > "enforce_srid"
> > already exists.
> > 
> > After commenting out the following 4 lines in 
> > AddGeometryColumn(), adding a second geometry column works again:
> > 
> > EXECUTE 'ALTER TABLE ' ||
> > 
> >      quote_ident(real_schema) || '.' || quote_ident(table_name)
> > 
> >          || ' ADD CONSTRAINT enforce_srid CHECK (SRID(' || 
> > quote_ident(column_name) ||
> >          ') = ' || new_srid || ')' ;
> > 
> > Is it possible to check for existence of the constraint 
> > before? Or does 
> > it make more sense
> > to include the geometry column name in the constraint name (e.g. 
> > enforce_shape_srid)?
> > 
> > Regards,
> > 
> > Steffen
> 
> 
> Hi Steffen,
> 
> Good spot. I think that the best thing would be to include the geometry
> column in the constraint name, so that when you query the table in psql
> it is easy to spot which constraint belongs to which column. Could you
> try replacing the table checks section in postgis.sql with the following
> instead:
> 
> 
> 	-- Add table checks
> 
> 	EXECUTE ''ALTER TABLE '' || 
> 
> 		quote_ident(real_schema) || ''.'' ||
> quote_ident(table_name)
> 
> 		|| '' ADD CONSTRAINT enforce_'' || column_name ||
> ''_srid CHECK (SRID('' || quote_ident(column_name) ||
> 		'') = '' || new_srid || '')'' ;
> 
> 	IF (not(new_type = ''GEOMETRY'')) THEN
> 		EXECUTE ''ALTER TABLE '' || 
> 
> 		quote_ident(real_schema) || ''.'' ||
> quote_ident(table_name)
> 
> 		|| '' ADD CONSTRAINT enforce_'' || column_name ||
> ''_geotype CHECK (geometrytype('' ||
> 		quote_ident(column_name) || '')='' ||
> 		quote_literal(new_type) || '' OR ('' ||
> 		quote_ident(column_name) || '') is null)'';
> 	END IF;
> 
> 
> If this works for you, I'll commit the change into CVS.
> 
> 
> Kind regards,
> 
> Mark.
> 
> ---
> 
> Mark Cave-Ayland
> Webbased Ltd.
> Tamar Science Park
> Derriford
> Plymouth
> PL6 8BX
> England
> 
> Tel: +44 (0)1752 764445
> Fax: +44 (0)1752 764446
> 
> 
> This email and any attachments are confidential to the intended
> recipient and may also be privileged. If you are not the intended
> recipient please delete it from your system and notify the sender. You
> should not copy it or use it for any purpose nor disclose or distribute
> its contents to any other person.
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users