From Data to Decisions: How Electrum Built a Real-Time Spatial Analysis Engine

Introduction: A Journey Powered by Data

At Electrum, data drives everything we do. It shapes our decisions and helps us tackle the unique challenges of building Jakarta’s electric mobility network. One recurring issue we faced last year clearly illustrates this point: during peak hours, many drivers had difficulty finding available battery swap stations. This situation caused delays and frustration for both riders and operators, revealing inefficiencies in our station planning and resource management.

To solve this, we realized we needed a smarter approach—leveraging spatial data analysis combined with real-time processing to predict demand accurately and optimize our operations.

Previously, we've shared how ClickHouse helps us process millions of data points instantly and how H3 heatmaps visually guide our network expansion and optimization. These tools have enabled millions of clean kilometers traveled across Jakarta.

However, the real magic happens behind the scenes, where raw GPS and battery data is transformed into valuable insights. This transformation required us to build a robust real-time spatial analysis engine capable of:

• Detecting individual trips accurately from fragmented GPS and battery data.
• Aggregating location data using H3 grids to visualize demand clearly.
• Applying real-world heatmap insights to improve operational efficiency and enhance rider experience.

Hello, it’s me again, Andi Pangeran, and I’m a Principal Software Engineer at Electrum. In this blog post, I'll walk you through our journey—sharing how we approached these technical challenges, the lessons we learned, and how these innovations continue to drive Electrum toward a greener, more sustainable Jakarta.

Why Real-Time Spatial Data Matters

Last year, as our network in Jakarta grew, we encountered an increasingly difficult challenge: drivers often couldn't find available battery swap stations during peak hours. This wasn’t due to a lack of stations, but rather our inability to predict exactly when and where demand would surge. We realized that relying solely on historical data wasn't enough—we needed insights at the very moment events were happening.

Real-time spatial data became the clear answer to this challenge. By capturing and analyzing GPS locations and battery status instantly, we could better anticipate rider behavior, optimize station placement, and improve the overall experience.

Transforming Raw Data into Instant Insights

Handling more than a million GPS data points hourly presents a significant technical challenge. These data points come from various fragmented sources: GPS locations, battery statuses, and telemetry data. To make sense of this complexity, we built a real-time spatial analysis system with two core capabilities:

• Instant Trip Detection: Automatically identifying when and where a rider begins and ends their journey, including accurate route tracking despite intermittent data signals.
• Dynamic Spatial Aggregation: Using an H3 hexagonal grid system to group data points efficiently, creating real-time heatmaps that clearly illustrate current rider demand and operational hotspots.

By processing this information instantly, our teams gain immediate visibility into operations, transforming the way we manage Jakarta’s EV network.

But before diving deeper into exactly how this powerful spatial analysis works, it's essential to understand some foundational spatial concepts—particularly latitude, longitude, and why the H3 grid system has become central to our approach.

Foundations of Spatial Data and the Power of H3 Grids

To effectively turn millions of GPS data points into useful insights, we first needed to understand the basics of spatial data. As software engineers working on EV motorcycles in Jakarta, mastering spatial data concepts was crucial for building a system that could transform raw location data into actionable insights.

Geospatial 101: Understanding Spatial Data

Spatial data refers to any information tied to a specific location on Earth. At its core, spatial data consists of three fundamental elements:

• Points: Represent specific locations, such as the GPS position of a motorcycle at a given moment, the start and end of a trip, or the location of a battery swap station.
• Lines: Connect two or more points to represent movement. A rider’s journey across Jakarta, for example, can be visualized as a line connecting a series of GPS points.
• Polygons: Define areas or regions, such as neighborhoods, districts, or coverage zones for battery swap stations. These help us analyze usage patterns across different areas of the city.

Latitude and Longitude: The Basics

Every location on Earth is defined by latitude and longitude. These two coordinates act like a unique "address" for any point on the map. For example, Jakarta’s approximate location is at latitude -6.2° and longitude 106.8°. When a rider moves through the city, their EV motorcycle regularly sends GPS coordinates, creating continuous streams of spatial data.

