Auditing waste collection routes with batch geocoding

A municipal waste contract grows in the way most infrastructure grows: an acquisition here, a new subdivision there, a renegotiated service zone after a council meeting. Five years later the service-address master is a patchwork of CSV exports from three different predecessors of whatever you call your current CRM, and the routes your trucks actually drive were last formally optimised when the fleet was half the size.

Two problems compound silently in that environment. First, route imbalance: one truck finishes at 14:30 while another is still running collections at 18:45 on the same day, covering roughly the same number of stops on paper. Second, billing drift: addresses that are consistently serviced but were never added to the billing file — either because they were on an old paper supplement or because they came in mid-cycle and someone forgot to update both systems. Each unbilled address is small; across a thousand-stop route they add up fast.

Both problems are fundamentally a geocoding problem. You cannot balance routes you cannot place on a map. You cannot diff a billing file against a route file unless both files have been normalised to a consistent address format and coordinate pair. This post walks through the engineering from raw address master to balanced, sequenced, auditable routes — using the batch geocoding endpoint, the Routing API's optimise and matrix endpoints, and reverse geocoding to resolve driver-reported GPS anomalies.

Why this is not the same as a parcel dispatch problem

Before getting into the code it is worth being precise about what kind of routing problem we are solving, because the engineering differs from a same-day parcel dispatch problem in ways that change the tooling choices.

In parcel dispatch, a new manifest arrives each morning and the stops are different every day. Priority, time windows, and recipient availability dominate the optimisation. The post on managing 5,000 stops per day in a dispatch console covers that pattern.

Municipal waste collection is different in three ways. The stops are fixed and recurring — you visit the same 340 addresses every Tuesday, week after week. The sequence matters for physical flow — the truck's compactor loads from back to front, and doubling back costs real fuel. And critically, there is a billing reconciliation dimension that parcel dispatch does not have: you need to verify that every stop you are driving past is invoiced, and every invoiced address is actually on a route.

Those differences push you towards: batch geocoding the full service-address master once (not daily), route balancing using a distance matrix rather than ad hoc assignment, stop sequencing with an optimise call that respects road topology, and a reconciliation diff that runs on the geocoded canonical addresses rather than on the raw text strings.

The four-stage engineering workflow

The full audit and rebalance is four stages. Each has a distinct API surface, and they compose cleanly.

1. Geocode the service-address master — batch endpoint, 1 credit per address row.
2. Balance routes across the fleet — distance matrix to compute inter-stop travel costs, then a clustering step in your own code.
3. Sequence each route — optimise endpoint to find the near-optimal stop order per truck.
4. Reconcile against the billing file — diff on canonical address strings and coordinates to surface unbilled stops.

Driver-reported GPS issues (the "couldn't access bin" field in your telematics export) feed a fifth sub-task: reverse geocoding the reported GPS coordinate to a canonical address so the incident links back to a service record. That is covered after the main four stages.

---

Stage 1: Geocode the service-address master

Step 1 — Export and clean the address master

Your address master almost certainly has inconsistencies: "St" vs "Street", missing postcodes, unit numbers in the wrong field. The geocoder is tolerant of these, but a pre-clean pass reduces the number of low-confidence results you need to hand-review later.

Export to CSV. A minimal schema is enough:

stop_id,raw_address,city,postcode,route_id

If you have lat/lng already from a previous system, keep them — but do not trust them blindly. Geocode anyway and compare. Addresses move, parcels get renumbered, and coordinates that were clicked onto a map by a field operative in 2017 are not always where you think they are.

Step 2 — Submit the batch geocoding job

The WEB batch tool accepts a CSV of address rows. Each row is one credit. For a 10,000-stop service master that is 10,000 credits — at the entry paid tier of $54/month for 100,000 calls, the whole master costs about $5.40 to geocode once.

The REST equivalent, if you are scripting this into a pipeline:

# Geocode a single address to verify the endpoint before batch
curl -s "https://csv2geo.com/api/v1/geocode" \
  --data-urlencode "q=14 Ferndale Road, Birmingham, B5 7RP" \
  --data-urlencode "api_key=$CSV2GEO_KEY" \
  -G | jq '.results[0] | {lat, lng, confidence, formatted_address}'

For the full batch in Python:

import csv
import os
import time
import requests

API = "https://csv2geo.com/api/v1/geocode"
KEY = os.environ["CSV2GEO_KEY"]

