需要使用pgrouting以经纬度形式提供的路径。

huangapple
go评论112阅读模式
英文:

Need Path in the form of Lat,long using pgrouting

问题

我正在使用这个查询来使用Node.js查找路径，使用PostgreSQL、PostGIS、pgrouting和postgis_topology。

  1. client.query(
  2.   "WITH dijkstra AS (
  3.     SELECT * 
  4.     FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
  5.        FROM edges_noded',230965,3338,false)) 
  6.    SELECT seq, 
  7.      CASE WHEN dijkstra.node = edges_noded.source THEN ST_AsText(edges_noded.the_geom) 
  8.      ELSE ST_AsText(ST_Reverse(edges_noded.the_geom)) END AS route_geom 
  9.    FROM dijkstra JOIN edges_noded ON(edge = id) 
  10.    ORDER BY seq",(err,res)=>{ var data = res.rows;})

问题是此查询返回两列，如下图所示。

需要使用pgrouting以经纬度形式提供的路径。

我认为上图中的 route_geom 列包含路径信息。

如何修改上述查询以获取以纬度和经度形式的完整路径。

  1. client.query(
  2.   "WITH dijkstra AS (
  3.     SELECT * 
  4.     FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
  5.        FROM edges_noded',230965,3338,false)) 
  6.    SELECT seq, 
  7.      CASE WHEN dijkstra.node = edges_noded.source THEN ST_X(edges_noded.the_geom) AS longitude, ST_Y(edges_noded.the_geom) AS latitude
  8.      ELSE ST_X(ST_Reverse(edges_noded.the_geom)) AS longitude, ST_Y(ST_Reverse(edges_noded.the_geom)) AS latitude END AS route_coords 
  9.    FROM dijkstra JOIN edges_noded ON(edge = id) 
  10.    ORDER BY seq",(err,res)=>{ var data = res.rows;})
英文:

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

  1. client.query(
  2. "WITH dijkstra AS (
  3.   SELECT * 
  4.   FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
  5.      FROM edges_noded',230965,3338,false)) 
  6.  SELECT seq, 
  7.    CASE WHEN dijkstra.node = edges_noded.source THEN edges_noded.the_geom 
  8.    ELSE ST_Reverse(edges_noded.the_geom) END AS route_geom 
  9.  FROM dijkstra JOIN edges_noded ON(edge = id) 
  10.  ORDER BY seq",(err,res)=>{ var data = res.rows;})

now problem is that this query returns two columns which is

需要使用pgrouting以经纬度形式提供的路径。

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.

答案1

得分: 1

如果我理解您正确的话，您只需将已经存在的坐标转换为您的应用程序可以读取的格式。您可以直接从PostGIS获取许多格式。以下是一些可能会对您有兴趣的格式：

数据示例

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

PostGIS几何格式 - 正如您目前所做的那样。

  1. SELECT seq, the_geom FROM t;
  2.  seq |                  the_geom                  
  3. -----+--------------------------------------------
  4.    1 | 0101000000000000000000F03F0000000000000040
  5.    2 | 010100000000000000000000400000000000000840
  6. (2 Zeilen)

以纬度和经度分开的列检索数据 - ST_X, ST_Y

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

作为WKT - ST_AsText

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

.. 或者GeoJSON - ST_AsGeoJSON

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

所以您的CTE 很可能看起来像这样

  1. WITH dijkstra AS (
  2.   SELECT *
  3.   FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
  4.     FROM edges_noded',230965,3338,false)) 
  5. SELECT seq, 
  6.   CASE WHEN dijkstra.node = edges_noded.source THEN ST_X(edges_noded.the_geom)
  7.   ELSE ST_X(ST_Reverse(edges_noded.the_geom)) END AS route_geom_x,
  8.   CASE WHEN dijkstra.node = edges_noded.source THEN ST_Y(edges_noded.the_geom)
  9.   ELSE ST_Y(ST_Reverse(edges_noded.the_geom)) END AS route_geom_y 
  10. FROM dijkstra JOIN edges_noded ON(edge = id) 
  11. ORDER BY seq
英文:

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

  1. CREATE TEMPORARY TABLE t (seq INT, the_geom GEOMETRY);
  2. 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.

  1. SELECT seq, the_geom FROM t;
  2.  seq |                  the_geom                  
  3. -----+--------------------------------------------
  4.    1 | 0101000000000000000000F03F0000000000000040
  5.    2 | 010100000000000000000000400000000000000840
  6. (2 Zeilen)

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

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

As WKT - ST_AsText

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

.. or GeoJSON - ST_AsGeoJSON

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

So your CTE will most likely look like this

  1. WITH dijkstra AS (
  2.   SELECT *
  3.   FROM pgr_dijkstra('SELECT id,source,target,distance AS cost 
  4.     FROM edges_noded',230965,3338,false)) 
  5. SELECT seq, 
  6.   CASE WHEN dijkstra.node = edges_noded.source THEN ST_X(edges_noded.the_geom)
  7.   ELSE ST_X(ST_Reverse(edges_noded.the_geom)) END AS route_geom_x,
  8.   CASE WHEN dijkstra.node = edges_noded.source THEN ST_Y(edges_noded.the_geom)
  9.   ELSE ST_Y(ST_Reverse(edges_noded.the_geom)) END AS route_geom_y 
  10. FROM dijkstra JOIN edges_noded ON(edge = id) 
  11. ORDER BY seq

huangapple
  • 本文由 发表于 2020年1月6日 19:03:14
  • 转载请务必保留本文链接：https://go.coder-hub.com/59610928.html
  • node.js
  • pgrouting
  • postgis
  • postgresql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定