Merging lines of which ends are not connected but close in PostGIS

Question

I have a sample network as shown below.

CREATE TABLE sample (id integer, geom geometry(MultiLineString, 4326));
INSERT INTO sample (id, geom)
VALUES  (1, ST_GeomFromText('MULTILINESTRING((136.6047267616309 36.58881962722421,136.60472407895327 36.588929462324984))', 4326)),
        (2, ST_GeomFromText('MULTILINESTRING((136.60423725884255 36.58906837160822,136.60424396418773 36.58891438698953))', 4326)),
        (3, ST_GeomFromText('MULTILINESTRING((136.60424396418773 36.58891438698953,136.60424798685528 36.58878839916548),(136.60424798685528 36.58878839916548,136.6040039063554 36.588896081289306))', 4326)),
        (4, ST_GeomFromText('MULTILINESTRING((136.60463154321133 36.588421203276084,136.60473346697609 36.5885762652826),(136.60473346697609 36.5885762652826,136.6047267616309 36.58881962722421))', 4326)),
        (5, ST_GeomFromText('MULTILINESTRING((136.60463154321133 36.588421203276084,136.60528197878045 36.5881638415882))', 4326)),
        (6, ST_GeomFromText('MULTILINESTRING((136.60442098943724 36.58891977123062,136.60424396418773 36.58891438698953))', 4326)),
        (7, ST_GeomFromText('MULTILINESTRING((136.6047053038069 36.589643388231934,136.60439684983498 36.58964231174343))', 4326)),
        (8, ST_GeomFromText('MULTILINESTRING((136.6047267616309 36.58881962722421,136.60507142500649 36.58882608795375))', 4326)),
        (9, ST_GeomFromText('MULTILINESTRING((136.60455241815976 36.588284446070475,136.60463154321133 36.588421203276084))', 4326)),
        (10,ST_GeomFromText('MULTILINESTRING((136.6042198236861 36.58963692750234,136.60423859973173 36.58906837160822))', 4326)),
        (11,ST_GeomFromText('MULTILINESTRING((136.6037987170373 36.58982644523445,136.6038201748613 36.58906837160822),(136.6038201748613 36.58906837160822,136.60382419842824 36.58897468833135))', 4326)),
        (12,ST_GeomFromText('MULTILINESTRING((136.6047053038069 36.589643388231934,136.60472005628571 36.58906837160822))', 4326)),
        (13,ST_GeomFromText('MULTILINESTRING((136.60472407895327 36.588929462324984,136.60442098943724 36.58891977123062))', 4326)),
        (14,ST_GeomFromText('MULTILINESTRING((136.6040039063554 36.58889715777775,136.60382419842824 36.58897468833135))', 4326)),
        (15,ST_GeomFromText('MULTILINESTRING((136.60439684983498 36.58964231174343,136.60441562498124 36.58906837160822),(136.60441562498124 36.58906837160822,136.60442098943724 36.58891977123062))', 4326)),
        (16,ST_GeomFromText('MULTILINESTRING((136.6042198236861 36.58963692750234,136.60421580101854 36.58983505984031),(136.60421580101854 36.58983505984031,136.6040039063554 36.58983075208772),(136.6040039063554 36.58983075208772,136.6037987170373 36.58982644523445))', 4326)),
        (17,ST_GeomFromText('MULTILINESTRING((136.60439684983498 36.58964231174343,136.6042198236861 36.58963692750234))', 4326)),
        (18,ST_GeomFromText('MULTILINESTRING((136.60471871539653 36.58906837160822,136.60472407895327 36.588929462324984))', 4326))
;

The network is illustrated below. Each line segments are colored differently. This network contains some dangles (red points). There are three red rectangles where the link edges are not topologically connected (so that it is detected as dangles).

Can you provide queries or functions (or related posts) which can connect and merge these links into one in PostGIS?

As far as I checked, the distance between nodes are less than 20 cm so it can be used as a threshold in the process.

I believe there should be appropriate functions in PostGIS, but what I could found were the posts related to finding islands so far.

Maybe these answers could help https://gis.stackexchange.com/questions/53797/how-can-i-snap-lines-in-postgis. — user30184, Jun 09 '22 at 06:49

Cushen · Accepted Answer · 2022-06-10T02:30:18.963

The following will do what is required.
The steps are as follows:

Find nodes within 1 metre of other nodes that aren't touching (EPSG unit)
Cluster these nodes and get the center-point (a point to snap the lines to)
Snap the lines with 2 meters of a center-point to the point.
Delete lines which were moved from the output

DROP TABLE IF EXISTS public.ge_snappedlines;
CREATE TABLE public.ge_snappedlines AS
SELECT DISTINCT
    l.id ,
    ST_TRANSFORM(ST_Snap(ST_TRANSFORM(l.geom,3857), ST_TRANSFORM(p.geom,3857), 2),4326) geom
FROM
    public.ge_linestosnap l,
    (
    SELECT 
    row_number() over (ORDER BY unnest(ST_ClusterWithin(ST_TRANSFORM(geom,3857), 1))) AS id, 
    ST_Centroid(unnest(ST_ClusterWithin(ST_TRANSFORM(geom,3857), 1))) geom
    FROM (
        SELECT DISTINCT p.*
        FROM public.ge_linestosnap l, 
        (SELECT id, (ST_DumpPoints(geom)).geom FROM public.ge_linestosnap) as p
        WHERE ST_DWithin(ST_TRANSFORM(l.geom,3857), ST_TRANSFORM(p.geom,3857), 1) AND NOT ST_TOUCHES(l.geom, p.geom) AND l.id != p.id
        )AS nodes) AS p

Now to delete the the geoms that were moved:

DELETE FROM public.ge_snappedlines sl
USING 
(
SELECT DISTINCT l.* 
FROM ge_snappedlines l, 
    sample s,
    (SELECT id FROM(
    SELECT id, ROW_NUMBER() OVER(PARTITION BY id ORDER BY geom asc) AS Row, geom 
    FROM ONLY ge_snappedlines l
    ) dups WHERE row > 1) ids
WHERE l.id = ids.id AND ST_EQUALS(ST_Snap(ST_TRANSFORM(s.geom,3857),ST_TRANSFORM(l.geom,3857),0.001), ST_TRANSFORM(l.geom,3857))
) as dels
WHERE sl.geom = dels.geom

It helps me but I found two remaining isues in your solution: 1) original lines before snapping still remain after running the query, and 2) snapped lines are not merged into one segment. Is it possible to solve them? As for 2, I do not care about the line ID (and other attributes) after mering. — HSJ, Jun 09 '22 at 10:07
I have one questions: can I set the same threshold valuess in ST_Snap, ST_ClusterWithin and ST_DWithin?
If not, could you let me know what rule I should adapt, i.e., if finding nodes within 1 meter, snap should be 2 meters (two times larger than finding nodes)? — HSJ, Jun 09 '22 at 10:17
I have a fix for part 1, removing duplicates. Part 2, merging as one line was not specified in the original question but an ST_COLLECT around the output would merge all to one feature.
Setting the sample threshold for all ST_Snap, ST_ClusterWithin and ST_DWithin should work ok in theory. — Cushen, Jun 10 '22 at 01:50

Merging lines of which ends are not connected but close in PostGIS

1 Answers1

Linked