1

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

  • Check if the table that was created by the query has a geometry field defined as multilinestring. If it does try to create the target table beforehand and define the geometry field as a generic "geometry". – user30184 Nov 28 '16 at 21:50
2
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.

  • cool that worked perfect – ziggy Nov 29 '16 at 17:52

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.