# Movement Paths & Spatial-Temporal 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

• 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               2010-09-01 20:00   (52,1)

b               2010-09-01 20:01   (52,1)

a               2010-09-01 20:05   (52,0.5)

b               2010-09-01  20:03  (52.1, 1.1)

a               2010-09-01  20:07  (52.5, 1.5)

b               2010-09-01  20:09  (53, 2)

a               2010-09-01  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               2010-09-01 20:00  (52,1)

b               2010-09-01 20:00  (52,1)

a               2010-09-01 20:05  (52.25, 1)

b               2010-09-01 20:05  (52.1, 1.1)

a               2010-09-01 20:10  (51,0)

b               2010-09-01 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".

Wednesday, May 18, 2011 8:56 AM

### 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               2010-09-01 20:00   (52,1)

b               2010-09-01 20:01   (52,1)

a               2010-09-01 20:05   (52,0.5)

b               2010-09-01  20:03  (52.1, 1.1)

a               2010-09-01  20:07  (52.5, 1.5)

b               2010-09-01  20:09  (53, 2)

a               2010-09-01  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               2010-09-01 20:00  (52,1)

b               2010-09-01 20:00  (52,1)

a               2010-09-01 20:05  (52.25, 1)

b               2010-09-01 20:05  (52.1, 1.1)

a               2010-09-01 20:10  (51,0)

b               2010-09-01 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".