def geocode_address(raw_address, city, postcode):
    q = f"{raw_address}, {city}, {postcode}"
    r = requests.get(
        API,
        params={"q": q, "api_key": KEY},
        timeout=30,
    )
    r.raise_for_status()
    results = r.json().get("results", [])
    if not results:
        return None, None, 0.0, None
    top = results[0]
    return (
        top.get("lat"),
        top.get("lng"),
        top.get("confidence", 0.0),
        top.get("formatted_address"),
    )

enriched = []
with open("service_master.csv") as f:
    reader = csv.DictReader(f)
    for row in reader:
        lat, lng, conf, formatted = geocode_address(
            row["raw_address"], row["city"], row["postcode"]
        )
        enriched.append({
            **row,
            "lat": lat,
            "lng": lng,
            "confidence": conf,
            "formatted_address": formatted,
            "geocode_flag": "review" if conf < 0.7 else "ok",
        })
        time.sleep(0.05)  # stay well inside rate limits

with open("service_master_geocoded.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=enriched[0].keys())
    writer.writeheader()
    writer.writerows(enriched)

The same loop in Node:

import fs from 'node:fs';
import { parse } from 'csv-parse/sync';
import { stringify } from 'csv-stringify/sync';

const API = 'https://csv2geo.com/api/v1/geocode';
const KEY = process.env.CSV2GEO_KEY;

async function geocodeAddress(raw, city, postcode) {
  const q = `${raw}, ${city}, ${postcode}`;
  const url = `${API}?q=${encodeURIComponent(q)}&api_key=${KEY}`;
  const r = await fetch(url);
  if (!r.ok) throw new Error(`http ${r.status}`);
  const data = await r.json();
  const top = data.results?.[0];
  if (!top) return { lat: null, lng: null, confidence: 0, formatted_address: null };
  return {
    lat: top.lat,
    lng: top.lng,
    confidence: top.confidence ?? 0,
    formatted_address: top.formatted_address ?? null,
  };
}

const rows = parse(fs.readFileSync('service_master.csv'), { columns: true });
const enriched = [];

for (const row of rows) {
  const geo = await geocodeAddress(row.raw_address, row.city, row.postcode);
  enriched.push({
    ...row,
    ...geo,
    geocode_flag: geo.confidence < 0.7 ? 'review' : 'ok',
  });
  await new Promise(r => setTimeout(r, 50)); // 20 req/s self-throttle
}

fs.writeFileSync(
  'service_master_geocoded.csv',
  stringify(enriched, { header: true })
);

Any row with confidence < 0.7 goes into a manual review queue. Do not route on low-confidence coordinates — a stop placed 400 m from its actual gate causes exactly the kind of "couldn't access bin" driver report you will spend the rest of this post trying to resolve.

---

Stage 2: Balance routes across the fleet

Step 3 — Build the distance matrix for inter-stop travel costs

You have geocoded coordinates for every stop. Now you need to decide which stops belong to which truck. Naive geographic balancing (draw a Voronoi partition) ignores road topology — a river, a motorway with limited crossings, or an industrial estate with one access road will punish a geographically "balanced" partition badly.

The matrix endpoint gives you actual road-travel costs between any set of points, which is what you need to make the partitioning sensible.

# Matrix call — origins and destinations can be the same set of stops
# for a full all-pairs cost table
curl -s -X POST "https://csv2geo.com/api/v1/routing/matrix" \
  -H "Content-Type: application/json" \
  -d '{
    "api_key": "'"$CSV2GEO_KEY"'",
    "locations": [
      {"lat": 52.4862, "lng": -1.8904},
      {"lat": 52.4910, "lng": -1.8831},
      {"lat": 52.4775, "lng": -1.9012}
    ],
    "profile": "driving"
  }' | jq '.durations'

The response is a 2-D array of travel durations in seconds — durations[i][j] is the road-time from stop i to stop j. For a 300-stop route this is a 300×300 matrix (90,000 values), which is tractable in memory and fast to compute server-side.

