Re: Update with last known location?

James David Smith <james.david.smith@gmail.com> wrote:

> I've made a self-contained example of my problem below.

It is always easier to provide advice on this sort of thing with a
self-contained test case.  Looking at that, I think I would
approach it this way, at least as a first attempt, to see if
performance is good enough:

update test_data x
  set the_geom = y.the_geom
  from test_data y
  where x.the_geom is null
    and y.ppid = x.ppid
    and y.the_geom is not null
    and y.point_time < x.point_time
    and not exists
        (
          select * from test_data z
            where z.ppid = y.ppid
              and z.the_geom is not null
              and z.point_time > y.point_time
              and z.point_time < x.point_time
        )
;

To my eye, that is simple and straightforward.  On my machine, it
runs in less than 1 ms with the provided test data; the question is
whether it scales OK.  If it does not, we will need a description
of your hardware, OS, and your configuration to figure out why not.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company