The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

antonpitts (1) [Avatar] Offline
I couldn't find much discussion of the time dimension in PostGIS in action, other than a mention the 'T' in the 'ST_' prefix stood for time but it wasn't used much...

For context, I'm working on a field data collection system where, in theory, users start a patrol, the device collects waypoints and users enter sightings, then users finish the patrol. In theory, we can use the start and finish patrol entries to select the waypoints belonging to a patrol. In practice, it's not rare that users forget to finish the patrol, so the device sits around collecting waypoints for 27 hours until it runs out of battery...

So, on importing the data, what I'd like to do is import the waypoints into a staging table along with their time stamps, and then ask questions (and ideally, get the answers in a visual/ mapping output format for examination...) like:
- When did the device leave/ arrive at the dock/parking lot/ village?
- When did the device lose/ regain GPS signal?
- When did the device start/ stop moving at an appreciable speed?

The first I could probably do by putting the timestamp into the M dimension, merging the waypoints into a linestring, clipping the linestring to polygons defined around the docks/parking lots of interest, and selecting the boundaries of the multilinestrings produced. But is there a better approach?

The second I'd need some way of examining the intervals between successive points and seeing when they exceed either a pre-determined gap length, or ideally, one generated by looking at the usual gap for that track (if the device is collecting points every 10 seconds, then a few minutes of gap would indicate a loss of signal; if it's collecting them every 5 minutes, it wouldn't...)

For the third, I'd need to calculate some kind of moving average speed over a number of adjacent points; I imagine there's enough error in individual points there'd always be a non-zero velocity, but if it's low enough we could consider them stopped...

Any thoughts on how to tackle especially the latter two questions?