Re: Update with last known location?
От | James David Smith |
---|---|
Тема | Re: Update with last known location? |
Дата | |
Msg-id | CAMu32ADC7FZi9MrOip0Y8tNsM-qLC8t_Fu+StriwRoifiSLPFQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Update with last known location? (James David Smith <james.david.smith@gmail.com>) |
Список | pgsql-novice |
On 28 January 2014 10:42, James David Smith <james.david.smith@gmail.com> wrote: > On 27 January 2014 19:26, Daniel Staal <DStaal@usa.net> wrote: >> --As of January 27, 2014 1:02:05 PM +0000, James David Smith is alleged to >> have said: >> >>> ppid (person ID) >>> point_time (timestamp) >>> the_geom (geometry point) >>> >>> My problem is that some (alot) of the info from the location >>> (the_geom) column is missing. This column needs updating with the last >>> known location of the person. The attached screenshot explains a bit >>> better than I am managing too in writing. In the attached image, >>> the_geom column from 14:41 to 14:51 would be updated with the data >>> from the 14:40. >>> >>> I'm struggling conceptually as to how to do this. Some sort of >>> self-join on the table I think. But how to get the right data for the >>> update? >>> >>> Does anyone have any clever ideas? >> >> >> --As for the rest, it is mine. >> >> Is this a one-time thing, or something ongoing? If this is something you >> need regularly, I'd write in a trigger or something to fill in the location >> at record creation. (And I'd probably write a program to go through and >> fill in the locations on historic data, if needed.) >> >> Anyway, the select for the data is probably something along the lines of: >> >> SELECT the_geom FROM table WHERE ppid = current_ppid AND point_time < >> current_time ORDER BY point_time DESC NULLS LAST LIMIT 1; >> >> Daniel T. Staal > > Hi Daniel, > > This is a one-time thing. > > I'm afraid the select you wrote above doesn't do what I need it to do. > Maybe I didn't explain my issue well enough. > > I was playing around a bit yesterday and thought maybe I need to do > some sort of loop. In pseudo-code it would work something like this: > > 1) Order the table by ppid and then point_time > 2) Iterate through the table. > 3) When you come to a row that has a blank 'the_geom' column, take > 'the_geom' from the row above and copy it to this row, but only if > they have the same ppid. > 4) Move to the next row i.e. keep iterating through the table. > 5) Repeat 3 as necessary. > > What do you think? I've not done much with LOOPS in postgreSQL. I'm > going to do some reading today and see if I can figure it out! > > Thanks > > James Hi, Bad form to reply to oneself I guess, but hey ho. I've been working on trying to do this in a LOOP, but haven't managed to get it to work. But I thought that posting my progress so far might help people understand what I'm trying to do. DROP TABLE test; -- Create a table which is a join on itself. The join is offset by one minute . CREATE TABLE test AS (SELECT a.ppid as a_ppid, a.point_time as a_point_time, a.the_geom as a_the_geom, b.ppid as b_ppid, b.point_time as b_point_time, b.the_geom as b_the_geom FROM hybrid_location a LEFT JOIN hybrid_location b ON a.ppid = b.ppid AND a.point_time = b.point_time + INTERVAL '1 MINUTE' ORDER BY a.ppid, a_point_time); ---Now create a function which is going to go through this table row by row, and copy the data from b_the_geom to a_the_geom IF a_the_geom is null. DROP FUNCTION update_locations(); CREATE FUNCTION update_locations() RETURNS integer AS $$ DECLARE mviews RECORD; BEGIN FOR mviews IN SELECT a_the_geom, b_the_geom FROM test ORDER BY a_ppid, a_point_time LOOP -- Now "mviews" has one record from the above query. EXECUTE 'UPDATE test SET a_the_geom = ' || b_the_geom || ' WHERE a_the_geom IS NULL AND WHERE a_ppid = ' || quote_literal(b_ppid); END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; SELECT update_locations(); So this doesn't work, but maybe it shows what I'm trying to do? Thanks James
В списке pgsql-novice по дате отправления: