Re: LOOP Functions - where to start?

Hi Merlin,

Thank you so much for your help - I've managed to do it ! :-) I didn't
delve into LOOPS in the end, which worries me a little bit as I think that
I need to learn how to understand them, but in the meantime all is good. My
final query looks like the below.

Basically I have a load of GPS points. Some are every few seconds, some are
every minute etc. The time between each point is not regular. But for the
work I am doing I need a point for every second. So I first make a line
between all the points that exist, then I calculate the time between the
first point and the end point, and see how many seconds this is. Then I use
the number that has been generated from this inside the
st_line_interpolate_point function that you helped me with to split the
line I've just made into the correct number of points.

SELECT ST_Line_Interpolate_Point(
(SELECT St_MakeLine(the_geom_osgb36) as line
FROM (SELECT the_geom_osgb36
FROM gps_12_07_2012
WHERE person_id = '1'
ORDER BY date_time) a),
v::float /
(SELECT (EXTRACT(EPOCH FROM (MAX(date_time) -
MIN(date_time)))/1::integer)::integer FROM gps_12_07_2012 WHERE person_id =
'1')
) INTO new_table from
generate_series(1,
(SELECT (EXTRACT(EPOCH FROM (MAX(date_time) -
MIN(date_time)))/1::integer)::integer FROM gps_12_07_2012 WHERE person_id =
'1')
) v;

I think I will read the manual now so that I can understand how to store
this as a function because I will use this alot in the work I'm doing.

Best wishes

James

On 30 July 2012 17:29, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Mon, Jul 30, 2012 at 11:11 AM, James David Smith
> <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
> > Hi all,
> >
> > Thanks for the help a week or two ago with matching the time fields. I
> > managed to move on from that issue at last which was pleasing. I'm now
> > struggling with LOOP functions though. I've been having a look around the
> > net for some gentle introductions to them but haven't found any good ones
> > yet - could someone point me towards them if they exist? I don't like the
> > ones in the manual much.
> >
> > Once I've learnt how loops work, I want to try and do something like the
> > below (simplified). Wrote in pseudo-code for now as I'm not sure how of
> the
> > format to write it properly and get it working. Essentially I want to
> split
> > a line into a number of equally defined and evenly spaced points. I
> > discovered the st_line_interpolate_point function, but I need to put that
> > inside a loop and store each record that it generates into a new table.
> > _____________
> > j = 0.2
> > i = 0.2
> > WHILE i < '1'
> > LOOP
> > SELECT ST_Line_Interpolate_Point(line, j)
> > INTO new_record_in_new_table
> > j = i+j
> > END LOOP
> > ____________
> >
> > Thanks for the communitys help as always.
>
> how about:
>
> INSERT INTO new_table
> SELECT ST_Line_Interpolate_Point(null, v::float / 10) from
> generate_series(2, 10) v;
>
> we're using generate_series to break out values -- it generates
> integers so we're doing the to float math from the generated value --
> and wrapping the entire query into a insert/select. if you must use
> loops (maybe for fine grained error handling), you've almost got it.
> see here:
> http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html.
>
> specifically, i'd be using this construction (for lloops are for integers):
> LOOP
> -- some computations
> EXIT WHEN count > 100;
> END LOOP;
>
> plpgsql btw can sometimes be used with the DO construct:
>
> DO
> $$
> BEGIN
> LOOP
> -- some computations
> EXIT WHEN count > 100;
> END LOOP;
> END;
> $$ LANGUAGE PLPGSQL;
>
> merlin
>