Thread: LOOP Functions - where to start?
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'
WHILE i < '1'
LOOP
SELECT ST_Line_Interpolate_Point(line, j)
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.
James
On Mon, Jul 30, 2012 at 11:11 AM, James David Smith <james.david.smith@gmail.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
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
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@gmail.com> wrote:
how about:On Mon, Jul 30, 2012 at 11:11 AM, James David Smith
<james.david.smith@gmail.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.
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