Re: Update with last known location?

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