[GIS] Geometry type issue with st_intersection() between polylines and polygons

clipintersectionpostgisqgisspatial statistics

I clip a polylines vector layer with a polygon vector layer, containing one single polygon. I need my polylines to be clipped at the polygon's boundaries.

CREATE TABLE test AS (SELECT a.gid, st_intersection(a.geom, b.geom) FROM lines a, polygon b WHERE st_intersects(a.geom, b.geom));

I can't visualize the test table in QGIS because the type of geom column seems not to be recognized (see image below, which is DB Manager GUI). Thus I tried to specify it within pgAdmin:

ALTER TABLE test ALTER COLUMN geom TYPE geometry(MultiLineString) USING geom::geometry(MultiLineString);

This issues the following error message:

ERROR: Geometry type (LineString) does not match column type (MultiLineString)

enter image description here

—

EDIT :

I tried to keep the geometry column from the polylines layer together with the st_intersection() function. 

CREATE TABLE test AS (SELECT a.gid, a.geom, st_intersection(a.geom, b.geom) FROM lines a, polygon b WHERE st_intersects(a.geom, b.geom));

This allows to display the table within QGIS but as there are 2 geometry columns produced, the clipping is not displayed and QGIS considers that 2 tables were created.

enter image description here

I need to make a clean clipping of my polylines layer resulting in a new table with a geometry columns having geometry(MultiLineString) type and displaying well in DB Manager.

Best Answer

The ST_Intersection can produce different geometry-types in one go (image two lines intersecting at one point, returning a point) and likely therefore you are receiving linestrings and multilinestrings at the same time.

Try wrapping your ST_Intersection() in ST_Multi() so that it always returns a multilinestring, even when it is just a linestring, and check your output for anything else then a line (points).

As a bonus: try to alias your functions so they produce nice column names. So:

SELECT ST_Intersection(geomA, geomB) AS geom
Related Question