Geometry type issue with st_intersection() between polylines and polygons

Question

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)

--

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.

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.

Answer 1

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

Answer 2

OK, just found the solution on my own:

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));

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

Answer 3

Other solution to this issue:

1. Create a new table with the st_intersection() function:

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

2. At this stage, geometry column's type is geometry with no more precision on the type of geometry (point, linestring, polygon, etc.). It is not possible to set a more accurate type:

   ALTER TABLE test ALTER COLUMN geom TYPE geometry(MultiLineString) USING geom::geometry(MultiLineString);
   
   -- Error message:
   
   -- ERROR:  Geometry type (LineString) does not match column type (MultiLineString)
   

3. Then, let's check geometry type of test table:

   SELECT DISTINCT GeometryType(the_geom) FROM test;
   
   -- Result:
   
   -- geom
   -- geometry
   -- ----------------
   -- LINESTRING
   -- MULTILINESTRING
   

   There are 2 geometry types. This is why changing the type of geometry column is not accepted.

4. Update the geometry column for LINESTRING rows:

   UPDATE test SET geom = st_multi(geom) WHERE GeometryType(geom) = 'LINESTRING';
   

5. Now, defining geometry column type is possible:

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

Hope this can help!