Answered by:
Movement Paths & SpatialTemporal Queries
Question

Hey, so I'm trying to figure out the best way of storing movement paths and then afterwards how they might be queried.
Let me try to explain a bit more. Say I have many cars moving around on a map and I want to determine if and when they're in a convoy. If I store just the paths then I can see that they travelled along the same road, but not if they were there at the same time. I can store the start and end times but that will not take into account the changes in speed of the two vehicles. I can't think of any obvious way to store and achieve this so I thought I'd put the question out there in case there's something obvious I'm missing before trying to implement a solution. So does anyone know anything I don't?
Thanks,
Andrew
Tuesday, May 17, 2011 11:33 PM
Answers

I don't think there's anything "obvious" you're missing... SQL Server doesn't implement a STLocateVehiclesInSimilarTimeAndSpace() method, sadly :)
My first thoughts: suppose that your table had rows of points, representing individual locations of each vehicle recorded from their GPS log at a given time:
VehicleID TimeStamp Location
a 20100901 20:00 (52,1)
b 20100901 20:01 (52,1)
a 20100901 20:05 (52,0.5)
b 20100901 20:03 (52.1, 1.1)
a 20100901 20:07 (52.5, 1.5)
b 20100901 20:09 (53, 2)
a 20100901 20:10 (51, 0)
To determine whether these two vehicles were in convoy, suppose that you grouped the readings together into 5 minute intervals, and calculated the average location of each vehicle in that period (say, by using EnvelopeCenter() or STCentroid() on the aggregate of points recorded for that vehicle in that time period). This would give the following:
VehicleID TimeToNearest5mins AverageLocation
a 20100901 20:00 (52,1)
b 20100901 20:00 (52,1)
a 20100901 20:05 (52.25, 1)
b 20100901 20:05 (52.1, 1.1)
a 20100901 20:10 (51,0)
b 20100901 20:10 (53,2)
Now, you could look through the results in this table and determine, during each 5 minute period, which vehicles lay were within a certain distance, x, of another vehicle (by testing whether a.STIntersects(b.STBuffer(x)) or a.STDistance(b) < x). Using a value of x = 1 for the example above, you could conclude that vehicles a and b were in convoy between 20:00 and 20:05, but not by 20:10.
By adjusting the interval that you use to group the time readings together, and the distance parameter, you change the acceptable tolerances in time and space that you will allow and still conclude that two vehicles were "in the same place at the same time".
twitter: @alastaira blog: http://alastaira.wordpress.com/ Marked as answer by Alex Feng (SQL) Wednesday, May 25, 2011 9:53 AM
Wednesday, May 18, 2011 8:56 AMAnswerer
All replies

I don't think there's anything "obvious" you're missing... SQL Server doesn't implement a STLocateVehiclesInSimilarTimeAndSpace() method, sadly :)
My first thoughts: suppose that your table had rows of points, representing individual locations of each vehicle recorded from their GPS log at a given time:
VehicleID TimeStamp Location
a 20100901 20:00 (52,1)
b 20100901 20:01 (52,1)
a 20100901 20:05 (52,0.5)
b 20100901 20:03 (52.1, 1.1)
a 20100901 20:07 (52.5, 1.5)
b 20100901 20:09 (53, 2)
a 20100901 20:10 (51, 0)
To determine whether these two vehicles were in convoy, suppose that you grouped the readings together into 5 minute intervals, and calculated the average location of each vehicle in that period (say, by using EnvelopeCenter() or STCentroid() on the aggregate of points recorded for that vehicle in that time period). This would give the following:
VehicleID TimeToNearest5mins AverageLocation
a 20100901 20:00 (52,1)
b 20100901 20:00 (52,1)
a 20100901 20:05 (52.25, 1)
b 20100901 20:05 (52.1, 1.1)
a 20100901 20:10 (51,0)
b 20100901 20:10 (53,2)
Now, you could look through the results in this table and determine, during each 5 minute period, which vehicles lay were within a certain distance, x, of another vehicle (by testing whether a.STIntersects(b.STBuffer(x)) or a.STDistance(b) < x). Using a value of x = 1 for the example above, you could conclude that vehicles a and b were in convoy between 20:00 and 20:05, but not by 20:10.
By adjusting the interval that you use to group the time readings together, and the distance parameter, you change the acceptable tolerances in time and space that you will allow and still conclude that two vehicles were "in the same place at the same time".
twitter: @alastaira blog: http://alastaira.wordpress.com/ Marked as answer by Alex Feng (SQL) Wednesday, May 25, 2011 9:53 AM
Wednesday, May 18, 2011 8:56 AMAnswerer 
Hi, thanks for the response. It's reassuring to know that I'm not missing something. Processing the individual points is what I'm currently doing. Unfortunately it's not performant enough when you scale up to large numbers of vehicles. I was hoping to find a way to make paths the unit of processing rather than individual points as this would result in a much smaller data set.
My initial thinking was along the lines of a second geometry which has time and distance offset as the axes. That way I can see where the movement paths intersect and using the distance along those paths at the intersection points see if they correspond on the time dimension. I don't think it will be a clean solution though. I was hoping someone had already figured something like this out to save me having to. :)
Thursday, May 19, 2011 3:51 PM