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

[Fay Du]

Michael:
Thank you very much. It gives me great help and I learned lots from your
email. I am a newbie of PostGIS and PostgreSQL. It is hard for me to
pick up right words to show how much I appreciate your help. 

I have a further question. In my application, I don't know column name
in front. For example, it could be "label", it could be "tag", there
could be 3 columns, and there could be 10 columns. Before I split lines,
can I get the number of columns and each column name programmatically?

Thanks again
Fay


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Michael Fuhr
Sent: Tuesday, March 07, 2006 7:37 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] How can I split a polyline?

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users