0

I am working in Postgres 9.6. I have a table called builtup_areas:

                                          Table "public.builtup_areas"
    Column    |             Type             | Collation | Nullable |                    Default                     
--------------+------------------------------+-----------+----------+------------------------------------------------
 ogc_fid      | integer                      |           | not null | nextval('builtup_areas_ogc_fid_seq'::regclass)
 objectid     | bigint                       |           |          | 
 wkb_geometry | geometry(MultiPolygon,27700) |           |          | 

And a table called local_authorities:

                                           Table "public.local_authorities"
     Column     |           Type           | Collation | Nullable |                      Default                       
----------------+--------------------------+-----------+----------+----------------------------------------------------
 ogc_fid        | integer                  |           | not null | nextval('local_authorities_ogc_fid_seq'::regclass)
 wkb_geometry   | geometry(Geometry,27700) |           |          | 
 canonical_name | character varying        |           |          | 

I am trying to create a new table that contains the ST_Difference between the local authority's boundary, and the built-up areas, for each local authority. I have made a new table to hold this, with a MultiPolygon column:

CREATE TABLE las_without_buas(gid serial primary key, geom geometry(MultiPolygon,27700), county varchar(1000));

This is my code:

INSERT INTO las_without_buas(county, geom) 
  SELECT 
    county, 
    ST_Multi(ST_Difference(la.geom, bua.wkb_geometry)) AS geom 
  FROM
    (SELECT canonical_name AS county, wkb_geometry AS geom FROM local_authorities) la, 
  builtup_areas bua;

But when I run it, I get this error:

ERROR:  Geometry type (GeometryCollection) does not match column type (MultiPolygon)

I don't understand why it's trying to insert a GeometryCollection when I've specifically used ST_Multi to return a MultiPolygon. Any suggestions?

1

I would guess that it is already ST_Difference that is returning geometry collections. They can't be expressed with any multigeometry. You may get for example empty results as GEOMETRYCOLLECTION EMPTY http://postgis.net/docs/ST_Difference.html.

SELECT ST_AsText(ST_Multi(
(
    ST_Difference(
            ST_GeomFromText('LINESTRING(50 100, 50 200)'),
            ST_GeomFromText('LINESTRING(50 100, 50 200)')
        )
    )));

Add a filter that passes only polygons and multipolygons from ST_Difference for ST_Multi.

  • Thanks! Could you give me a hint as to how I might add a filter? (Google is failing me, but just need a hint!) – Richard Oct 29 '18 at 15:21
  • 1
    postgis.net/docs/GeometryType.html where GeometryType(....) in ('POLYGON','MULTIPOLYGON). – user30184 Oct 29 '18 at 15:47

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.