[postgis-users] creating linestrings from points, complexishlinestrings

[Rhys Stewart]

plpgsql to the rescue...sort off

a recursive function did the trick...for the most part

CREATE OR REPLACE FUNCTION tmp.whodn_1(in whoid text, out tot text)
RETURNS setof text as
$BODY$
DECLARE
myrec record;
qot text;
thol text;
outro bigint;
BEGIN
--qot := 'SELECT count(pointnumber) FROM tmp.testpoints_ where parent
= '|| quote_literal(whoid);
--raise notice '-> %',qot;
--EXECUTE qot into outro ;
qot := 'SELECT pointnumber FROM tmp.testpoints_ where parent = '||
quote_literal(whoid);
FOR myrec in EXECUTE qot LOOP
	tot := whoid||','||myrec.pointnumber;
	thol := myrec.pointnumber;
	RETURN NEXT;
	--tot := null;
	FOR myrec in select * from tmp.whodn_1(thol) LOOP
		--tot := null;
		tot := split_part(tot,',',1)||','||myrec.tot;
		IF
		RETURN NEXT;
		--tot := null;
	END LOOP;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

thats just the shell, but it goes through and gives me the points in
the correct order. :-)




On 4/5/07, Jason Birch <Jason.Birch at nanaimo.ca> wrote:
>
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>