Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I use PostGis and pgRouting. I have a table with geometries of the adjacent edges returned by a shortest path query. I want to concatenate these edges and retrieve a list of all nodes these edges consist of. How do I do this in SQL?

I think the solution would be a mix of LineMerge and GeomUnion, but is there a smart way to do it?


share|improve this question
ST_LineMerge won't do the job. ST_LineMerge — Returns a (set of) LineString(s) formed by sewing together a MULTILINESTRING. PGRouting send you back a list of linestring. Maybe GemUnion is the key. I'll check that if I can. – Fabien Ancelin Jul 29 '11 at 14:01

Ok, so I did it myself with that sql function:

CREATE OR REPLACE FUNCTION adjacent_linestring_array_to_set_of_points(array_geom geometry[])
   RETURNS SETOF geometry AS
    accu_line geometry;
    temp_line geometry;

FOR temp_line in select * from unnest(array_geom) LOOP
    IF accu_line is NULL THEN
        accu_line := temp_line;
    END IF;
    accu_line := linemerge(GeomUnion(accu_line, temp_line));

RETURN QUERY SELECT ST_PointN(accu_line, generate_series(1, ST_NPoints(accu_line)));

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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