Some help if you can please. I have GPS data for a number of people. Each person turned the GPS on, at home, at a different time of the day. Before that time, I presume that the person was in their house. I have made a table for each person for a whole 24 hours (one record per minute), and I now want to 'fill in' their location for the rows before they turned the GPS on. So for each person I want to take the first row where the point_geom is not null, and update all of the rows above it with that value. It's driving me nuts.
spid | point_time | point_geom
-----------------------------------------------------------
1 | 2012-01-01 00:01:00 |
1 | 2012-01-01 00:02:00 |
1 | 2012-01-01 00:03:00 | POINT(X, Y)
1 | 2012-01-01 00:04:00 | POINT(X, Y)
1 | 2012-01-01 00:05:00 | POINT(X, Y)
2 | 2012-01-01 00:01:00 |
2 | 2012-01-01 00:02:00 |
2 | 2012-01-01 00:03:00 |
2 | 2012-01-01 00:04:00 |
2 | 2012-01-01 00:05:00 | POINT(X, Y)
3 | 2012-01-01 00:01:00 |
3 | 2012-01-01 00:02:00 | POINT(X, Y)
3 | 2012-01-01 00:03:00 | POINT(X, Y)
3 | 2012-01-01 00:04:00 | POINT(X, Y)
3 | 2012-01-01 00:05:00 | POINT(X, Y)
I've managed to select the correct row using this:
SELECT spid, min(point_time) as point_time
FROM hlhs_day
WHERE point_geom IS NOT NULL
GROUP BY spid;
However when I try to add in the column 'point_geom' to the query, it won't work.
Thanks for your help and suggestions.
James
