Re: Update with last known location?

I think you could do this using a common table expression, where you could
order, aggregate, and enumerate your data with row_number() or another
ranking function, depending on how you want ties within your data handled.
You could then directly update the data within the table expression
matching on ID, and filtering on NULLs and row numbers being greater than
one, assuming data with a row number of 1 has the "max" value when ordered
by ID and date location was obtained, descending.

On Tue, Jan 28, 2014 at 12:07 PM, James David Smith <
james(dot)david(dot)smith(at)gmail(dot)com> wrote:

> 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
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>