Combining linestrings

[Jim]

I have a set of LineStrings that represent all the segments of a highway.

I would like to combine them into one LineString representing the entire highway.

Is there a Spatialite function that will so this or does anyone have any thoughts on how to do this?

Regards,

Jim

[a.fu...@lqt.it]

hi Jim,

www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html

please RTFM searching for: ST_LineMerge()

bye Sandro

[Jim]

Sandro,

Thank you, I'm just getting back to using Spatialite after a long absence.

I see how ST_LINEMERGE works but I have a usage question.

I have a database with, for example, 20 rows that represent segments for a road.

Each has a geometry column ("geom") that is a LINESTRING.

Do I read all of those rows in, converting the geometry column to text, and appending each to a string that becomes a MULTISTRING, and then pass that to ST_LINEMERGE or is there a better way?

Also, if ST_LINEMERGE  fails to merge, does Spatilaite return a null or the original  MULTISTRING?

Best regards,

Jim

[i-s-o]

TL;DR:

• Combine your input linestrings into a single [multi-]geometry with the aggregate function ST_Collect.
• Merge them into a single linestring with ST_LineMerge.
• If some lines don't touch any other line, the resulting geometry will be MultiLineString.
  

I sometimes find the PostGIS docs helpful in testing out how these functions work (of course this only works if SpatiaLite's implementation is closely aligned with PostGIS):
https://postgis.net/docs/ST_LineMerge.html

I was curious about your question myself, so I tested it in SpatiaLite GUI with the example linestrings from that PostGIS page:
CREATE TABLE line_segments (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
SELECT AddGeometryColumn('line_segments', 'geometry', 4326, 'LINESTRING', 'XY');

INSERT INTO line_segments (id, geometry)
VALUES
  (NULL, GeomFromText('LineString(-29 -27,-30 -29.7,-36 -31,-45 -33)', 4326)),
  (NULL, GeomFromText('LineString(-45 -33,-46 -32)', 4326)),
  (NULL, GeomFromText('LineString(-29 -27,-30 -29.7)', 4326))
;

SELECT ST_LineMerge( ST_Collect(geometry) ) as geometry
FROM line_segments
;

-- Test the case where one linestring doesn't touch others
UPDATE line_segments
SET geometry = GeomFromText('LineString(-45.2 -33.2,-46 -32)', 4326)
WHERE id = 2
;

SELECT ST_LineMerge( ST_Collect(geometry) ) as geometry
FROM line_segments
;

[Jim]

i-s-o,

Thank you so very much.  Your example is just what I was looking for and also offers insight into another issue i have been working on.

Regards,

Jim