Re: Update with last known location?

On 30 January 2014 16:45, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> James David Smith <james(dot)david(dot)smith(at)gmail(dot)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;