Re: LOOP Functions - where to start?
| От | James David Smith |
|---|---|
| Тема | Re: LOOP Functions - where to start? |
| Дата | |
| Msg-id | CAMu32ABYV30d2ZxuOYGxfcGPCyCPyDOvj0yxBf6NB9mkx-htpQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: LOOP Functions - where to start? (Merlin Moncure <mmoncure@gmail.com>) |
| Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: