Update with last known location?

I have a large table of data which has about 75,000 people's locations per minute of the day for a 24 hour period. The columns are:

ppid (person ID) point_time (timestamp) the_geom (geometry point)

My problem is that some (alot) of the info from the location (the_geom) column is missing. This column needs updating with the last known location of the person. I'm struggling conceptually as to how to do this. Some sort of self-join on the table I think. But how to get the right data for the update?

I've made a SQL fiddle which demonstrates the problem:

http://sqlfiddle.com/#!15/77157/1

Thanks

James

Answers


I'm not sure how this will perform over a larger data set, but here's a single query solution, using two nested sub-queries:

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 test_data 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 test_data 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 test_data data

Need Your Help

from matplotlib.backends import _tkagg ImportError: cannot import name _tkagg

python matplotlib pip virtualenv tk

While trying to run this example to test how matplotlib works with Tkinter, I am getting the error:

Using Toggle Button to Update Value

jquery css asp.net-mvc toggle

I have this check box which helps me to send a value to the controller: