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:
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