Adding Routing Capabilities with pgRouting in a Spring Boot REST API

What is pgRouting?

pgRouting is an open-source extension that adds routing functionality to PostGIS/PostgreSQL. It allows you to compute shortest paths, driving distances, and custom cost routes over a graph of nodes and edges.

Preparing Your OSM Data for Routing

Instead of importing all OSM features, focus on roads by using osm2pgrouting:

sudo apt install osm2pgrouting
osm2pgrouting -f serbia-latest.osm.pbf -d osmdb -U postgres -W secret --clean

This tool creates a ways table with columns like source, target, length, reverse_cost, and geom.

Setting Up pgRouting

Enable the pgRouting extension in your database:

CREATE EXTENSION pgrouting;

Make sure your ways table includes a proper network structure: source/target nodes and cost/reverse_cost values.

Creating the Routing Graph

Verify and build the routing graph:

SELECT pgr_createTopology('ways', 0.0001, 'geom', 'id');

This function assigns source and target values for each edge based on topology.

Writing SQL for Shortest Path

To compute the shortest path between two nodes:

SELECT * FROM pgr_dijkstra(
  'SELECT id, source, target, cost, reverse_cost FROM ways',
  start_vertex, end_vertex,
  directed := true
);

To find the closest node to a given point:

SELECT id FROM ways_vertices_pgr
ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(lng, lat), 4326)
LIMIT 1;

Integrating pgRouting into Spring Boot

Use JdbcTemplate for custom routing queries:

@Autowired
private JdbcTemplate jdbcTemplate;

public List<RouteSegment> calculateRoute(double fromLat, double fromLng, double toLat, double toLng) {
  long source = findNearestVertex(fromLat, fromLng);
  long target = findNearestVertex(toLat, toLng);

  return jdbcTemplate.query("SELECT * FROM pgr_dijkstra(...) JOIN ways ...", rowMapper);
}

Define a model RouteSegment with fields like geom, cost, seq.

REST Endpoint for Route Calculation

@RestController
@RequestMapping("/api/route")
public class RouteController {

  @Autowired
  private RoutingService routingService;

  @GetMapping
  public List<RouteSegment> getRoute(@RequestParam double fromLat,
                                     @RequestParam double fromLng,
                                     @RequestParam double toLat,
                                     @RequestParam double toLng) {
    return routingService.calculateRoute(fromLat, fromLng, toLat, toLng);
  }
}

Rendering Route Geometry

If geom is returned in WKB or WKT format, you can parse it into LineString in Java, or encode it as GeoJSON for frontend use:

SELECT ST_AsGeoJSON(geom) as geojson FROM ways WHERE id IN (...)

Optimizations and Indexing

Ensure GIST index exists:

CREATE INDEX idx_ways_geom ON ways USING GIST (geom);

Consider caching frequently requested routes.

---

Conclusion and Further Enhancements

With pgRouting integrated, your REST API can now calculate optimal routes over real-world road networks. Next steps might include:

• Elevation-aware routing
• Turn restrictions
• Isochrone generation (travel time polygons)
• Integration with frontends like Leaflet or MapLibre