sqlnode.jspostgresqlpostgispgrouting

Need Path in the form of Lat,long using pgrouting


I am using this query for finding the path using postgresql , postgis, pgrouting, postgis_topology using nodejs

client.query(
"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 edges_noded.the_geom 
   ELSE ST_Reverse(edges_noded.the_geom) END AS route_geom 
 FROM dijkstra JOIN edges_noded ON(edge = id) 
 ORDER BY seq",(err,res)=>{ var data = res.rows;})

now problem is that this query returns two columns which is

enter image description here

I think route_geom column in above above image has a information of path.

How can I modified the above query to get path in the form of latitude and longitude.

I need the complete path in the form of latitude and longitude.


Solution

  • If I understood you correctly, you just need to convert the already existing coordinates to a format that your application can read. There are many formats you can get directly from PostGIS. Here a few that might interest you:

    Data sample

    CREATE TEMPORARY TABLE t (seq INT, the_geom GEOMETRY);
    INSERT INTO t VALUES (1,'POINT(1 2)'),(2,'POINT(2 3)');
    

    Geometries in the PostGIS geometry format - as you've been doing so far.

    SELECT seq, the_geom FROM t;
     seq |                  the_geom                  
    -----+--------------------------------------------
       1 | 0101000000000000000000F03F0000000000000040
       2 | 010100000000000000000000400000000000000840
    (2 Zeilen)
    

    Retrieving data as latitude and longitude in separated columns - ST_X, ST_Y

    SELECT seq, ST_X(the_geom), ST_Y(the_geom) FROM t;
     seq | st_x | st_y 
    -----+------+------
       1 |    1 |    2
       2 |    2 |    3
    (2 Zeilen)
    

    As WKT - ST_AsText

    SELECT seq, ST_AsText(the_geom) FROM t;
     seq | st_astext  
    -----+------------
       1 | POINT(1 2)
       2 | POINT(2 3)
    (2 Zeilen)
    

    .. or GeoJSON - ST_AsGeoJSON

    SELECT seq, ST_AsGeoJSON(the_geom) FROM t;
     seq |             st_asgeojson             
    -----+--------------------------------------
       1 | {"type":"Point","coordinates":[1,2]}
       2 | {"type":"Point","coordinates":[2,3]}
    (2 Zeilen)
    

    So your CTE will most likely look like this

    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