Latest / Logosofia no Dia a Dia / Find the Quietest Time to Drive Using Google's API
Transcript
- [Prompt] custom: Driving in Israel is stressful, especially here in Jerusalem, and at the moment I'm running a lot of | Hosts: corn, herman [Response] Corn: Daniel sent us this one — and I have to say, it's the most Jerusalem problem I've heard in a while. He's been running nocturnal delivery runs across the city, boxes and pickups and drop-offs, and he noticed something: there's this window, somewhere in the dead of night, where the streets go from chaotic to almost empty. Not vibes-empty. And he thought — why not stop guessing and use Google's own traffic data to find, with statistical confidence, the exact quietest time to drive any given route on any given day? Herman: He's right that the data exists. Google's Routes API lets you query predicted travel time with traffic for any future departure time, and the free tier is generous enough that you can sample systematically without spending a shekel. The question is how you turn raw API responses into something actionable — not just "Tuesday at 2 AM feels quiet," but "Tuesday at 2:47 AM is, with 95 percent confidence, the optimal departure window for this specific route. Corn: That's the gap, isn't it? Between the feeling and the number. I love that Daniel's instinct was to distrust his own gut on this. Most people would do a few runs, notice a pattern, and call it wisdom. He wanted the confidence interval. Herman: Which is very on-brand, honestly. But let's be clear about what we're actually building here — because it's not just checking Google Maps at 2 AM and squinting at the green lines. We're talking about programmatically querying the Routes API at regular intervals across the full week, dumping the results into BigQuery, and running statistical analysis to find the true quiet windows. It's a miniature data pipeline for a personal optimization problem. Corn: The "I just want to buy groceries without losing years off my life" data pipeline. Herman: And Jerusalem makes this genuinely non-trivial. You've got narrow streets that were laid out centuries before anyone imagined a car, aggressive driving culture, and traffic patterns that shift dramatically around Shabbat. Friday afternoon the city is a parking lot. Saturday night it surges back to life. Sunday morning is the start of the work week. The quietest hour on a Tuesday might be completely different from the quietest hour on a Thursday. Corn: The question Daniel's really asking is: how do you sample the system densely enough to catch those weekly rhythms, without burning through API credits or overcomplicating the analysis? And what does the output actually look like when you're done? Herman: And I want to walk through the whole thing — the API mechanics, the sampling strategy, the BigQuery SQL, the dashboard you could build on top. But the core insight is that Google's traffic predictions are based on historical aggregates, and by querying them systematically, you're essentially reverse-engineering the model they've already built. You're not measuring traffic directly. You're asking Google what it thinks traffic will be, and then finding the valleys in that prediction surface. Corn: Which raises an interesting question — how good are those predictions for a city like Jerusalem, compared to, say, New York or London? The model is only as good as the data density behind it. Herman: That's a real caveat and we should dig into it. But even with wider confidence intervals, the approach still beats "I dunno, 3 AM feels right." And the free tier makes it accessible — two hundred dollars of monthly credit, and traffic-aware queries cost five credits each. That's roughly eight thousand queries per month before you pay anything. For a personal project sampling five routes at half-hour intervals across the week, you're looking at under two thousand queries. You'd never hit the cap. Corn: The barrier isn't cost. It's knowing how to structure the queries and what to do with the responses. Which is exactly the kind of thing Herman Poppleberry was put on this earth to explain. Herman: I accept this burden. Corn: Before we get into the API guts though — there's something almost philosophical about this project. Daniel's essentially asking: can I replace my subjective experience of the city with a data model, and will that model actually make my life better? The answer isn't obviously yes. You could spend three weekends building this pipeline and discover that the optimal window is, in fact, exactly when you were already driving. Herman: That's the risk of any optimization project, right? You formalize your intuition and the machine says "congratulations, your intuition was correct." But the value isn't just in the answer — it's in the confidence. Knowing that Tuesday at 2:47 AM is optimal with a tight confidence interval means you can plan around it. You can tell someone "I'll pick it up tonight during the window" and actually mean a specific 45-minute block, not a vague "sometime after midnight. Corn: There's a knock-on effect you might not anticipate. Maybe the data reveals that Thursday nights are systematically quieter than Tuesday nights, even though both feel empty to a human driver. That's actionable in a way that vibes alone wouldn't catch. Herman: Or it reveals that the quietest window shifts by 45 minutes depending on whether it's winter or summer, because people's evening patterns change. You'd never notice that anecdotally, but the data would surface it. Corn: Let's get into how you actually pull this off. Where do you start? Herman: The first thing to understand is what the Routes API actually gives you. It's the successor to the old Directions API — same underlying data, but a more modern interface. You send it an origin, a destination, and a departure time, and it returns a pile of JSON that includes something called `durationInTraffic`. That's the predicted travel time in seconds, accounting for typical traffic conditions at that time. Corn: The key word there is "predicted." It's not live sensor data from the road. Herman: When you set `departureTime` to a future timestamp, Google isn't looking at current conditions. It's consulting a historical model — basically saying "for this road segment, on this day of the week, at this hour, here's what traffic typically looks like based on years of anonymized location data from Android devices." It's a statistical prediction, not a measurement. Corn: You're not asking "how bad is traffic right now." You're asking "how bad does Google expect traffic to be at 3 AM on a Wednesday, based on every Wednesday at 3 AM it's ever seen. Herman: And that's what makes this project possible. If you had to sample live traffic for weeks to build your own model, it would take forever. Instead, you're querying Google's pre-built model — effectively reverse-engineering the pattern they've already learned. You run queries at half-hour intervals across the full week, collect the `durationInTraffic` values, and the valleys in that data are your quiet windows. Corn: Which is a clever bit of laziness, honestly. Let Google do the hard work of aggregating years of data, then just ping their model until you've mapped the surface. Herman: It's the data equivalent of asking someone who's lived in the neighborhood for 20 years instead of standing on the corner with a clipboard. Corn: Daniel's specific situation — short delivery runs between known points — makes the sampling tractable. You're not trying to model the whole city. You pick three to five routes you actually drive, and you query those. Herman: Which keeps the query volume manageable. You also have to decide on your sampling density. Half-hour intervals give you 48 data points per route per day. That's granular enough to catch a quiet window that might only last 45 minutes, without being so fine-grained that you're querying every five minutes and blowing through your credits. Herman: Let's walk through an actual query, because the structure matters. You're hitting the endpoint at routes dot googleapis dot com slash directions slash v2 colon computeRoutes. It's a POST request with a JSON body. You specify the origin as a lat-lng pair or a place ID, same for the destination, and then the critical field: departureTime. Corn: That's where you set the future timestamp? Herman: So if I want to know what traffic looks like at 3 AM on a Thursday, I'd set departureTime to something like 2026-07-09 T 03:00:00 Z. There's also a trafficModel parameter — you set it to "best guess," which tells Google to use its default historical model. The alternatives are "optimistic" and "pessimistic," which are useful if you want to see the range, but for this project, best guess is what you want. Corn: The response comes back with what, exactly? Herman: The field we care about is durationInTraffic, returned in seconds. That's the predicted travel time accounting for typical congestion at that departure time. There's also a plain duration field — that's the time without traffic, like the baseline. The difference between the two tells you how much traffic is adding. If duration is 900 seconds and durationInTraffic is 905, you know the roads are basically empty. Corn: You run this query once and you've got one data point. The magic is running it systematically. Herman: And the sampling strategy is where most people would overthink it. Daniel's use case is actually forgiving — he's got 5 routes he cares about, and he needs coverage across the full week. At half-hour intervals, that's 48 time slots per day, times 7 days, times 5 routes. Sixteen hundred and eighty queries per month. The free tier gives you roughly 8,000. You're using about a fifth of your budget. Corn: Which means you could even up the granularity to 15-minute intervals if you wanted, though I'm not sure the model's resolution justifies it. Herman: It probably doesn't. Google's historical aggregates aren't that fine-grained — the predictions smooth out sub-hour variations. Half-hour sampling is the sweet spot. And here's the thing: you don't even need to run these queries continuously. You run the full grid once, dump everything into BigQuery, and you've got your baseline model. Refresh it monthly if you want to catch seasonal shifts. Corn: You're building a table with columns for route ID, day of week, time slot, and duration in seconds. What does the analysis actually look like? Herman: This is where BigQuery earns its keep. The naive approach is to find the single time slot with the lowest durationInTraffic and call that your optimal window. But that's fragile — you might catch a fluke where a holiday or a road closure made one Tuesday look unusually quiet. What you actually want is the 10th percentile window. Corn: Meaning the time range where 90 percent of observed durations are higher, so you know the quiet isn't a one-off. Herman: And BigQuery has a function for exactly this: PERCENTILE_CONT. You'd write something like — SELECT route ID, day of week, time slot, duration seconds, PERCENTILE_CONT duration seconds comma 0.1 OVER PARTITION BY route ID comma day of week AS p10 duration. That computes, for each route and day, the 10th percentile duration across all time slots. Then you filter to the slots that fall at or below that threshold. Corn: You're not asking "what's the single quietest minute." You're asking "what's the band of time where it's consistently quiet. Herman: That band might be 45 minutes wide on a Tuesday and 90 minutes wide on a Friday. The data tells you not just when to leave, but how much slack you have. If the quiet window on Thursday is only 30 minutes, you plan around that differently than if it's a two-hour stretch. Corn: There's a caveat here though that I want to make sure we don't gloss over. Google's traffic predictions for Jerusalem — how dense is the underlying data? Because the model is only as good as the volume of historical location pings it's trained on. Herman: That's the honest uncertainty. Google doesn't publish per-city data density metrics, but we can make reasonable inferences. Jerusalem has a population of about a million people, high Android penetration, and heavy Google Maps usage — both by drivers and through Waze, which Google owns. The data is almost certainly sparser than New York or London, but it's not sparse in absolute terms. You're probably looking at wider confidence intervals, not garbage predictions. Corn: The quiet window might be fuzzier — "sometime between 2 and 3 AM" rather than "2:47 AM precisely" — but the broad pattern should hold. Herman: For Daniel's use case, that's perfectly fine. He's not dispatching ambulances. He's trying to avoid honking. A 45-minute window is actionable even if the edges are soft. Corn: The other thing the model won't catch is anomalies. Road closures, construction, the night before a holiday when everyone's doing last-minute shopping. Those get smoothed out of the historical averages. Herman: The model assumes a typical day. If there's a marathon or a presidential visit, the prediction won't know. But that's where you layer on a sanity check — before you head out, you glance at live traffic to confirm nothing weird is happening. The statistical model gives you the plan; live data gives you the go/no-go. Corn: You've got your data, you've run your percentiles, you know that Route A to Mahane Yehuda is quietest at 2:30 AM on weekdays but 3:15 AM on weekends. What do you actually do with that? You could just memorize it, but the whole point was to stop relying on memory and vibes. Herman: This is where the thing graduates from a spreadsheet exercise to something useful. The simplest version is connecting BigQuery to Looker Studio — which used to be called Data Studio — and building a dashboard that shows, for each route, the top three recommended departure windows ranked by confidence. Corn: You open it on your phone and it just says "Route A: leave between 2:15 and 3:02. Route B: leave between 1:45 and 2:30. Herman: But you can go further. You could write a Cloud Function that runs a fresh batch of queries once a week, updates the BigQuery table, recalculates the percentiles, and then pushes a notification. Imagine getting a Telegram message every Sunday night that says "This week's optimal windows: Tuesday 2:15 to 3:02 AM, Wednesday 2:45 to 3:30 AM, Thursday 1:50 to 2:40 AM. Corn: That's the point where the data stops being interesting and starts being infrastructure. You're not thinking about traffic anymore. You're just doing what the notification says. Herman: The notification is backed by a statistical model you built yourself, not a generic "avoid peak hours" suggestion from a navigation app. There's a comparison here to Waze's planned drives feature, which does something superficially similar — you tell it when you want to arrive, and it tells you when to leave based on predicted traffic. But Waze is optimizing a single trip. It says "for this specific drive on Thursday, leave at 2:35." It doesn't tell you that Thursday is systematically 20 minutes quieter than Tuesday across all your routes. Corn: Waze gives you a departure time. This gives you a strategy. Herman: The aggregate view surfaces patterns that single-trip predictions hide. Maybe you discover that Tuesday nights are consistently worse than Thursday nights because of some regular event you never knew about — a late-night market, a shift change at a big employer. Waze would never tell you that. It would just give you a departure time for each trip and you'd never notice the pattern. Corn: There's a variable here though that I think is worth naming explicitly, because it's easy to miss if you're just staring at API responses. Jerusalem's traffic lights switch to flashing mode after midnight — no red-green cycle, just blinking yellow. That fundamentally changes how traffic flows through intersections. Herman: That's a great catch. When lights are on a timed cycle, congestion builds in predictable waves. After midnight, when they switch to flashing, the flow dynamics change — it becomes more like a yield situation, which can actually be faster if there's no cross traffic, but more unpredictable if there is. The API captures this indirectly because the historical travel times reflect whatever the actual flow was, but the model doesn't know why the pattern shifts at midnight. It just sees the numbers change. Corn: Which means your quiet window might start not when there are fewer cars, but when the lights change mode. The data would show a discontinuity at midnight that's actually an infrastructure effect, not a traffic volume effect. Herman: That's the kind of thing you'd never notice without looking at the data systematically. A human driver just thinks "wow, it's easier to get through intersections late at night." They don't attribute it to the traffic light controller. Corn: Let's talk about what breaks. The model assumes you drive the same way at 3 AM as you do at 3 PM — same speed, same acceleration, same route adherence. But nobody does. At night, with empty roads, you're probably driving faster and taking turns more aggressively. The predicted duration might say 12 minutes, but you're doing it in 9. Herman: Which means the model is conservative for nighttime windows. It's predicting based on aggregate driver behavior, which includes people who still drive cautiously at night. If you know you're faster than average, you can mentally adjust — the quiet window is actually wider than the data suggests. Corn: The flip side is that if Google's traffic data for Jerusalem is sparser than for major US cities — and it almost certainly is — your confidence intervals are wider. The model might say the 10th percentile window is 2:15 to 3:02, but the true window could be 1:45 to 3:30. You're getting a fuzzy picture. Herman: That's fine for this use case. Daniel's not running a logistics company. He's trying to avoid honking. A fuzzy window is still a window. The alternative is guessing, and even a fuzzy data-driven window beats "sometime after midnight feels right. Corn: There's also a hidden assumption that the optimal departure time is the same regardless of direction. But Jerusalem isn't symmetrical — the route from home to Talpiot might have a different quiet window than the return trip, because you're hitting different intersections in a different order, and the traffic light patterns aren't mirrored. Herman: That's why you model each direction as a separate route. It doubles your query volume, but you're still well within the free tier. And the asymmetry might be surprising — maybe the outbound trip is quietest at 2:30 AM but the return is quietest at 3:15 because of how the traffic light flashing sequence interacts with the one-way streets in the city center. Corn: Jerusalem's one-way street topology is its own special kind of chaos. You can see your destination from your car window and still need to drive eight blocks to reach it legally. Herman: Which is exactly why data beats intuition here. Your intuition says "it's 3 AM, the streets are empty, I'll be fine." The data says "yes, but if you leave 20 minutes earlier, you'll avoid the one intersection where late-night taxis cluster near the shuk." That's the level of granularity that makes the project worth building. Herman: If you're sold on the idea, here's where you actually start. Pick three to five routes you drive most often. Not every possible trip — the ones that matter. Home to the shuk, home to Talpiot, home to that one drop-off point you keep visiting. Define them as origin-destination pairs. Corn: You're not guessing at the coordinates. You pull the exact lat-lng from Google Maps for the parking spot or the loading zone, not the building entrance. A 50-meter difference changes which intersection the route hits. Herman: Then you query the Routes API at 30-minute intervals across a full week. That's your 1,680 queries — well inside the free tier. Run it once, dump everything into BigQuery, and you've got your baseline. Refresh it monthly. Corn: The 10th percentile is the real insight here. Don't hunt for the single quietest minute — that's how you build a schedule around a fluke. The percentile gives you a window that's consistently quiet, week after week. Herman: Then the simplest version of making this useful: a scheduled query in BigQuery that runs weekly and emails you the updated optimal windows. No dashboard, no Cloud Function, no push notifications. Just a text file in your inbox that says "Route A: Tuesday 2:15 to 3:02 AM. Route B: Thursday 1:50 to 2:40 AM." You read it, you plan your week, done. Corn: For the non-coders listening — and I know Daniel's comfortable with this stuff, but the principle generalizes — you can do a version of this in Google Sheets with Apps Script. There's a built-in Maps dot getDirections function. It's slower, it's clunkier, you'd sample fewer time slots, but the logic is identical. Zero infrastructure, just a spreadsheet that refreshes itself. Herman: Honestly, for most people, that's the right starting point. Prove the concept in Sheets before you stand up a BigQuery pipeline. If the data shows your quiet window is exactly when you thought it was, you've lost nothing and gained confidence. If it surprises you, now you've got something worth automating. Herman: The thing I keep coming back to is what happens when you scale this from five routes to, say, twenty. At that point you're not just optimizing your grocery runs — you're solving a time-dependent traveling salesman problem. If Route A is quietest at 2:15 AM and Route B is quietest at 3:30 AM, but they're in opposite directions, do you optimize for the quietest departure on each leg, or do you chain them into a single loop that's suboptimal per-leg but saves you a round trip? Corn: That's where it stops being a data project and starts being an operations research problem. And the answer depends on what you're actually minimizing — total time behind the wheel, or total time spent in traffic. They're not the same thing when the roads are empty. Herman: And Google's model doesn't optimize across multiple stops with time-dependent costs. That's something you'd have to build yourself — a cost matrix that varies by hour, fed into a routing solver. It's a weekend project for a motivated person, but the fact that the API makes the cost matrix accessible is what makes it possible at all. Corn: What strikes me is that we're basically describing a feature that navigation apps will eventually just have. Google already owns Waze, Waze already has planned drives, and the gap between "tell me when to leave for this one trip" and "tell me my optimal delivery schedule for the whole week" is mostly a UI problem, not a data problem. Herman: The data's there. The predictions exist. It's just that nobody's packaged them into a weekly optimization layer yet. What Daniel's describing — this DIY pipeline — is essentially a preview of what will be a standard feature in five years. And by building it yourself now, you get the benefit before it's productized. Corn: Which is a pretty good definition of a weird prompt, honestly. "I noticed a thing. Can I hack together the future version of Google Maps using their own API and a database? Herman: The answer is yes. With enough statistical rigor to actually trust the output. Corn: If you've got a weird prompt of your own, send it to us. Show at my weird prompts dot com. Herman: This has been My Weird Prompts. Thanks to our producer Hilbert Flumingtop, and we'll catch you next time. Corn: Preferably at 2:47 AM on a Tuesday.