As data grew exponentially, managing this information became complex. Simply storing GPS points wasn’t enough—we needed a way to analyze and aggregate this data efficiently.

Why We Need Grid Systems

When dealing with millions of GPS points scattered across Jakarta, identifying meaningful trends becomes difficult. Grid systems solve this problem by dividing large geographic areas into smaller, structured sections, making data analysis faster and more efficient.

Grid systems help us:

• Group GPS data into meaningful areas instead of analyzing isolated points.
• Recognize mobility trends at different levels, from city-wide demand to local neighborhood activity.
• Process location data efficiently, reducing the computational load for real-time analysis.

Choosing the Right Grid: Why H3?

Traditional grid systems often use square or rectangular shapes, but these distort real-world distances and movement patterns. Instead, we use H3, a hexagonal grid system developed by Uber, which provides several advantages:

• Complete and Accurate Coverage: Hexagons cover the city seamlessly with no gaps or overlaps, ensuring precise spatial analysis.
• Better Representation of Movement: Unlike squares, hexagons naturally reflect real-world travel patterns, making them ideal for tracking vehicle movement.
• Scalability and Flexibility: H3 grids allow us to zoom in for detailed insights (e.g., station-level demand) or zoom out for a broader citywide perspective.

By structuring spatial data into H3 grids, we simplify analysis and generate dynamic heatmaps that provide a real-time view of Jakarta’s EV mobility trends.

But how do we accurately detect trips when GPS signals are often fragmented and inconsistent? In the next section, I’ll break down how we solved this problem using real-time spatial data processing.

From EV Motorcycles to Data Insights: Telemetry Data Pipeline

To manage Jakarta’s electric mobility efficiently, we first need to understand how motorcycles communicate with our backend systems in real-time.

How Telemetry Data is Generated

Each motorcycle is equipped with multiple sensors that collect and send data continuously while the bike is in operation. The key telemetry data points include:

• GPS Coordinates (Latitude and Longitude): Provides real-time location tracking of each motorcycle.
• Battery Status (State of Charge - SoC): Monitors the battery’s energy level and discharge rate.
• Vehicle Status: Tracks whether the bike is turned on or off, its current speed, and any operational alerts.

High-Level Architecture: How Data Moves

To process millions of data points from thousands of bikes, we built a scalable pipeline that ensures real-time data ingestion and analysis. Here’s how the data flows:

• Motorcycle Sensors & IoT Gateway

Each EV motorcycle has a set of sensors and a battery management system (BMS) that continuously gather GPS, battery, and vehicle status data. This data is transmitted to an IoT gateway for further processing.

• Data Streaming via Kafka

The IoT gateway sends telemetry data to Kafka, a distributed event streaming platform. Kafka ensures that every data point reaches our backend reliably, even in fluctuating network conditions.

• ETL Processing & Transformation

A worker service processes raw Kafka streams, cleaning and structuring the data. This step ensures that missing or inconsistent data is handled before storage.

• Storage in ClickHouse for Analytics

The structured telemetry data is stored in ClickHouse, a columnar database optimized for real-time queries and large-scale analytics. This enables fast retrieval of vehicle activity, trip data, and battery usage patterns.

Sample Data Format

To better understand what telemetry data looks like when it arrives in our system, here’s an example JSON payload from a bike:

{
    "bike_id": "Bike_123",
    "battery_id": "Battery_5678",
    "timestamp": "2024-12-01T08:30:00Z",
    "latitude": -6.22723,
    "longitude": 106.84719,
    "battery_soc": 85,
    "total_mileage": 230,
    "speed_kmh": 40,
    "bike_status": "on"
}

But collecting raw data is just the beginning. The real challenge? Making sense of scattered GPS signals and structuring them into meaningful trips. Let’s dive into how we tackled this next.

The Challenge: Defining Trips from Fragmented GPS Data

GPS data sent by motorcycles arrives as individual latitude and longitude points. However, these points alone don’t clearly define when a journey starts or ends. Several key challenges complicate the process:

