How to extract nodes with PostGIS to a multipoint geometry

Question

I have a line in a PostGIS table test_line. I want to extract it's nodes into a multipoint geometry.

I tried the following:

SELECT ST_Collect((ST_DumpPoints(the_geom)).geom) FROM test_line;

But it gives me the error ERROR: set-valued function called in context that cannot accept a set.

What do I have to change in order to get it to work?

Answer 1

I had a similar problem and this mailing list post helped me:

http://lists.osgeo.org/pipermail/postgis-users/2010-June/026887.html

The solution they came up with is as follows:

SELECT gid, ST_AsText(replace(ST_AsEWKT(geom), 'LINESTRING',
'MULTIPOINT')::geometry)
FROM
 (SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom
  UNION ALL
  SELECT 2 as gid, 'LINESTRING (1 1, 2 6, 7 7)'::geometry AS geom) AS g;

Answer 2

ST_Dump is returning multiple values for one row, which cannot be nested within the ST_Collect() function, so you must separate things out using a sub-query.

 SELECT ST_Collect(the_geom2) 
 FROM (
       SELECT (ST_DumpPoints(the_geom)).geom AS the_geom2 
       FROM test_line) foo;

Answer 3

Old post, but this worked for me:

WITH A AS (
  SELECT (ST_DumpPoints(the_geom)).geom
  FROM test_line
)
SELECT ST_Collect(geom) FROM A;