[postgis-users] Mulitple Geometry Columns in One Table

[Mark Cave-Ayland]

> -----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.