I create a table with a geometry column (geom2)

 CREATE TABLE reu_oin_oi2 AS
 (SELECT eingangsdaten.*,
 ST_Intersection (test.geom, eingangsdaten.geom) as geom2
 FROM test, eingangsdaten
 JOIN test b
 ON ST_Intersects (eingangsdaten.geom, b.geom)
 WHERE test.id = 2);

Now some informations for the geometry column are missing (type of polygon ('Polygon') and SRID) How can I add these inside the ST_Intersection command?


Try Populate_Geometry_Columns() after your query.

In your case:

SELECT Populate_Geometry_Columns('reu_oin_oi2'::regclass::oid);

However, this will only work for those columns that have the same geometry type for each row. The result of ST_Intersection is often very diverse (e.g. a mix of ST_MultiPolygon, ST_Polygon and ST_GeometryCollection). You may want to use ST_Dump():

CREATE TABLE reu_oin_oi2 AS (
        (ST_Dump(ST_Intersection (test.geom, eingangsdaten.geom) AS geom2
        test b
        ST_Intersects (eingangsdaten.geom, b.geom)
        test.id = 2

SELECT Populate_Geometry_Columns('reu_oin_oi2'::regclass::oid);

However, be aware that in this way, any intersection between a geom from the eingangsdaten table and a geom from the test table that consists of more than one piece, will result in one than one row in your reu_oin_oi2 table.

  • the result is null. I can add further informations for a geometry column with ALTER TABLE reu_oin_oi2 ALTER COLUMN geom2 TYPE geometry(MultiPolygonZ,4326) USING ST_Multi(geom); but then my polygon seems to be not correct anymore...
    – Moehre
    Feb 16 '17 at 10:02
  • @Moehre, what you have said here in your comment is what I would have suggested trying... you should add this to your question. How is the polygon not correct after this?
    – Matt
    Feb 16 '17 at 10:14
  • inside the result table of the ST_Intersection(schablone.geom, eingangsdaten.geom) are different geometries (like MultiPolygon, GeometryCollection). I have to use a tool which is importing the table in QGIS but therefore I need to add Informations for the geometry column (SRID, typ of geometry). Is it possible to convert the different types of geometries in one typ? hope you get it...
    – Moehre
    Feb 16 '17 at 10:21

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.