[GIS] PostGIS do not want GeometryCollection in output

geometrycollectionintersectionpostgis

I am intersecting two layers. 1. truckmun which has multipolygon and polygon geometries in it 2. streamsw which is just multistring. The SRID is 3424 for both layers.

Here is my intersecting query

create table streamsw2 as
select truckmun.mun as mun,truckmun.county as county, st_intersection(truckmun.geom,streamsw.geom2d) as geom 
from truckmun,streamsw 
where st_intersects(truckmun.geom,streamsw.geom2d);

the query runs but spits out geometry types of St_geometrycollection, st_linestring and st_multilinestring

to get an idea of the layers being intersected the truckmun layer was created by buffering a network and using the st_intersection function with municipaliites. streamsw is just a multilinestring streams layer that at certain locations spatially intersects the truckmun layer.

My goal here is to 1. understand why the geometry collection exists 2. not have the geometry collection in the new table of streamsw2

Best Answer

create table streamsw2 as
SELECT *
FROM (
    select 
        truckmun.mun as mun,
        truckmun.county as county, 
        (ST_Dump(st_intersection(truckmun.geom,streamsw.geom2d))).geom as geom 
    from truckmun,streamsw 
    where st_intersects(truckmun.geom,streamsw.geom2d)
) a
WHERE ST_GeometryType(geom) ILIKE '%linestring';

or

create table streamsw2 as
select 
    truckmun.mun as mun,
    truckmun.county as county, 
    ST_CollectionExtract(st_intersection(truckmun.geom,streamsw.geom2d), 2) as geom 
from truckmun,streamsw 
where st_intersects(truckmun.geom,streamsw.geom2d)

The ST_Intersection is not always able to represent the result as a simple geometry type, that's why you're seing GeometryCollection in the output.

Refer to ST_Dump and ST_CollectionExtract for more info.

Related Question