Advanced Open-Source EV Route Planning with PostgreSQL, PostGIS, and pgRouting (MapBox Alternative)

Introduction

As an avid EV (Electric Vehicle) enthusiast and technology, I recently stumbled upon an intriguing feature in Mapbox: a comprehensive solution for EV trip planning using a global database of EV chargers. This feature, designed to predict range accurately and provide estimates of remaining battery on arrival, sparked my curiosity. It integrates live vehicle data, driver behavior, current and predicted traffic, and contextual data like elevation. Inspired by this, I embarked on a journey to develop a similar solution using open-source tools: PostgreSQL, PostGIS, and pgRouting.

In this blog, I’ll share my journey, detailing each step and how you, too, can build an EV route planner using these powerful open-source tools.

Discovering Mapbox’s EV Trip Planner

The Feature
Mapbox’s EV trip planning feature caught my attention due to its ability to offer accurate predictions and dynamic routing for EVs. It considers various critical factors such as:

- Live vehicle data
- Driver behavior
- Traffic conditions
- Elevation

The feature’s focus on EV-specific routing needs, like charger locations and battery range, made it an excellent tool for EV drivers.

Inspiration
The capabilities of Mapbox’s solution motivated me to create something similar but using open-source technologies. I aimed to harness the power of PostgreSQL, PostGIS, and pgRouting to build an EV route planner that could:

- Predict travel range accurately
- Provide battery estimates on arrival
- Consider charger locations and capacities
- Be customized and scaled as per need

Embarking on the Open-Source Journey

Chapter 2: Setting the Stage

Act 1: Installing PostgreSQL and PostGIS

My first step was to set up a robust database environment. PostgreSQL was my database of choice, and PostGIS was necessary for handling spatial data.

# Installing PostgreSQL
sudo apt-get install PostgreSQL

# Installing PostGIS

# showing only `how to enable the extension`
CREATE EXTENSION postgis;

These commands install PostgreSQL and PostGIS, turning the database into a platform capable of handling complex geographical data.

Act 2: Enlisting pgRouting

Next, I integrated pgRouting to add advanced routing functionalities to PostgreSQL, essential for EV route planning.

# showing only `how to enable the extension`
CREATE EXTENSION pgrouting;

pgRouting extends PostgreSQL’s capabilities, enabling it to perform complex route calculations, a critical component for our route planner.

Act 3: Importing the Map — OpenStreetMap Data

The foundation of any route planner is a detailed map. I chose OpenStreetMap (OSM) for its comprehensive and free geographical data.

1. Download OSM Data: I selected and downloaded the OSM data for my region of interest in the `.osm.pbf` format from websites like Geofabrik or BBBike.

2. Install osm2pgsql: This tool is essential for converting OSM data into a format that PostgreSQL and PostGIS can use.

sudo apt-get install osm2pgsql

3. Import Data into PostgreSQL: Using osm2pgsql, I imported the OSM data into my PostgreSQL database, which involved specifying my database name and credentials in the command.

osm2pgsql -c -d mydatabase -U myusername -W -H myhost - hstore mydata.osm.pbf

4. Verify the Import: After the import, I ran a few queries to ensure the data was correctly imported into my PostgreSQL database.

SELECT COUNT(*) FROM planet_osm_roads;

5. Optimize for Performance: I created indexes on the imported geographical data to enhance the performance of spatial queries.

CREATE INDEX idx_planet_osm_roads_geom ON planet_osm_roads USING GIST (way);

Chapter 3: Crafting the `calculate_routes` Function

With the database ready, I started writing the `calculate_routes` function, which is the core of the route planner.

Scene 1: Function Definition

CREATE OR REPLACE FUNCTION calculate_routes(
 start_latitude DOUBLE PRECISION,
 start_longitude DOUBLE PRECISION,
 end_latitude DOUBLE PRECISION,
 end_longitude DOUBLE PRECISION,
 current_battery_level DOUBLE PRECISION,
 total_battery_capacity DOUBLE PRECISION,
 car_efficiency DOUBLE PRECISION
) RETURNS TABLE(route_id INT, route_details TEXT, total_time DOUBLE PRECISION) AS $$

This SQL function is designed to take essential parameters like start and end coordinates, battery levels, and car efficiency to calculate the best route for an EV.

Scene 2: Variable Declaration

DECLARE
 charger_combinations ev_chargers[];
 a_combination ev_chargers[];
 route_id_counter INT := 1;
 calculated_time DOUBLE PRECISION;
 route_segment RECORD;
 start_vertex BIGINT;
 end_vertex BIGINT;
BEGIN

Here, I declare variables for charger combinations, route ID counters, and other necessary elements for the route calculation.

Scene 3: Generating Charger Combinations

DECLARE
 possible_charger ev_chargers;
 combination_set ev_chargers[] := ARRAY[]::ev_chargers[];
BEGIN
 FOR possible_charger IN SELECT * FROM ev_chargers LOOP
 IF ST_Distance(
 ST_SetSRID(ST_MakePoint(current_longitude, current_latitude), 4326),
 ST_SetSRID(ST_MakePoint(possible_charger.longitude, possible_charger.latitude), 4326)
 ) <= current_battery_level * car_efficiency THEN
 combination_set := array_append(combination_set, possible_charger);
 END IF;
 END LOOP;
- This involves iterating over the set of possible chargers and generating every subset
END;

In this part of the function, I created logic to determine which chargers are within the EV’s current range and then generated all possible combinations of these chargers.

Scene 4: The Route Calculation Odyssey

FOREACH a_combination IN ARRAY combination_set
LOOP
 calculated_time := 0;
 start_vertex := (SELECT id FROM ways_vertices_pgr 
 ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(start_longitude, start_latitude), 4326) LIMIT 1);

Here, the function iterates through each combination of chargers, calculating the total travel time from the starting point through each charger in the combination.

Scene 5: Charting the Path

FOREACH current_charger_location IN ARRAY a_combination
LOOP
 end_vertex := (SELECT id FROM ways_vertices_pgr 
 ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(current_charger_location.longitude, current_charger_location.latitude), 4326) LIMIT 1);
FOR route_segment IN SELECT * FROM pgr_dijkstra('SELECT id, source, target, cost FROM ways', start_vertex, end_vertex, FALSE)
 LOOP
 calculated_time := calculated_time + route_segment.cost;
 END LOOP;
start_vertex := end_vertex;
END LOOP;sq

In this section, the function uses pgRouting’s `pgr_dijkstra` function to find the shortest path and travel time between each set of chargers.

Scene 6: The Final Stretch

RETURN QUERY SELECT route_id_counter, array_to_string(a_combination, ' -> '), calculated_time;
route_id_counter := route_id_counter + 1;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

The function concludes by compiling the total travel time for each route combination and returning the route details.

Chapter 4: The Magic Comes to Life

To see the route planner in action, I executed the function with specific coordinates:

SELECT * FROM calculate_routes(
 52.5200, 13.4050, - Berlin
 48.8566, 2.3522, - Paris
 50, - Battery level
 100, - Total capacity
 6 - Efficiency

This query brought the route planner to life, illustrating a path from Berlin to Paris, complete with charging stops.

Epilogue: The Route Revealed

This journey, blending coding skills, geographic information systems, and creativity, culminated in a functional and customizable EV route planner. It highlights the vast potential of open-source tools in creating practical solutions for everyday challenges.

Are you ready to embark on your coding adventure? The world of PostgreSQL, PostGIS, and pgRouting is vast and full of possibilities. Happy coding, and may your EV travels be efficient and full of discovery! 🚗⚡🌍