1 2 3 Previous Next 38 Replies Latest reply on May 5, 2014 9:35 AM by allan.walker.0

# How do I compare GPS stops by time and location?

I have created a worksheet (see attached packaged workbook called "Tableau_NearbyStops") that shows the time stamp and GPS coordinates for every pickup and drop-off over the course of a single day.

I would like to use this dataset to identify any group of two or more stops that occur within a certain number of minutes and miles.

The exact number of minutes and miles will be defined by a set of parameters (one for minutes and one for miles).

Here is where it gets tricky (at least, from my perspective).

I want to filter the view to only show those groups of trips that fall within the parameter values. Based on my experimentation, this condition would seem to eliminate the possibility of using the LOOKUP function to help identify nearby stops. I have also found it challenging to map results when using the LOOKUP function (which may simply be the result of a lack of knowledge on my part).

So, I am hoping another option is available to help me achieve the desired goal.

Any advice or suggestions would be greatly appreciated.

Thanks,

Bobby

• ###### 1. Re: How do I compare GPS stops by time and location?

I'm not sure I understood the lookups and nearby stops, but noticing origin and destination info in separate fields I did a quick custom sql reshape on your data to make it play a bit more nicely with mapping. This may help with the next part as well.

Though I'm not sure I totally understand the filtering issue, one trick I use when filters cause problems is to obscure rather than filtering. By that i mean, create a calculation that depends on date where the values only exist in the desired range. i.e. something like:

Filtered X:

if [DateTime]>[DateParameter1] and [DateTime]<[DateParameter2] then [X Coordinate] end

Since that will be null outside your desired range, it will not be plotted there, but the whole dataset is still available for lookup.

Let me know if this is close. It may take a couple of iterations to get all the way to what you want.

N.

• ###### 2. Re: How do I compare GPS stops by time and location?

Hi Noah,

I appreciate the help on the map side but I first need help designing a method for identifying vehicle stops that occur within x number of minutes and x number of miles of each other with both x's defined by separate parameters.

For example, if the "Time" parameter is set to 5 minutes and the "Miles" parameter is set to 3 miles, I want to see all of the vehicle stops (pickups and/or drop-offs) that occurred with 5 minutes AND 3 miles of each other.

Let's take a look at a simple example of my goal...

Vehicle   TripID  Pickup Time   Drop-off Time

100      0001      7:00 AM         7:30 AM

105      0007      7:34 AM         7:52 AM

120      0023      8:10 AM         8:29 AM

In this example, Vehicle 100 had a drop-off at 7:30 and Vehicle 105 had a pickup at 7:34 AM which definitely falls within the 5 minute parameter setting. Now, the only remaining question is whether the GPS coordinates for these two stops fall within the 3 miles of each other. As for Vehicle 120, the GPS coordinates of its two stops are irrelevant since the pickup and drop-off times do not fall within 5 minutes of any of the stops made by Vehicles 100 and 105.

I hope this helps clarify the challenge I am facing.

Bobby

• ###### 3. Re: How do I compare GPS stops by time and location?

Gotcha. I've got 3 more questions.

1. Does it matter if it is the origin or destination that is within 3 miles? I read NO above, but just wanted to double check.

2. When you say parameters, you want to be able to adjust these dynamically within Tableau, again I think this is clear, but want to be sure as it is likely to increase the difficulty. Are there any bounds you can put on these, i.e. time parameter<1 hour distance<100 miles. It might help to have something like that if things start to blow up, the tighter the bound easier I expect it to be.

3. Is this your actual data, anonymized or is it a subset of a larger data source. I don't want to go to far down a path if it wont be available later. If this needs to work connected to a data warehouse it would be useful to know more about that so I can anticipate any limitations there.

That said, I've got a couple ideas. I may not have much time until tomorrow evening, but this is an interesting one.

N.

• ###### 4. Re: How do I compare GPS stops by time and location?

Couldn't wait... See if this looks like what you need.

In addition to the first round of reshaping I did a full outer join on the data with itself to get the structure to compute all of the pairwise distances in time and space. Distance is computed using the great circle formula in the Tableau workbook.

If you are working with a larger dataset it may pay to be a bit smarter. Even with your modest 690 row dataset, the first reshaping multiplies that by 2 (1380 rows) and the self join squares that 1380^2=1,904,400. So about twice as big as Tableau public can handle (if that was your plan). This was a bit too much for the JET driver, and I had already done the first step, so I exported the data again after the first reshaping.

OK. I do need to look at a couple other things this evening, try not to ask followup questions until morning.

N.

• ###### 5. Re: How do I compare GPS stops by time and location?

Hi Noah,

#1 - No, it doesn't matter whether the stop is a pickup (a.k.a. origin) or a drop-off (a.k.a. destination). I want to check ALL stops for time and distance proximity.

