[postgis-users] Create LINESTRING with a LOOP

[Mark Cave-Ayland]

On Sat, 2007-04-21 at 11:23 +0000, paallen at attglobal.net wrote:
> Hi all,
> 
> I am writing a function that returns a linestring
> (4D).  I am creating this linestring within a
> loop.  But it doesn't work because the first two
> times through the linestring is null or empty.
> 
> What is the most efficent way to create a
> linestring within a LOOP?
> 
> Below is a simplified example of what I am trying
> to do.
> 
> thanks,
> 
> Phillip Allen


Hi Phillip,

Since text parsing in general is considered expensive, I'd aim to build
all the points up into an EWKT string representation first and then
parse the complete string. I have something like this working:


CREATE OR REPLACE FUNCTION mc_test2( )
        RETURNS geometry AS
$BODY$
        DECLARE
                x integer;
                str text;
        BEGIN
            x := 1;

            -- Start of LINESTRING EWKT definition
            str := 'SRID=32717;LINESTRING(';

            -- Add each point to the EWKT string of the linestring
            WHILE (x <= 5) LOOP
                str := str || 400000 + x || ' ' || 900000 + x || ' ' ||
3500 + x || ' ' || x;

                -- Add commas after each entry except the last
                IF x < 5 THEN
                    str := str || ',';
                END IF;

                x := x + 1;
            END LOOP;

            -- End of LINESTRING EWKT definition
            str := str || ')';

            -- For debugging...
            --RAISE NOTICE 'str: %', str;

            -- Return resulting geometry
            RETURN GeomFromEWKT(str);
        END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;


This gives me the following output:

postgis=# select asewkt(mc_test2());
                                                             asewkt 
---------------------------------------------------------------------------------------------------------------------------------
 SRID=32717;LINESTRING(400001 900001 3501 1,400002 900002 3502 2,400003
900003 3503 3,400004 900004 3504 4,400005 900005 3505 5)
(1 row)


HTH,

Mark.