In Python, use the matrix as the distance metric for a clustering algorithm. A simple approach that works well for waste collection (where the depot's location matters):

import numpy as np
from scipy.spatial.distance import squareform
from scipy.cluster.hierarchy import linkage, fcluster

def cluster_stops(durations_matrix, n_trucks):
    """
    durations_matrix: 2-D list of travel durations in seconds.
    Returns a list of cluster labels, one per stop.
    """
    dist = np.array(durations_matrix, dtype=float)
    # Symmetrize (matrix endpoint returns asymmetric times)
    sym = (dist + dist.T) / 2.0
    condensed = squareform(sym)
    Z = linkage(condensed, method="ward")
    labels = fcluster(Z, t=n_trucks, criterion="maxclust")
    return labels.tolist()

This gives you an initial partition. You will refine it manually for any clusters that violate a hard constraint the algorithm does not know about — a commercial-waste truck that cannot enter a residential access road, for example. The matrix gives you the cost structure; the assignment is still yours to make.

---

Stage 3: Sequence each route

Step 4 — Call the optimise endpoint per truck

Once you know which stops belong to which truck, you need the optimal drive sequence through them. The optimise endpoint solves this — given a depot origin, a list of waypoints, and a return-to-depot flag, it returns the near-optimal ordering.

curl -s -X POST "https://csv2geo.com/api/v1/routing/optimise" \
  -H "Content-Type: application/json" \
  -d '{
    "api_key": "'"$CSV2GEO_KEY"'",
    "depot": {"lat": 52.4700, "lng": -1.9100},
    "stops": [
      {"id": "stop_042", "lat": 52.4862, "lng": -1.8904},
      {"id": "stop_107", "lat": 52.4910, "lng": -1.8831},
      {"id": "stop_223", "lat": 52.4775, "lng": -1.9012}
    ],
    "return_to_depot": true,
    "profile": "driving"
  }'

The response returns the stops in optimised order with a total estimated duration. Write the ordered sequence back to your route file:

import requests, os

API = "https://csv2geo.com/api/v1/routing/optimise"
KEY = os.environ["CSV2GEO_KEY"]

def optimise_route(depot_lat, depot_lng, stops):
    """
    stops: list of dicts with keys id, lat, lng
    Returns ordered list of stop IDs.
    """
    payload = {
        "api_key": KEY,
        "depot": {"lat": depot_lat, "lng": depot_lng},
        "stops": stops,
        "return_to_depot": True,
        "profile": "driving",
    }
    r = requests.post(API, json=payload, timeout=60)
    r.raise_for_status()
    data = r.json()
    return [s["id"] for s in data["optimised_stops"]]

Run this once per truck cluster. Write the resulting sequences to your route management system as the new canonical route order. For a fleet of 12 trucks covering 3,600 stops, that is 12 optimise calls — trivial at any billing tier.

A practical note on re-sequencing frequency. For fixed-route municipal collection you do not need to re-run the optimise call daily. Run it when the address master changes — new subdivision added, a stop dropped, a temporary access restriction lifted. Build an admin trigger in your route management system ("re-optimise route 7") rather than automating it on a nightly cron. The sequence is stable by design; over-optimising it creates driver confusion and reduces the performance benefit of a familiar route.

---

Stage 4: Billing reconciliation — catching unbilled addresses

Step 5 — Diff the billing file against the geocoded route file

This is the part most operations teams skip, and it is where the most immediate financial recovery sits.

Your billing file has one set of addresses. Your geocoded route file has another. After geocoding both to canonical formatted_address strings and coordinate pairs, the diff is straightforward:

import csv

def load_addresses(path, id_col, addr_col):
    """Returns a dict keyed by formatted_address -> row."""
    result = {}
    with open(path) as f:
        for row in csv.DictReader(f):
            key = row[addr_col].strip().lower()
            result[key] = row
    return result

route_addrs = load_addresses(
    "service_master_geocoded.csv",
    id_col="stop_id",
    addr_col="formatted_address",
)
billing_addrs = load_addresses(
    "billing_master_geocoded.csv",
    id_col="account_id",
    addr_col="formatted_address",
)

# Stops that are routed but not billed
unbilled = {
    addr: row
    for addr, row in route_addrs.items()
    if addr not in billing_addrs
}

# Accounts that are billed but not on any active route
phantom = {
    addr: row
    for addr, row in billing_addrs.items()
    if addr not in route_addrs
}

print(f"Unbilled service addresses: {len(unbilled)}")
print(f"Billed but unrouted accounts: {len(phantom)}")

The unbilled set is revenue leakage. The phantom set is either a data-entry error in billing, a suspended account that was not removed from the billing master, or a legitimate customer who moved and whose new address never got added to the route.

