Re: Update with last known location?

On 30 January 2014 16:45, Kevin Grittner <kgrittn@ymail.com> wrote:
> 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

Hi Kevin et al,

Thanks for the code. I've just ran it. It completed in 0.730 ms. Speedy.

I can't run it on my actual data at the moment, or rather I don't want
too, as I set the below query going an hour or so ago and thought I
should let it finish really.

If it hasn't finished when I come into work tomorrow (I'm leaving the
office shortly) then I'll cancel it and give yours a crack instead. To
my novice eye, your code looks like it'll be quicker than the below
anyway.

Cheers

James

SELECT
  data.ppid,
  data.point_time,
  CASE
    WHEN data.the_geom IS NULL
    THEN (
      --Get all locations with an earlier time stamp for that ppid
      SELECT geom.the_geom
      FROM hybrid_location geom
      WHERE data.ppid = geom.ppid
      AND geom.point_time < data.point_time
      AND geom.the_geom IS NOT NULL
      AND NOT EXISTS (
        -- Cull all but the most recent one
        SELECT *
        FROM hybrid_location cull
        WHERE cull.ppid = geom.ppid
        AND geom.the_geom IS NOT NULL
        AND cull.point_time < data.point_time
        AND cull.point_time > geom.point_time
        AND cull.the_geom IS NOT NULL
        )
    )
  ELSE data.the_geom
  end
FROM hybrid_location data;