[postgis-users] How can I split a polyline?

[Michael Fuhr]

On Tue, Mar 07, 2006 at 04:01:54PM -0500, Fay Du wrote:
> My next question is, after I split a line, how can I keep all attributes
> for new lines? The attributes are from clients. Column names and numbers
> are random. Can I get column names of a table?

Are you looking for something like the following?  The query would
specify whatever columns it wants and the functions would generate
as many output rows as necessary for each matching row:

CREATE FUNCTION split_geom(geometry) RETURNS SETOF geometry AS $$
  SELECT GeometryN($1, n) FROM generate_series(1, NumGeometries($1)) AS g(n);
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION split_points(geometry) RETURNS SETOF geometry AS $$
  SELECT PointN($1, n) FROM generate_series(1, NumPoints($1)) AS g(n);
$$ LANGUAGE sql IMMUTABLE STRICT;

SELECT id, label, AsText(geom) FROM foo;
 id | label |                    astext                    
----+-------+----------------------------------------------
  1 | abc   | MULTILINESTRING((1 2,3 4),(5 6,7 8))
  2 | def   | MULTILINESTRING((11 12,13 14),(15 16,17 18))
(2 rows)

SELECT id, label, AsText(split_geom(geom)) FROM foo;
 id | label |         astext          
----+-------+-------------------------
  1 | abc   | LINESTRING(1 2,3 4)
  1 | abc   | LINESTRING(5 6,7 8)
  2 | def   | LINESTRING(11 12,13 14)
  2 | def   | LINESTRING(15 16,17 18)
(4 rows)

SELECT id, label, AsText(split_points(split_geom(geom))) FROM foo;
 id | label |    astext    
----+-------+--------------
  1 | abc   | POINT(1 2)
  1 | abc   | POINT(3 4)
  1 | abc   | POINT(5 6)
  1 | abc   | POINT(7 8)
  2 | def   | POINT(11 12)
  2 | def   | POINT(13 14)
  2 | def   | POINT(15 16)
  2 | def   | POINT(17 18)
(8 rows)

Only SQL functions can be used as I've shown, as a set-returning
function in the select list; functions written in other languages
like PL/pgSQL will cause such queries to fail with the following
error:

  ERROR:  set-valued function called in context that cannot accept a set

The PostgreSQL documentation says in "Query Language (SQL) Functions":

  Currently, functions returning sets may also be called in the
  select list of a query.  For each row that the query generates
  by itself, the function returning set is invoked, and an output
  row is generated for each element of the function's result set.
  Note, however, that this capability is deprecated and may be
  removed in future releases.

If that worries you then you could do something like this, although
it's not as flexible as the above examples:

CREATE FUNCTION foo_split() RETURNS SETOF foo AS $$
DECLARE
  row     foo%ROWTYPE;
  newrow  foo%ROWTYPE;
  geom    geometry;
BEGIN
  FOR row IN SELECT * FROM foo LOOP
    newrow := row;
    FOR i IN 1 .. NumGeometries(row.geom) LOOP
      geom := GeometryN(row.geom, i);
      FOR j IN 1 .. NumPoints(geom) LOOP
        newrow.geom := PointN(geom, j);
        RETURN NEXT newrow;
      END LOOP;
    END LOOP;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT id, label, AsText(geom) FROM foo_split();
 id | label |    astext    
----+-------+--------------
  1 | abc   | POINT(1 2)
  1 | abc   | POINT(3 4)
  1 | abc   | POINT(5 6)
  1 | abc   | POINT(7 8)
  2 | def   | POINT(11 12)
  2 | def   | POINT(13 14)
  2 | def   | POINT(15 16)
  2 | def   | POINT(17 18)
(8 rows)

-- 
Michael Fuhr