[postgis-users] creating linestrings from points, complexishlinestrings

[Jason Birch]

I don't think that it will help in this particular case (the first line
is a multilinestring) but the MySQL GROUP_CONCAT function can be a handy
way of avoiding procedural language with similar problems.

For instance, with certain data structures you could pull the geometry
out into x/y values, build a WKT linestring with GROUP_CONCAT, and then
flip it back into a geometry.

I'm not familiar enough with PostGIS to know; does MakeLine(geometry
set) do the same thing?

The MySQLCompat library has an example of adding GROUP_CONCAT to
PostgreSQL:

http://pgfoundry.org/projects/mysqlcompat/

-- GROUP_CONCAT()
-- Note: only supports the comma separator
-- Note: For DISTINCT and ORDER BY a subquery is required
CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
  SELECT CASE
    WHEN $2 IS NULL THEN $1
    WHEN $1 IS NULL THEN $2
    ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
  END
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat (
	BASETYPE = text,
	SFUNC = _group_concat,
	STYPE = text
);

Jason

Martin Davis wrote:

> Hmm, that is complexish.  I see now that pointnumber can be
> used as the ordering, so that's good.  But it looks like
> you might have to do an arbitrary-depth self-join to chain
> all the points in a particular list together - ugly.  'Fraid
> I don't have any good ideas about how to aggregate the
> sequences of points into linestrings.  Perhaps someone else
> on the list will.

Rhys Stewart wrote:
> here is an excerpt from the table:
>
>
> parish | pointnumber | parent |  devicetype
> --------+-------------+--------+--------------
> KSAS   |           3 |        | START
> KSAS   |           4 | 3      | INTER
> KSAS   |           5 | 4      | INTER
> KSAS   |           6 | 5      | END
> KSAS   |           7 | 3      | INTER
> KSAS   |           8 | 7      | END
> KSAS   |           9 | 7      | END
> KSAS   |          10 |        | START
> KSAS   |          11 | 10     | INTER
> KSAS   |          12 | 11     | INTER
> KSAS   |          13 | 12     | END
> KSAS   |          14 |        | START
> KSAS   |          15 | 14     | END