Hello friends -
I'm looking at data returned by our GPS provider that tells me where a vehicle reported its location. The GPS units report about every 15 seconds or so. This data is not spatially aware at this stage, but I do have lat/long and a timestamp. That is, for example, I might have a lat/long at 7:30:00 / 7:30:15 / 7:30:30 - .... 4:00:00 - if a particular worker were to start driving at 7:30:00 and stopped working at 4:00:00.
We've worked out a component to take this information, make it spatially aware, and pop it into GIS as a point, and map it out overlayed on top of our work order data and road data; essentially this is where the user drove over the day in relationship to the work they reported doing. Great. Now, to be really tricky, what we would like to do is create points where the vehicle reported it was stopped for certain timeframes; i.e., make a point with a different flavored icon if a vehcile stops for 10 minutes, 20 minutes, or more than thirty minutes. At this point, you can see where they drove, where they worked, and where they stopped and for how long; which is often times quite a bit different from where they were supposedly working.
Programmatically, it is difficult to engage in the environment that might allow for us to create points on the fly to allow for this, which is unfortunate because I can see how to do this iteratively with relative ease.
It would be much easier for a variety of reasons to create a view of our route data that allows us to grab a lat / long / starting + ending timestamp that would represent time stopped. In other words, if I went to lunch from 11:30 - 12:55, I would need some sql that would evaluate the route data such that it understood, this vehicle stopped reporting a change in lat/long at 11:30 and started reporting a change in lat / long at 12:55, and the lat / long values where the vehicle stopped were 32.991923 / -84.91293912 [or whatever].
My source rows might look like this:
Key Truck # lat long timestamp
101 abcd 32.1111 -84.9999 06/22/2009 11:29:45
..... [other truck data ]
199 abcd 32.1111 -84.9999 06/22/2009 12:55:00
Ideally I could write a query that would return
Truck # Lat Long Start Time End Time
abcd 32.1111 -84.9999 06/22/2009 11:29.45 06/22/2009 12:55:00
To make things more complicated, the fact that we might sometimes get slightly different lat / longs as a result of our timeframe, I may not be able to count on lat / longs being exactly the same between stop time report stops. In other words, I may have data that looks like this:
Key Truck # lat long timestamp
101 abcd 32.1112 -84.9997 06/22/2009 11:29:45
.....
199 abcd 32.1113 -84.9998 06/22/2009 12:55:00
This could happen if the device reported a location, the user drove a bit more, then cut the engine before the next reporting cycle. This would tend to give me problems in terms of grouping on lat/longs up and above the problems I was already having.
Finally, I need to batch this up, and group by truck; i.e., I can't say, just show me the values for truck abcd on date 6/22/2009 at run time, but rather, show me all of the stop times you've got past timestamp x. And it would be really nice if could calculate the number of minutes the vehicle was stopped.
I am willing to award 500 points and 50000000 cool points to someone who can help.
Thanks
brian
