0

I want to clip the geometries of my table by a polygon.
I have a query that does this just fine as a SELECT query:

WITH 
    clip AS
    (
        SELECT ST_GeomFromEWKT('SRID=26915;POLYGON ((379308.073398988 5232934.49102113,377638.690726783 5102722.64258913,537899.427258473 5097714.49457251,541238.192602883 5236273.25636554,379308.073398988 5232934.49102113))') AS geom
    ),
    new_geom AS
    (
        SELECT 
            input.gid,
            CASE
                WHEN ST_CoveredBy(input.geom, clip.geom) THEN input.geom
                ELSE ST_Multi( ST_Intersection(input.geom,clip.geom) )
            END AS geom
        FROM "rail_lines" AS input
        INNER JOIN clip 
        ON ( ST_Intersects(input.geom, clip.geom)
        AND NOT ST_Touches(input.geom, clip.geom) )
    )
SELECT * FROM new_geom;

However, when I use this same query to UPDATE the geometry of the input table the query either does not complete or does nothing (ie. does not alter the geometries of the input table):

WITH 
    clip AS
    (
        SELECT ST_GeomFromEWKT('SRID=26915;POLYGON ((379308.073398988 5232934.49102113,377638.690726783 5102722.64258913,537899.427258473 5097714.49457251,541238.192602883 5236273.25636554,379308.073398988 5232934.49102113))') AS geom
    ),
    new_geom AS
    (
        SELECT 
            input.gid,
            CASE
                WHEN ST_CoveredBy(input.geom, clip.geom) THEN input.geom
                ELSE ST_Multi( ST_Intersection(input.geom,clip.geom) )
            END AS geom
        FROM "rail_lines" AS input
        INNER JOIN clip 
        ON ( ST_Intersects(input.geom, clip.geom)
        AND NOT ST_Touches(input.geom, clip.geom) )
    )
UPDATE rail_lines
SET geom = new_geom.geom
FROM new_geom
WHERE new_geom.gid = rail_lines.gid;

Does anyone know what is happening here?
I'm also open to other (simpler) means to clipping a table's geometry in-place without creating a new table.

Update

I'll update my question based on the suggestion by @JohnPowellBarca.
Updating the table still doesn't work but it might help to illustrate the issue.

Here's the query I'm using. First, I create a new table based on the intersection with my area of interest. Then I use that new table to attempt to update the geometry of the original table.

CREATE TABLE rail_lines_clip AS
SELECT in_table.gid, in_table.geom
FROM 
    rail_lines AS in_table,
    aoi AS clip
WHERE ST_Intersects(in_table.geom, clip.geom);

UPDATE rail_lines
SET geom = rail_lines_clip.geom
FROM rail_lines_clip
WHERE rail_lines_clip.gid = rail_lines.gid;

And here's a visual of the result after running the query: enter image description here The area of interest (ie. aoi) is in black.
The new table (ie. rail_lines_clip) is in green.
And the original table (ie. rail_lines) is in red.

From my understanding, the geometry of rail_lines should be identical to that of rail_lines_clip.
Maybe I should just use that new table, and then drop the old table, and rename the new to old. Such as:

CREATE TABLE rail_lines_clip AS
SELECT in_table.gid, in_table.geom
FROM 
    rail_lines AS in_table,
    aoi AS clip
WHERE ST_Intersects(in_table.geom, clip.geom);

DROP TABLE IF EXISTS rail_lines;

ALTER TABLE IF EXISTS rail_lines_clip
RENAME TO rail_lines;

(Which works as expected, by the way).

But, I'm still curious: Has anyone else had success updating the geometry of a table in place?

  • 1
    There is nothing syntactically wrong with this query. I use similar constructs all the time. Are you sure that new_geom is changing anything? – John Powell Apr 11 '18 at 9:35
  • did you get results? if anything didn't work, give a shout. – ThingumaBob Apr 13 '18 at 20:50
  • @JohnPowellakaBarça - the SELECT query correctly returns the clipped geometry when I run it alone. However when I append the UPDATE to the end of the query that should alter the geometry of original table nothing happens. Any clue what could be happening here? – rumski20 Apr 30 '18 at 17:03
  • None, it looks OK, but hard to debug without access to data, sorry. Suggest you run it in two steps, ie, select into a new table and then use that to run an update. It might throw light onto what is happening. – John Powell May 1 '18 at 10:31
