# Simplify table of linestrings

I have a PostGIS table of linestrings representing a road network. I want to simplify the geometries such that only lines that connect road junctions are stored.

At the moment the table has a large number of road segments that make up a single road. I want to join as many of these as possible into a single linestring. Such that:-

``````x----x----x----x becomes x------------x
``````

and:-

``````     x                        x
|                        |
x----x----x----x becomes x----x--------x
``````

It is important that I retain the points in each linestring so I am not looking to simplify to a topology.

• Please explain "It is important that I retain the points in each linestring". Only the points that connect to other lines, right? Commented Jan 2, 2014 at 12:13
• No, I need all of the points such that the linestring will properly render upon the map. In the diagram above the '-' represents a point, the 'x' represents where linestrings intersect Commented Jan 2, 2014 at 12:52

## 1 Answer

assuming that your data is correct, try this:

``````SELECT ST_GeomFromText('LINESTRING(' || a.start || ' , ' || a.end || ')') FROM (
SELECT ST_StartPoint(geom) as start, ST_EndPoint(geom) as end FROM roads) as a
``````

(not tested, probably has syntax errors).

Add WHERE clause if needed.

• Unless I am mistaken this will not work at all. It will remove all of the points in my linestrings and hence not meet this criteria "It is important that I retain the points in each linestring". Also it will not meet the criteria of joining linestrings either. Commented Jan 2, 2014 at 11:28
• So you want ST_Linemerge(geom) FROM... WHERE ST_GeometryType(ST_Linemerge(geom)) LIKE 'ST_Linesting' and then you need ST_Split all results geom, or you try add only touching geoms in ST_Linemerge query. Commented Jan 2, 2014 at 12:56