#2 - If limits are needed, I would be fine with a 1 to 60 minute range for the Time parameter and 1 to 25 mile range for the Distance parameter.

#3 - The packaged workbook has anonymized actual data.

Please let me know if you have any other questions.

And thank you for your interest.

Bobby

• ###### 6. Re: How do I compare GPS stops by time and location?

Cool. I chose wisely then. An easy way to cut your data down would be to filter in the SQL query by adding:

where a.[DateTime] < b.[DateTime]

Right now everything is in the table twice (since A to B and B to A are the same distance). I didn't know enough about the questions to be able to say if that is what you'd want, but it would get you back under a million rows, so Tableau public is back in play.

N.

• ###### 7. Re: How do I compare GPS stops by time and location?

After I reviewed the new datasource you built, I realized what I really need is a "manifest" database view of my data for this particular task. A manifest view would provide me with one row of data for every stop (pickup or drop-off). I also assume that a manifest view would eliminate the need for a self-join. While I can handle a doubling of my record count, a subsequent squaring of the doubling is not feasible for the amount of data I would be analyzing.

Let's say I am able to find/create a manifest view, will it be possible to possible to identify the stops that occur within a preset time and distance range without using the LOOKUP function?

• ###### 8. Re: How do I compare GPS stops by time and location?

What you are describing corresponds to the first reshaping I did on the data:

SELECT [Mean Time to Failure Example Data#csv].[ActualPickupDateTime] AS [DateTime],

[Mean Time to Failure Example Data#csv].[OriginXcoordinate] AS [Xcoordinate],

[Mean Time to Failure Example Data#csv].[OriginYcoordinate] AS [Ycoordinate],

[Mean Time to Failure Example Data#csv].[TripID] AS [TripID],

[Mean Time to Failure Example Data#csv].[VehID] AS [VehID],

'Origin' as [ToFrom]

FROM [Mean Time to Failure Example Data#csv]

Union All

SELECT [Mean Time to Failure Example Data#csv].[ActualDropoffDateTime] AS [DateTime],

[Mean Time to Failure Example Data#csv].[DestinationXcoordinate] AS [Xcoordinate],

[Mean Time to Failure Example Data#csv].[DestinationYcoordinate] AS [Ycoordinate],

[Mean Time to Failure Example Data#csv].[TripID] AS [TripID],

[Mean Time to Failure Example Data#csv].[VehID] AS [VehID],

'Destination' as [ToFrom]

FROM [Mean Time to Failure Example Data#csv]

Sorry, it looks like I attached the wrong file last night. The correct one is attached here. I'm not sure if there is a way around squaring the data since you need to look at all possible pairs. If time and space distance were computed in the query instead of in tableau it might be possible to filter out records that are too far apart, I had trouble getting the formula to work in Jet SQL (probably ARCCOS) but I gave up pretty quickly on that. How much that does for you depends on how aggressive this limit is chosen to be. As I suggested last night, another way to shrink this dataset is to require the time difference to be positive (that will cut in half).

It is possible I'm misunderstanding your requirement. If you want to select a particular point then it should be possible to filter based on distance from that point, I understood your desire was to consider all possible combinations. That seems like a cartesian product if I've ever seen one.

N.

• ###### 9. Re: How do I compare GPS stops by time and location?

Are there any downsides to limiting the time difference to positive values only?

Bobby

• ###### 10. Re: How do I compare GPS stops by time and location?

It depends on what questions you are asking of this data. Does 4 minutes ago qualify as being within 5 minutes? That could make the filtering tricky. On the other hand, if you only count in the forward direction you shouldn't won't need to worry about double counting as much (except possibly when the times are identical), so it might be preferable. So answering based on the information I have, my answer is somewhere between possibly and probably.

• ###### 11. Re: How do I compare GPS stops by time and location?

Yes, I want a wrap-around time window (x number of minutes before / x number of minutes after) when analyzing each stop.

It sounds like I need to create a matrix for calculating the time and distance between all of the stops. It reminds of one of those matrixes from paper atlases where you had an identical list of cities along the x and y axis and calculated distances between any two cities in the grid.

I am going to take a deeper look at your latest workbook after lunch and I'll follow up afterwards.

• ###### 12. Re: How do I compare GPS stops by time and location?

A matrix is a great way to think of this, it motivates why the number of records gets squared.

• ###### 13. Re: How do I compare GPS stops by time and location?

I managed to coax a SQL statement from my DBA that creates a matrix for stop distances.

Could you take a look at the new data and let me know if I am on the right track?

• ###### 14. Re: How do I compare GPS stops by time and location?

I wasn't able to open that connection. I'll look at the sql query if you can paste it into a response though.

N.

1 2 3 Previous Next