1

Try

WITH 
  clip AS (
    SELECT ST_GeomFromEWKT('SRID=26915;POLYGON ((379308.073398988 5232934.49102113,377638.690726783 5102722.64258913,537899.427258473 5097714.49457251,541238.192602883 5236273.25636554,379308.073398988 5232934.49102113))') AS geom
  )

UPDATE rail_lines
  SET geom = ST_Multi(ST_Intersection(geom, clip.geom))
  FROM clip
  WHERE ST_Relate(geom, clip.geom, 'T*T******')
RETURNING rail_lines.gid

I´m using a DE-9IM matrix and ST_Relate here (check this blog for some good explanation); your lines have to intersect, but may not either be contained by or only touch the polygon, so that the line interior has to intersect the polygon's interior and exterior in some way.

Finally, with RETURNING you can check if something has been updated; in this case, it will return all rail_lines.gid where an update happened. If there are no updates listed, your polygon might just not be where you expect it to be (double checked the coordinates? correct SRID?)

  • Sorry for the silence. I appreciate the novel use of DE-9IM (learned a lot from that blog post). However, I'm still getting the same result--the original table's geometry remains unchanged. – rumski20 Apr 30 '18 at 17:14
  • did you get any results from RETURNING? if you do a simple SELECT * FROM rail_lines WHERE ST_Intersects(geom, <your_polygon>), do you get any results then? if so, run WITH clip AS ( SELECT ST_GeomFromEWKT('SRID=26915;POLYGON ((379308.073398988 5232934.49102113,377638.690726783 5102722.64258913,537899.427258473 5097714.49457251,541238.192602883 5236273.25636554,379308.073398988 5232934.49102113))') AS geom ) SELECT GeometryType(ST_Intersection(geom, clip.geom)) FROM clip WHERE ST_Relate(geom, clip.geom, 'T*T******') and check the returned types for consistency and validity. – ThingumaBob May 1 '18 at 12:29
  • The GeometryType query returns something to the effect of: geometrytype LINESTRING LINESTRING MULTILINESTRING ... – rumski20 May 1 '18 at 14:39
0

The examples I see, are always updating inside the "with" https://www.postgresql.org/docs/9.6/static/queries-with.html

WITH t AS (
  UPDATE products SET price = price * 1.05
  RETURNING *
)
SELECT * FROM t;

I don’t have the database to test it, but you can try something like this:

WITH 
clip AS
(
    SELECT ST_GeomFromEWKT('SRID=26915;POLYGON ((379308.073398988 5232934.49102113,377638.690726783 5102722.64258913,537899.427258473 5097714.49457251,541238.192602883 5236273.25636554,379308.073398988 5232934.49102113))') AS geom
),
new_geom AS
(
    SELECT 
        input.gid,
        CASE
            WHEN ST_CoveredBy(input.geom, clip.geom) THEN input.geom
            ELSE ST_Multi( ST_Intersection(input.geom,clip.geom) )
        END AS geom
    FROM "rail_lines" AS input
    INNER JOIN clip 
    ON ( ST_Intersects(input.geom, clip.geom)
    AND NOT ST_Touches(input.geom, clip.geom) )
)
 UPDATE rail_lines
 SET geom = new_geom.geom
 WHERE new_geom.geom IN(SELECT geom FROM clip)
  • Thanks for the response. I like the idea here, but I think the final WHERE statement is missing some column identifiers. – rumski20 Apr 10 '18 at 20:26
  • Try the new change I just made, or try to do it in PL/pgSQL, some times doing the same in PL/pgSQL is far easier. – Alfredo Garcia Apr 10 '18 at 21:46

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.