2

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.

3

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.

  • 1
    This is not a good solution for the query will generate two geometry columns: geom and st_intersection. When opened with QGIS, the column geom is used to display the lines so the intersection (clipping) is not performed... Any help would be welcome! – wiltomap Sep 19 '16 at 14:23
3

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
  • Thanks @tilt, but st_multi() does not do the job for me... I will post an answer with a solution I found afterward. – wiltomap Feb 7 '17 at 14:57
1

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!

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.