[postgis-users] constraint issue in 2.0.2 r10789

[Nicolas Ribot]

Hi,

By calling AddGeometryColumn('public', profiles_line_wgs84', 'geom', 4326,
'LINESTRING', 2), Postgis 2.0 created a geometry column of type
geometry(LineString,4326)
that restrict that column to LINESTRINGs only.
The constraint defined later does not change this restriction.

To enable the older constraint mechanism (and define the geom column type
as 'geometry'), call addGeometryColumn with 'false' as final parameter (
http://postgis.refractions.net/docs/AddGeometryColumn.html).:
SELECT AddGeometryColumn('public', 'profiles_line_wgs84', 'geom', 4326,
'LINESTRING', 2, false);

By the way, do you really need to store heterogenous geometries ? It may
prevent you from using some functions that will not handle both types.
A trigger on the geom column could convert LINESTRINGs to MULTI by calling
st_multi. ;)

Nicolas




On 15 December 2012 12:48, Gery . <gamejihou at hotmail.com> wrote:

>  Hello,
>
> I recently upgraded both my postgresql (from 8.4.4 to 9.2.2) and postgis
> (from 1.5.3 to 2.0.2 r10789). With the previous versions I run thesecommands without problems:
>
> [code]
> CREATE TABLE profiles_line_wgs84(
> GID SERIAL,
> ID SMALLINT,
> FID SMALLINT,
> SURVEY VARCHAR(100),
> PROFILE VARCHAR(100),
> TYPE VARCHAR(100),
> SOURCE VARCHAR(100),
> NR_TRACES VARCHAR(100),
> TRACE_SPACE_M VARCHAR(100),
> LENGTH_M FLOAT,
> COMMENTS TEXT
> );
> SELECT AddGeometryColumn('public', profiles_line_wgs84', 'geom', 4326,
> 'LINESTRING', 2);
> CREATE INDEX profiles_line_wgs84_geom ON profiles_line_wgs84 USING GIST (
> geom );
>
> ALTER TABLE profiles_line_wgs84 DROP CONSTRAINT enforce_geotype_geom;
> ALTER TABLE profiles_line_wgs84 ADD CONSTRAINT enforce_geotype_geom CHECK
> ((geometrytype(geom) = ANY (ARRAY['MULTILINESTRING'::text,
> 'LINESTRING'::text])) OR geom IS NULL);
> [/code]
>
> now with postgis 2.0.2 I get these errors:
>
> [code]
> CREATE TABLE
>                              addgeometrycolumn
> ---------------------------------------------------------------------------
>  public.seismic_profiles_line_wgs84.geom SRID:4326 TYPE:LINESTRING DIMS:2
> (1 row)
>
> CREATE INDEX
> psql:13_seismic_profiles_mergecsvshp2line.sql:119: ERROR:  constraint
> "enforce_geotype_geom" of relation "profiles_line_wgs84" does not exist
> psql:13_seismic_profiles_mergecsvshp2line.sql:128: ERROR:  Geometry type
> (MultiLineString) does not match column type (LineString)
> [/code]
>
> it seems  to me that the constraint statement is ok, or is it something
> missing? any idea is appreciated,
>
> Thanks in advance,
>
> Best regards,
>
>
> __________________________________________________________________________________________
> Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si
> NO es necesario.
> Think green - keep it on the screen. Do NOT print if it is NOT necessary.
> Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken
> Sie NICHT, wenn es NICHT notwendig ist.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121215/0267e989/attachment-0001.html>