Both sets need human review, not automated resolution. Write them to exception reports and route them to your billing and operations leads. The code's job is to produce the lists; the business's job is to decide what to do with each line.

Why raw string matching fails here. Without geocoding both files first, a naive string diff will miss "14 Ferndale Rd" vs "14 Ferndale Road", "Flat 3, 14 Ferndale Road" vs "14A Ferndale Road", and any case or punctuation variation. After geocoding, both sides have a formatted_address from the same normalisation pipeline and a coordinate pair — matching on the formatted string gets you 95%+ coverage, and matching on coordinates rounded to four decimal places catches the remaining edge cases where the normaliser chose slightly different representations.

---

Resolving driver-reported GPS issues with reverse geocoding

Telematics exports contain events like "couldn't access bin" with a GPS coordinate logged at the moment the driver tapped the button. That coordinate needs to resolve to a service record so operations can decide whether it was a genuine access issue, a wrong-address stop, or a driver error.

Reverse geocoding does that resolution:

curl -s "https://csv2geo.com/api/v1/reverse" \
  --data-urlencode "lat=52.4863" \
  --data-urlencode "lng=-1.8907" \
  --data-urlencode "api_key=$CSV2GEO_KEY" \
  -G | jq '{formatted_address, confidence}'

In Python, batching the reverse calls for a full day's incident log:

import requests, os, csv

API = "https://csv2geo.com/api/v1/reverse"
KEY = os.environ["CSV2GEO_KEY"]

def reverse_geocode(lat, lng):
    r = requests.get(
        API,
        params={"lat": lat, "lng": lng, "api_key": KEY},
        timeout=30,
    )
    r.raise_for_status()
    results = r.json().get("results", [])
    if not results:
        return None, 0.0
    top = results[0]
    return top.get("formatted_address"), top.get("confidence", 0.0)

incidents = []
with open("telematics_incidents.csv") as f:
    for row in csv.DictReader(f):
        addr, conf = reverse_geocode(row["gps_lat"], row["gps_lng"])
        incidents.append({
            **row,
            "resolved_address": addr,
            "confidence": conf,
        })

The resolved address links the incident to a row in the service master. A GPS coordinate 80 m from the stop's recorded location is probably a driver who parked at the corner and walked — not a wrong-address error. A coordinate 400 m away might indicate the driver confused two adjacent streets, which is a data quality problem in the route sequence worth fixing.

For accuracy context: the distance-to-resolved-address metric is your leading indicator of route-data quality. When you run a fresh geocoding pass on the service master and re-sequence routes with the optimise endpoint, that average distance should drop. See Reverse-Geocoding Accuracy and the Distance Meters for how to set reasonable thresholds.

---

Observability: what to instrument

A waste route audit is a batch operation that runs infrequently (once per contract cycle, once per major address-master update). But the reverse-geocode step for driver incidents runs daily. Instrument both.

For the batch geocoding run, log:

• Total addresses submitted
• Count with confidence >= 0.7 (your "clean geocode" rate)
• Count with confidence < 0.7 (sent to manual review)
• Count with null result (unresolvable — investigate)

For the daily driver-incident resolution, log:

• Count of incidents processed
• Mean and maximum distance between reported GPS and resolved stop coordinate
• Count where resolved address matched a stop in the service master vs. unmatched

If the clean geocode rate on your service master drops below 90% after an update, something changed upstream — a new estate with addresses that have not yet propagated into the reference dataset, or a bulk import from a legacy system with malformed fields. Catch it before it causes a routing problem.

The post on observability for geocoding pipelines covers the full metrics schema for instrumenting a production geocoding pipeline; the patterns apply directly here.

---

Caching strategy for a recurring fixed-route operation

Because the stops are fixed and recurring, the geocoding results are almost perfectly cacheable. An address that was geocoded correctly last month is still at the same coordinates this month — land does not move on operational timescales. Cache the (formatted_address, lat, lng, confidence) tuple keyed by the canonical address string. Invalidate only when the address master record is explicitly updated.

At 10,000 stops and a monthly re-run, without caching you spend 10,000 credits every month. With a local cache table that persists across runs, you spend 10,000 credits once on the initial run and then only the delta — new addresses added since last run, which in a mature operation might be 50-200 per month.

