Re: Update with last known location?

On 28 January 2014 10:42, James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
> On 27 January 2014 19:26, Daniel Staal <DStaal(at)usa(dot)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