Movement Paths & Spatial-Temporal Queries in SQL Server

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 change开发者_如何学Gos 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 I'm missing before trying to implement a solution. So does anyone know anything I don't?

Thanks, Andrew

---

Well it depends on what type of movement information you have. If you have some tables setup like:

Vehicle (Id, Type, Capacity, ...)

MovementPoint(VehicleId, Latitude, Longitude, DateTime, AverageSpeed)

This would allow you to query if two cars going to the same point plus or minus 5 minutes like so:

Select * from Vehicle v INNER JOIN MovementPoint mp on mp.VehicleId = v.Id
WHERE v.Id = @FirstCarID 
AND EXISTS 
    (
     SELECT 1 FROM Vehicle v2 INNER JOIN MovementPoint mp2 on mp2.VehicleId = v2.Id
     WHERE v2.Id = @SecondCarId 
     AND mp2.Latitude = mp.Latitude AND mp2.Longitude = mp.Longitude
     AND mp2.DateTime BETWEEN DATEADD(minute,-5,mp.DateTime) AND DATEADD(minute,5,mp.DateTime)
    )

You could also query for multiple points in common between multiple vehicles with specific time windows.

Also you could make the query check latitude and longitude values are within a certain radius of each other.