Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

Thanks.

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

1 Answer 1

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
$$
DECLARE
    accu_line geometry;
    temp_line geometry;
BEGIN

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));
END LOOP;

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

LANGUAGE 'plpgsql' VOLATILE STRICT;
share|improve this answer

Your Answer

 
discard

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.