I have two PostGIS tables:

  • roads have an id, a name, and a geom (line).
  • nodes have an id and a geom (point). They represent road tips (intersections or dead-ends).

I would like to store for each road a begin node and an end node. I know their geometries match correctly:

SELECT ST_Intersects(
    (SELECT geom FROM nodes WHERE id = 995),
    (SELECT geom FROM roads WHERE id = 421)
-- => true

However, I have way too many roads, and not enough nodes. Here's why: some road sections are multiple lines for no reason

Red lines are road sections, yellow squares at their tips.

Red dots are nodes. Should be the only tips.

Thus I want to merge redundant roads (have the same name and intersect at one tip and don't intersect with a node at this tip). These road sections also ST_Intersects() correctly. How can I perform this merge with PostGIS (SQL)?

I decided to proceed in two steps:

  1. Merge all intersecting roads with the same name
  2. Split merged roads with the points

I successfully merged the roads using this answer. First I created a new table, mroads (merged roads).

CREATE TABLE mroads ( -- Merged roads
    gid SERIAL,
    name varchar(10),
    CONSTRAINT pk_troncons PRIMARY KEY (gid)
    ADD COLUMN geom_tmp geometry(GeometryCollection),
    ADD COLUMN geom geometry(MultiLineString, 2154);
CREATE INDEX idx_mroads ON mroads USING GIST(geom);

I used a temporary geom_tmp column, since the ST_ClusterIntersecting method returns collections, and I don't want collections, so I'll have to process them later. Then I merged the roads:

INSERT INTO mroads (name, geom_tmp)
SELECT name, unnest(ST_ClusterIntersecting(geom))
FROM roads GROUP BY name;

I ran into problems about how collections didn't have a SRID, and some other things. My tinkering resulted in this:

-- SRID for my data is 2154 (Lambert-93 for metropolitan France)
SELECT UpdateGeometrySRID('public', 'mroads', 'geom_tmp', 2154)
-- ST_ClusterIntersecting did not actually merge geometries,
-- it regrouped them in a collection. We ST_LineMerge this collection.
UPDATE mroads SET geom = ST_Multi(ST_LineMerge(ST_CollectionExtract(geom_tmp, 2)));
ALTER TABLE mroads DROP COLUMN geom_tmp;

(Trivia: I had 756k rows, now there are only 91k of them.) Now, I want to split mroads wherever they intersect with nodes, unless it's at the very beginning or end of the line. I expect to produce more tuples.

Some lines may be split multiple times, but never at the tips

  • 1
    Welcome to GIS SE. I really like this question and the way you present it, but I think it is too broad for GIS SE. This platform is not meant for doing your coding work. Please edit your question and add the information what you tried so far and describe exactly where you are stuck.
    – pLumo
    Dec 18 '17 at 14:45
  • @RoVo Thank you. :-) I'm trying some solution right now and will update right after succeeding (or failing).
    – GnxR
    Dec 18 '17 at 14:49

Browse other questions tagged or ask your own question.