• Fragmented Data: GPS signals are sometimes intermittent due to network or environmental issues.
• Ambiguous Trip Boundaries: Without clear markers, it's challenging to determine when exactly a trip begins or finishes.
• Scalability: With thousands of bikes generating millions of points daily, processing must be both accurate and efficient.

Our Solution: Real-Time Trip Detection

To tackle these challenges, we built a real-time trip detection system that processes telemetry data efficiently. The system relies on Redis for tracking the previous state of each bike and ClickHouse for historical lookups.

The high-level process works as follows:

Implementing the Trip Detection Logic

• Telemetry Data Ingestion

1. Bikes continuously send telemetry (GPS, bike status, timestamp) via Kafka.
2. The telemetry data is stored in the battery_metrics table in ClickHouse for historical tracking.

• Fetching the Previous Bike Status from Redis

1. To detect a status change, the system retrieves the previous state from Redis:

import redis

r = redis.Redis(host='redis-server', port=6379, db=0)

def get_previous_status(bike_id):
    return r.get(f"bike_status:{bike_id}")

def update_status(bike_id, status):
    r.set(f"bike_status:{bike_id}", status)

• Comparing Current vs. Previous Status

1. If the previous state was ON and the current state is OFF, a trip has ended.
2. The system queries ClickHouse to find the last “bike-on” record, marking the trip’s start.

• Finding the Trip Start Using ClickHouse

1. To locate the starting point of the trip, we query ClickHouse for the last “bike-on” event:

SELECT bike_id, timestamp AS start_time, latitude, longitude 
FROM battery_metrics 
WHERE bike_id = 'Bike_123' 
AND bike_status = 'on' 
ORDER BY timestamp DESC 
LIMIT 1;

• Publishing the Trip Event to Kafka

1. Once both start-time and end-time are determined, the system publishes the trip event to Kafka:

from kafka import KafkaProducer
import json

producer = KafkaProducer(
    bootstrap_servers=['kafka-broker:9092'],
    value_serializer=lambda m: json.dumps(m).encode('utf-8')
)

def publish_trip_event(bike_id, start_time, end_time):
    event = {
        "bike_id": bike_id,
        "start_time": start_time,
        "end_time": end_time
    }
    producer.send("trip-events", value=event)

Summarizing Trips into Actionable Metrics

After detecting each trip, we summarize the data into meaningful metrics, including:

• Battery Usage (SoC Change): Indicates how much battery was consumed during the journey.
• Distance and Duration: Measures trip length and time taken.
• Speed Analysis: Provides average and median speeds, helping identify unusual riding patterns.

This summarized trip data is stored in a dedicated trips table within ClickHouse for quick access.

SELECT 
    bike_id, 
    min(time_unix) AS start_time, 
    max(time_unix) AS end_time, 
    argMin(latitude, time_unix) AS start_latitude, 
    argMax(latitude, time_unix) AS end_latitude, 
    argMin(longitude, time_unix) AS start_longitude, 
    argMax(longitude, time_unix) AS end_longitude, 
    argMin(soc, time_unix) AS start_soc, 
    argMax(soc, time_unix) AS end_soc, 
    argMin(soh, time_unix) AS start_soh, 
    argMax(soh, time_unix) AS end_soh, 
    argMin(cycle_count, time_unix) AS start_cycle_count, 
    argMax(cycle_count, time_unix) AS end_cycle_count, 
    min(voltage) AS min_voltage, 
    max(voltage) AS max_voltage, 
    quantile(0.5)(voltage) AS median_voltage, 
    min(current) AS min_current, 
    max(current) AS max_current, 
    quantile(0.5)(current) AS median_current, 
    min(speed) AS min_speed, 
    max(speed) AS max_speed, 
    quantile(0.5)(speed) AS median_speed, 
    min(power) AS min_power, 
    max(power) AS max_power, 
    quantile(0.5)(power) AS median_power, 
    sum(power) AS total_power, 
    argMin(total_mileage, time_unix) AS start_mileage, 
    argMax(total_mileage, time_unix) AS stop_mileage, 
    (stop_mileage - start_mileage) AS distance, 
    (start_soc - end_soc) AS soc_decreased
