Re: Update with last known location?

On 28 January 2014 16:57, Daniel Staal <DStaal(at)usa(dot)net> wrote:
> --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

Hi Daniel,

Given the data is so large I don't want to be taking the data out to a
CSV or whatever and then loading it back in. I'd like to do this
within the database using SQL. I thought I would be able to do this
using a LOOP to be honest.

Thanks for your thoughts anyway.

James