Hauling Speed Analytics & Geospatial Data Pipeline

01. The Challenge & Core Requirement

Mining and logistical hauling operations generate massive amounts of telemetry data. Each unit logs its Latitude, Longitude, Speed, and Odometer every few seconds, generating thousands of rows across dozens of Excel (XLS) files daily.

Manually processing this data to find out exactly how fast trucks are moving on specific segments of the road (KM markers) was impossible. The raw data contained anomalies (e.g., speed reading 0 when moving), required spatial context (matching a GPS point to a physical road kilometer), and needed complex time-clustering to measure exactly how long a truck drove above the safe speed limit (e.g., 40 km/h) without counting idle breaks.

02. My Core Role & Implementation

I designed and built the entire data processing pipeline using Python, Pandas, and GeoPandas, packaged neatly into a Jupyter Notebook that can be run on a schedule:

Automated Format Conversion: Wrote a script that uses a headless LibreOffice instance to automatically convert legacy .xls files into modern .xlsx files before ingestion, preventing corruption and ensuring `openpyxl` compatibility.
Geospatial Joining (GeoPandas): Mapped every GPS coordinate to a specific route kilometer marker (KM) using gpd.sjoin() with an intersects predicate against predefined bounding box shapefiles.
Direction Classification: Implemented logic to track the change in KM values over time (dKM) to automatically tag if a unit is moving forward (Return) or backward (Hauling) along the route.
Time-Clustered Speed Analytics: Developed custom algorithms like mean_valid_speed() (to ignore anomalous readings) and duration_over40_clustered(), which calculates the exact minutes spent over the speed limit while accurately handling breaks and pauses in the data feed.

03. Interactive Data Pipeline

Below is an interactive demonstration of how the raw telemetry logs are joined spatially and aggregated into clean analytical tables:

01. Raw Coordinate & Speed Feed

Each hauling unit (e.g. TRK-015, TRK-030) transmits location (Latitude/Longitude), speed, and odometer readings every few seconds. This data is merged from dozens of daily XLS files:

#	Date	Unit	Latitude	Longitude	Speed (km/h)	Speed (km/h)
1	2025-12-28 08:14:38	TRK-015_XX1234YY	-2.78471	121.202570	0	30635.225
2	2025-12-28 08:14:26	TRK-015_XX1234YY	-2.78458	121.202680	28	30635.340
3	2025-12-28 08:14:22	TRK-015_XX1234YY	-2.78451	121.202810	42	30635.500
4	2025-12-16 07:49:05	B-015_DD8966UF	-2.832530	121.202950	35	30635.750

02. Spatial Join & Direction Classification

Using GeoPandas, the Latitude/Longitude coordinates are mapped to predefined KM bounding boxes. The change in KM value over time is used to determine if the truck is returning empty or hauling a load:

Actual Plot: GeoPandas Spatial Join

Loading Plotly Cartesian Canvas...

Loading Plotly Cartesian Canvas...

# GeoPandas Spatial Join
gdf_join=gpd.sjoin(gdf_all, gdf_geofence, how="left", predicate="intersects")
# Direction Classification based on Delta KM
def classify_direction(d):
if d > 0: return "Return"  # moving to higher KM
elif d < 0: return "Hauling" # moving to lower KM

03. Speed Metrics Aggregation & Final Output

After grouping, the data is processed to extract the mean valid speed (ignoring zeros or unreasonable speeds), and a time-clustering algorithm computes exactly how long the truck spent driving over 40 km/h:

Daily Average Speed per Segment - Dec 27
DriverUnitKM Route SegmentsAVG SpeedAVG Payload (t)
1234567891011121314151617181920212223
John SmithTRK-053--25393227242126342931393831353330393943412732.5826.71
UnknownTRK-02015232131372726383933322638373533323034-48452632.130.00
David MillerTRK-009234027393025232133413227373530352928393638392231.7024.60
Robert ChenTRK-008--20353322222229303127363732332931413641432831.3524.95
Michael ChangTRK-011323623353226222228353128363529333131363236342630.8226.32
James WilsonTRK-010264123353120193333343126343329302827403534343130.7225.46
William DavisTRK-054--25383025231923342732353830343131333338312730.2924.68
Thomas BrownTRK-003284330373222212127382726313225313130333536312630.1225.03
Daniel TaylorTRK-038113424363225232125333028353528332727383738362529.4925.70
Joseph AndersonTRK-050153224393224222227352829423927332729273035352629.4826.61
Route Average Speed213323333023212326312726323328302727313135312528.1426.03

Daily Average Speed per Segment - Dec 27
John Smith	TRK-053	-	-	25	39	32	27	24	21	26	34	29	31	39	38	31	35	33	30	39	39	43	41	27	32.58	26.71
Unknown	TRK-020	15	23	21	31	37	27	26	38	39	33	32	26	38	37	35	33	32	30	34	-	48	45	26	32.13	0.00
David Miller	TRK-009	23	40	27	39	30	25	23	21	33	41	32	27	37	35	30	35	29	28	39	36	38	39	22	31.70	24.60
Robert Chen	TRK-008	-	-	20	35	33	22	22	22	29	30	31	27	36	37	32	33	29	31	41	36	41	43	28	31.35	24.95
Michael Chang	TRK-011	32	36	23	35	32	26	22	22	28	35	31	28	36	35	29	33	31	31	36	32	36	34	26	30.82	26.32
James Wilson	TRK-010	26	41	23	35	31	20	19	33	33	34	31	26	34	33	29	30	28	27	40	35	34	34	31	30.72	25.46
William Davis	TRK-054	-	-	25	38	30	25	23	19	23	34	27	32	35	38	30	34	31	31	33	33	38	31	27	30.29	24.68
Thomas Brown	TRK-003	28	43	30	37	32	22	21	21	27	38	27	26	31	32	25	31	31	30	33	35	36	31	26	30.12	25.03
Daniel Taylor	TRK-038	11	34	24	36	32	25	23	21	25	33	30	28	35	35	28	33	27	27	38	37	38	36	25	29.49	25.70
Joseph Anderson	TRK-050	15	32	24	39	32	24	22	22	27	35	28	29	42	39	27	33	27	29	27	30	35	35	26	29.48	26.61
Route Average Speed	21	33	23	33	30	23	21	23	26	31	27	26	32	33	28	30	27	27	31	31	35	31	25	28.14	26.03

04. Key Deliverables & Outcomes

The automated pipeline eliminated the need for manual Excel manipulation. What used to take hours of manual VLOOKUPs and formatting is now achieved in seconds.

Management now receives clean, aggregated daily Excel reports that pinpoint exactly which units were speeding, at what specific kilometer marker, and for how long. This granular level of spatial and temporal analytics is critical for enforcing safety protocols and optimizing hauling cycles.

Hauling Speed Analytics & Geospatial Data Pipeline

Data Processing Pipeline

Raw GPS Logs

Conversion

Spatial Join

Algorithm

Excel Report