The 90% cost reduction through caching post covers the implementation pattern. For a waste-management context the relevant addition is a cache-validity check: if the address record's updated_at timestamp is newer than the cached geocode timestamp, re-geocode that row regardless of the cache hit.

---

What this workflow does not do

Be precise about scope. This workflow gives you:

• Clean, coordinates-attached service addresses
• A route assignment that reflects road-network travel costs rather than crow-flies distance
• An optimised stop sequence per truck, computed from real road topology
• A diff between your route file and your billing file surfacing unbilled and phantom accounts
• A mechanism to link driver-reported incidents back to service records

It does not give you:

• Real-time traffic-aware re-routing during the collection day — the optimise endpoint works on historical travel-time estimates, not live traffic feeds
• Fuel consumption or cost-savings estimates — those come from your fleet telematics, not from the geocoding API
• Automated billing updates — the reconciliation diff produces a list; a human authorises the changes
• Legal or contractual compliance with your waste-management licence conditions — that is your solicitor's problem, not your geocoder's

---

Frequently Asked Questions

How many credits does a full audit of a 10,000-stop service master cost? Ten thousand credits for the geocoding pass — one per address row. Add credits for the billing-file geocoding pass (typically a similar number). The matrix and optimise calls cost additional credits depending on the fleet size and stop counts. For a 12-truck fleet covering 10,000 stops, the full audit fits comfortably within the 100,000-call paid tier at $54/month. Run the batch tool on the WEB interface for a precise credit estimate before committing.

Can I use the batch tool on the website rather than the REST API? Yes. The WEB batch tool accepts a CSV upload and returns an enriched CSV — geocoded coordinates and confidence scores per row. It is the fastest way to run a one-off audit without writing any code. The REST API is the right choice when the geocoding step is embedded in a pipeline that also calls the matrix and optimise endpoints.

What confidence score threshold should I use to flag stops for manual review? A threshold of 0.7 is a reasonable starting point. Below 0.7, the geocoder has made a non-trivial interpretive choice — possibly a partial address match, a street-level centroid rather than a parcel centroid, or a fallback to postcode. For waste collection, a stop placed at a street centroid rather than a specific building gate matters — a compactor truck cannot guess which end of a 400 m street the bin is at.

How do I handle new addresses that appear after the initial audit? Build a lightweight differential process: maintain a local table of geocoded stops keyed by stop_id. When a new stop is added to the service master, geocode it individually via the REST API, write the result to the table, and trigger a re-optimise call for the affected truck's route. You do not need to re-geocode the entire master. See Idempotent Geocoding — Safe to Retry for the pattern that makes incremental updates safe to run repeatedly without double-billing.

The driver-incident GPS coordinates are sometimes 200–300 m from the stop. Is that a problem with the geocoder? No — the geocoder returns the best match for the coordinate you pass. A 200 m offset usually means the driver logged the event while the truck was still moving toward the stop, or parked at a junction rather than at the kerb in front of the property. Compare the resolved address to the assigned stop; if they match (or are adjacent), the incident is a genuine access note. If the resolved address is a completely different street, investigate the route sequence — the driver may have attended the wrong stop.

Should the route re-optimisation run automatically on a schedule? For fixed-route municipal collection, no. Unlike a dynamic parcel operation, your route structure is a contract artefact — changing it without operational sign-off creates driver confusion and can breach service-level agreements. The right trigger is a manual "re-optimise route" action taken by an operations manager after a significant change to the address master, not a nightly cron.

Does the billing reconciliation diff work if the billing file uses a different address format? Only after both files have been geocoded and normalised. Raw string matching across two systems with different address conventions will produce false positives and miss genuine matches. Geocode both files through the same API and match on formatted_address (the normalised string the geocoder returns) plus a coordinate proximity check for edge cases. This is the key reason to geocode the billing master, not just the route master.

---

Related Articles

• Managing 5,000 stops per day in a dispatch console — parcel dispatch routing patterns, and why they differ from fixed-route municipal collection
• Caching geocoding results — 90% cost reduction — cache the service master between audit cycles and pay only for new addresses
• Reverse-geocoding accuracy and the distance meters — how to measure and reason about coordinate-to-address resolution quality
• Exponential backoff — when to retry, when to stop — retry policy for batch geocoding runs that span thousands of addresses
• Observability for geocoding pipelines — metrics that matter — what to instrument in a recurring batch operation so problems surface before they become route errors

---

*I.A. / CSV2GEO Creator*