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


     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? – Alexandre Neto Jan 2 '14 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 – user1331131 Jan 2 '14 at 12:52

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.

| improve this answer | |
  • 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. – user1331131 Jan 2 '14 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. – simplexio Jan 2 '14 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.