2

PostgreSQL 14.6, Postgis 3.2

I have two tables, new_geom and original_geom

new_geom is a derivation of original_geom that merged some lines that touched and had the same properties. It worked fairly well, but created some errant line segments. I am now trying to remove those segments.

Below I am getting the difference between the new geom and the old geom:

WITH original_geom AS (
    SELECT (st_collect(geom)) AS geom FROM original_geom_backup
)
SELECT ogc_fid, ST_Difference(c.geom, og.geom) AS geom
FROM new_geom c JOIN original_geom og ON NOT st_within(c.geom, og.geom)
ORDER BY ogc_fid

Next I want to use that result to remove those "difference segments" from the new lines:

WITH original_geom AS (
    SELECT (st_collect(geom)) AS geom FROM original_geom_backup
)
, difference AS (
    SELECT ogc_fid, ST_Difference(ng.geom, og.geom) AS geom
    FROM new_geom ng JOIN original_geom og ON NOT st_within(ng.geom, og.geom)
    ORDER BY ogc_fid
)
SELECT ng.ogc_fid, ST_Difference(ng.geom, d.geom) FROM new_geom ng join difference d on ng.ogc_fid = d.ogc_fid

However, this returns the new_geom geoms completely unmodified. How can I rewrite this query to actually remove those differences?

EDIT:

The original lines are in black, the new in red. The red line was transformed into a line comprising:

  • the correct line, running along high street (within the black line)
  • the errant line, not contained by the black line example lines
2
  • 1
    @BERA added a screenshot
    – Avocado
    Feb 2, 2023 at 17:28
  • create a small buffer around your original lines and delete each red lines that are not fully contains by those buffers.
    – obchardon
    Feb 6, 2023 at 14:23

1 Answer 1

0

If you want to also remove the geometries that aren't contained in any of the lines you could try this:

with d as (
    select st_difference((select st_collect(geom) from new_geom), (select st_collect(geom) from original_geom_backup)) geom
)
select n.ogc_fid, ST_Difference(n.geom, d.geom) geom FROM new_geom n, d;

or this, which is a bit more efficient:

with d as (
    select ogc_fid, st_difference(geom, (select st_collect(geom) from original_geom_backup)) geom
    from new_geom
)
select n.ogc_fid, ST_Difference(n.geom, d.geom) geom FROM new_geom n join d on n.ogc_fid = d.ogc_fid;
2
  • Hey @jbalk, my apologies, when I said "How can I rewrite this query to actually remove those differences?", I was being imprecise. I understand how to actually store them in pg, it's just that the query was not removing differences from them. I looked a bit further into the data, and 3 of the "differences" are not actually contained within the new geom, so they don't get removed by st_difference. The other ones work.
    – Avocado
    Feb 2, 2023 at 22:41
  • You need to remove that st_within filter to catch those lines that are completely outside the old lines
    – jbalk
    Feb 3, 2023 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.