Understanding Path Finding with PostGIS, Pgrouting, and Node.js

As a technical blogger, I’ve encountered numerous queries and problems when working with spatial data. Recently, I came across a question on Stack Overflow that required me to explain how to modify a query to extract path information in the form of latitude and longitude using PostGIS, pgrouting, and Node.js.

In this article, we’ll break down the process step-by-step, exploring the underlying concepts and providing examples to illustrate each part.

Background: Understanding PostGIS and Pgrouting

PostGIS is a PostgreSQL extension that adds support for spatial data types and functions. It provides an efficient way to store and manipulate geometric objects, such as points, lines, and polygons.

Pgrouting is a routing module built on top of PostGIS, designed specifically for finding shortest paths between points in a network. It uses Dijkstra’s algorithm to calculate the path between nodes.

In this article, we’ll focus on using pgrouting with Node.js to retrieve path information in the form of latitude and longitude.

Query Explanation

The original query uses a Common Table Expression (CTE) to find the shortest path using pgr_dijkstra:

WITH dijkstra AS (
  SELECT * 
  FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
     FROM edges_noded',230965,3338,false)) 

The pgr_dijkstra function takes several arguments:

• The first argument is a SQL query that selects the edge IDs (id), source nodes (source), target nodes (target), and distance values (cost).
• The second and third arguments specify the origin and destination points, respectively.
• The fourth argument (false) indicates whether to use an alternative routing algorithm.

The CTE then joins this result with the original edges_noded table using the edge ID:

SELECT seq, 
  CASE WHEN dijkstra.node = edges_noded.source THEN ST_X(edges_noded.the_geom)
  ELSE ST_X(ST_Reverse(edges_noded.the_geom)) END AS route_geom_x,
  CASE WHEN dijkstra.node = edges_noded.source THEN ST_Y(edges_noded.the_geom)
  ELSE ST_Y(ST_Reverse(edges_noded.the_geom)) END AS route_geom_y 
FROM dijkstra JOIN edges_noded ON(edge = id) 
ORDER BY seq

Here, the query extracts the x and y coordinates of each node in the path. If a node is the source point, it uses the original geometry; otherwise, it reverses the direction to get the correct route.

Converting Coordinates to Latitude and Longitude

To convert the extracted coordinates to latitude and longitude, we can use various formats provided by PostGIS.

One common format is WKT (Well-Known Text), which represents geometric objects as a string:

SELECT seq, ST_AsText(the_geom) FROM edges_noded;

This query returns the original geometry in WKT format.

Another option is GeoJSON, a JSON-based format that provides detailed information about spatial objects:

SELECT seq, ST_AsGeoJSON(the_geom) FROM edges_noded;

Using GeoJSON, we can extract the latitude and longitude values from the path geometry:

WITH dijkstra AS (
  SELECT *
  FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
    FROM edges_noded',230965,3338,false)) 
SELECT seq, 
  CASE WHEN dijkstra.node = edges_noded.source THEN ST_X(edges_noded.the_geom)
  ELSE ST_X(ST_Reverse(edges_noded.the_geom)) END AS route_geom_x,
  CASE WHEN dijkstra.node = edges_noded.source THEN ST_Y(edges_noded.the_geom)
  ELSE ST_Y(ST_Reverse(edges_noded.the_geom)) END AS route_geom_y 
FROM dijkstra JOIN edges_noded ON(edge = id) 
ORDER BY seq

Retrieving Path Information in Latitude and Longitude Format

To retrieve the path information in latitude and longitude format, we can use the extracted coordinates:

WITH dijkstra AS (
  SELECT *
  FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
    FROM edges_noded',230965,3338,false)) 
SELECT seq, 
  CASE WHEN dijkstra.node = edges_noded.source THEN ST_X(edges_noded.the_geom)
  ELSE ST_X(ST_Reverse(edges_noded.the_geom)) END AS route_geom_x,
  CASE WHEN dijkstra.node = edges_noded.source THEN ST_Y(edges_noded.the_geom)
  ELSE ST_Y(ST_Reverse(edges_noded.the_geom)) END AS route_geom_y 
FROM dijkstra JOIN edges_noded ON(edge = id) 
ORDER BY seq

Using this extracted data, we can calculate the latitude and longitude values for each node in the path:

WITH dijkstra AS (
  SELECT *
  FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
    FROM edges_noded',230965,3338,false)) 
SELECT seq, route_geom_x AS lat, route_geom_y AS lon 
FROM dijkstra JOIN edges_noded ON(edge = id) 
ORDER BY seq

Conclusion

In this article, we explored how to modify a query to extract path information in the form of latitude and longitude using PostGIS, pgrouting, and Node.js. We discussed the underlying concepts, including spatial data types and functions, routing algorithms, and coordinate conversion.

By following these steps, you can efficiently retrieve path information from your network data and perform various analyses or visualizations to gain insights into your data.

Last modified on 2023-11-28