Postgis merge and order of linestrings

Question

I've imported a KML file in my database postgis. When I select a road I've the right result: This is one road. There aren't a lot of rows so I could order them manually but some roads have more then 100.

So I would like to order the linestrings at the import.

I have this :

1 - I would like to merge the linestrings. It's possible with ST_Union to do this but if I do it now, the result is very strange that's why I have to order the lines.

2 - So I have to order the linestrings, that's why I have a column position in my table. I know how to get the end and the first point of a linestring.

When I do this :

SELECT ST_AsText(ST_ClosestPoint(ST_GeomFromText('POINT(7.38770714271048 47.5497446465569)',4326),geometrie)), ST_AsText(ST_ClosestPoint(geometrie,ST_GeomFromText('POINT(7.38770714271048 47.5497446465569)',4326))) FROM sections WHERE nom_voie = 'LA THERMALE';

7.38770714271048 47.5497446465569 is the endpoint

It returns all rows of the road LA THERMALE.

Is there another solution to merge the linestrings maybe without ordering ?

When I concatenate the linestrings the result is false: it relate the endpoint of the Line 1 to the start point of the Line 4 etc. I think it's because they aren't ordered.

Answer 1

Try using using the ST_Collect to aggregate the line pieces into a MULTILINESTRING (hopefully), then use ST_LineMerge to sew them together.

SELECT nom_voie, ST_LineMerge(ST_Collect(geometrie))
FROM sections
WHERE nom_voie = 'LA THERMALE'
GROUP BY nom_voie;

---

For example, with a MULTILINESTRING, same as your figure:

SELECT ST_AsText(ST_LineMerge('
MULTILINESTRING ((27 215, 140 170),
  (230 210, 330 170),
  (230 210, 140 170),
  (330 170, 380 230))'));

                     st_astext
----------------------------------------------------
 LINESTRING(27 215,140 170,230 210,330 170,380 230)
(1 row)

So from this, it doesn't appear ordering or even direction matters.