2

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.

kml import

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

I have this :

Linestrings

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.

Mirza Selimovic
  • 1,669
  • 15
  • 18

1 Answers1

10

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: A

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)

enter image description here

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

Mike T
  • 41,085
  • 18
  • 152
  • 203
  • Just how you said, the result is a multilinestring, but what I need is one linestring merged from multiple linestrings. When I concatenate the linestrings it trys to relate them but the result is false because they aren't ordered so it relate point A to point Z, point B to point C then point C to point U ... – Mirza Selimovic Jul 31 '13 at 09:16
  • @MirzaSelimovic what do you mean "the result is false"? I'm not sure if ordering matters, see updated answer. If you specifically find something that does not do this, update your question with these details (WKT/WKB). – Mike T Aug 01 '13 at 00:58