FROM battery_metrics 
WHERE bike_id = 'Bike_123' 
AND time_unix BETWEEN {start_time} AND {end_time} 
GROUP BY bike_id;

Example Trip Summary:

Real-World Application: Operational Insights

With trip data clearly summarized, our operations team can:

• Monitor Fleet Activity: Instantly detect anomalies such as unexpected battery drain or unusual routes.
• Analyze High Consumption Trips: Investigate routes that consistently use more energy than expected.

Detecting individual trips is powerful, but true operational insights come from seeing the bigger picture. By aggregating trip data into H3 heatmaps, we can uncover Jakarta’s high-traffic zones, predict demand surges, and optimize battery swap locations in ways that weren’t possible before. Let’s explore how.

Summarizing Trips into H3 Heatmap Table

Rather than working directly with thousands of scattered GPS points, H3 grids aggregate trip data into geographic clusters, simplifying analyses of mobility patterns, battery consumption, and vehicle distribution across Jakarta.

Detailed H3 Summarization Pipeline

Our pipeline to summarize GPS trip data into H3 clusters involves five main steps:

1️⃣ Retrieving Trip GPS Data

• The trip detection system generates events containing start and end times, vehicle IDs, and associated GPS coordinates.
• We then query the ClickHouse database to fetch all GPS points recorded during these trips.

2️⃣ Converting GPS Points to H3 Cells

• Each GPS point (latitude and longitude) is converted into an H3 hexagonal cell using the geoToH3(latitude, longitude, resolution) function. We use resolution 8, where each cell covers roughly 0.74 km², providing detailed yet efficient geographic segmentation.

3️⃣ Segmenting GPS Data by H3 Cells

• To accurately calculate metrics within each H3 cell, GPS data points must be segmented whenever a vehicle moves from one cell to another.
• Each segment represents a continuous presence within a single H3 cell, allowing accurate aggregation of metrics.

4️⃣ Aggregating Metrics per H3 Segment

• For each segmented data group within an H3 cell, we calculate key metrics including:
  • Time Spent: Total duration the vehicle remained in the cell.
  • Distance Traveled: Measured mileage within the cell.
  • Battery SoC Change: Percentage of battery charge consumed within the cell.
  • Speed Statistics: Minimum, maximum, and median speed.

5️⃣ Storing Aggregated Data into h3_heatmap_table

• Aggregated data per H3 segment is stored into a dedicated ClickHouse table (h3_heatmap_table), significantly improving query speeds and reducing real-time computational load.

Here's a simplified version of our production ClickHouse query that segments and summarizes GPS data:

WITH trip_data AS (
    SELECT 
        geoToH3(latitude, longitude, 8) AS h3_index, 
        time_unix, 
        battery_sn, 
        soc,
        total_mileage,
        speed,
        row_number() OVER (PARTITION BY battery_sn ORDER BY time_unix) AS row_id
    FROM battery_metrics
    WHERE battery_sn = {battery_sn}
      AND time_unix BETWEEN {start_time} AND {end_time}
      AND latitude != 0 AND longitude != 0
),
joined AS (
    SELECT
        t.*, 
        tp.h3_index AS prev_h3_index
    FROM trip_data t
    LEFT JOIN trip_data tp
        ON t.battery_sn = tp.battery_sn AND t.row_id = tp.row_id + 1
),
segmented AS (
    SELECT
        *,
        sum(CASE WHEN h3_index != prev_h3_index OR prev_h3_index IS NULL THEN 1 ELSE 0 END)
            OVER (PARTITION BY battery_sn ORDER BY time_unix) AS segment_id
    FROM joined
),
aggregated AS (
    SELECT
        h3_index,
        battery_sn,
        min(time_unix) AS start_time,
        max(time_unix) AS end_time,
        argMin(soc, time_unix) AS start_soc,
        argMax(soc, time_unix) AS end_soc,
        (start_soc - end_soc) AS soc_decrease,
        dateDiff('second', min(time_unix), max(time_unix)) AS duration_seconds,
        argMin(total_mileage, time_unix) AS start_mileage,
        argMax(total_mileage, time_unix) AS end_mileage,
        (end_mileage - start_mileage) AS distance_mileage,
        arrayReduce('min', groupArray(speed)) AS min_speed,
        arrayReduce('max', groupArray(speed)) AS max_speed,
        arrayReduce('quantile(0.5)', groupArray(speed)) AS median_speed
    FROM segmented
    GROUP BY battery_sn, h3_index, segment_id
)
INSERT INTO h3_heatmap_table
SELECT * FROM aggregated
ORDER BY start_time;

