I have two PostGIS tables:
name, and a
geom(point). They represent road tips (intersections or dead-ends).
I would like to store for each
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
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:
- Merge all intersecting roads with the same name
- 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) ); ALTER TABLE mroads 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.