I have a multipolygon layer in which I want to dissolved based on a class column. The steps I'm doing are:

  1. create table tc_2010_00567 (gid int primary key, tc_2010 text(32));
  2. select addGeometryColumn('tc_2010_00567', 'the_geom', 4618, 'multipolygon');
  3. insert into tc_2010_00567 (gid, tc_2010, the_geom) select NULL, tc_2010, st_union(geometry) from tc_2010_00567__pol group by tc_2010;

I can create the table and add the geometry column (steps 1 and 2) but step 3 gives me the error IntegrityError: tc_2010_00567.the_geom violates Geometry constraint [geom-type or SRID not allowed]

My input dataset has SRID:4618 The select part (in step 3) works and I can load it in QGis (using QspatiaLite). But the insert into part is not working. Any clues as to what could be wrong? I'm using QSpatiaLite from QGis 2.8.1 and SpatiaLilte 4.1.1

  • 1
    As a quick workaround create table as gid int, tc_2010 text, the_geom geometry, insert features and see what you get there. Perhaps some union is a simple polygon and you should cast it to multipolygon. BTW. I wonder how you could insert all NULLs to primary key column. – user30184 Jun 15 '15 at 18:01
  • BINGO! One of the results of st_union was of type Polygon. Using CastToMultiPolygon worked! Please convert your comment to an answer so I can mark it as solved. As for the NULLs in the primary key, I read that if it's you insert null into the PK, it will autoincrement. But that is not working. Will look into that now. – Daniel Jun 15 '15 at 18:51
  • About the NULL value. From SQLITE FAQ, when NULL is inserted into an integer primary key, it will autoincrement (sqlite.org/faq.html#q1) – Daniel Jun 15 '15 at 19:00

The error message gives hints about what can be wrong:

IntegrityError: tc_2010_00567.the_geom violates Geometry constraint [geom-type or SRID not allowed]

Error messages may be misleading and the real issue may be somewhere else but it is worth checking the suggested reasons for the error first.

In your case the input dataset is in EPSG:4618 and so is the geometry column of the output table defined. ST_Union does not change SRID so probably error does not come from SRID.

The other part is geometry type. Geometry column of the output table is defined to accept only multipolygons. Easiest way for checking what geometrytypes your query is returning is to modify the SQL a bit

SELECT distinct ST_GeometryType(ST_Union(geometry)) from tc_2010_00567__pol group by tc_2010;

If there are other geometrytypes than multipolygons the insert will fail. In the most common case union is producing also simple polygons. If that is the case, use CastToMultiPolygon

SELECT CastToMultiPolygon(ST_Union(geometry)) from tc_2010_00567__pol group by tc_2010;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.