Example: Trip from Tebet to Kemang

Here's how a trip might look after H3 summarization:

This table represents a summarized view of the trip, reducing thousands of raw GPS records into just three key rows. For comparison, querying the raw GPS data might involve a table with multiple columns and thousands of rows, such as:

Visualizing Mobility with Geospatial Insights

Visualizing geospatial data has become a critical part of optimizing electric mobility operations at Electrum. By mapping motorcycle movements, battery usage, and user adoption trends, we significantly improve operational efficiency and enhance rider experiences throughout Jakarta.

Mapping EV Movement: Optimizing Routes and Efficiency

Visualizing motorcycle trips on a map provides valuable insights into popular routes, areas prone to congestion, and patterns in battery consumption. These visualizations help improve routing algorithms, ensuring motorcycles take efficient paths and conserve battery usage.

• Why this matters: Route visualizations highlighted that many motorcycles in South Jakarta took longer paths due to road constraints. Adjusting routing recommendations based on this information reduced battery usage per trip by 12%.

Heatmaps for Battery Demand: Preventing Swap Shortages

Maintaining adequate battery stock at swap stations is crucial. Real-time heatmaps, generated from trip data, accurately forecast where battery demand will peak, enabling proactive battery distribution to high-demand stations.

• Real-world impact: Heatmap analysis revealed peak demand in office areas between 8–10 AM and 5–7 PM. Proactive battery distribution reduced rider wait times by approximately 30%.

Strategic Expansion Using Demand Maps

Geospatial insights guide not only daily operations but also long-term infrastructure planning. Visualizing rider data allows us to identify areas with rapidly growing EV adoption that lack adequate infrastructure.

• How it helped us grow: Demand mapping indicated increased EV motorcycle usage in Jakarta’s outskirts, highlighting the scarcity of swap stations. This insight led to prioritized infrastructure expansion, resulting in a 40% increase in battery swaps.

Key Insight: Making Data Actionable for EV Mobility

At Electrum, the key to successfully optimizing our EV motorcycle operations lies in transforming complex data into simple, actionable visualizations. By converting millions of fragmented GPS and battery data points into clear heatmaps and mobility patterns, we effectively tackle operational challenges, predict battery shortages, and strategically place infrastructure. These real-time visualizations enable quick, informed decisions that enhance efficiency, sustainability, and the overall rider experience.

The Road Ahead: Driving the Future of Electric Mobility

Building an efficient EV motorcycle network at Electrum is just the first step in a broader vision. Our ultimate goal is transforming urban transportation across Indonesia, making it smarter, cleaner, and more accessible.

Moving forward, we're excited about scaling our technology nationwide, integrating advanced AI analytics, and continuously innovating to enhance the rider experience. By sharing our technical journey and insights, we aim to collaborate and inspire the Indonesian tech community to innovate together, contributing collectively to sustainable transportation solutions.

Thank you for following our tech journey—let’s keep building together! 🚀

Join Us in Shaping Jakarta's Future

we invite you to join us in creating a greener transportation future

🔹 Interested in partnering with us? Let’s connect and explore collaboration opportunities.

🔹 Ready to make the switch to electric? Visit our website to learn more about our offerings and how you can be part of Jakarta’s EV revolution.