Re: Update with last known location?

--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

---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------