Re: Update with last known location?

--As of January 28, 2014 10:42:45 AM +0000, James David Smith is alleged to
have said:

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

--As for the rest, it is mine.

If it's strictly a one-time thing, I personally would write it in Perl, not
SQL. ;) (Or whatever your scripting language of choice is.)

Which would allow you to change step 3 to 'If the_geom is blank, take
stored recent value for ppid and fill, then insert back into database.
Else, overwrite the_geom for this ppid.' (A bit less convoluted than
yours, and avoids the problems with multiple nulls in a row, as well as
allowing you to only sort by point_time.)

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