英文:
Need Path in the form of Lat,long using pgrouting
问题
我正在使用这个查询来使用Node.js查找路径,使用PostgreSQL、PostGIS、pgrouting和postgis_topology。
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 ST_AsText(edges_noded.the_geom)
ELSE ST_AsText(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;})
问题是此查询返回两列,如下图所示。
我认为上图中的 route_geom 列包含路径信息。
如何修改上述查询以获取以纬度和经度形式的完整路径。
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 ST_X(edges_noded.the_geom) AS longitude, ST_Y(edges_noded.the_geom) AS latitude
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
FROM dijkstra JOIN edges_noded ON(edge = id)
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
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
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获取许多格式。以下是一些可能会对您有兴趣的格式:
数据示例
CREATE TEMPORARY TABLE t (seq INT, the_geom GEOMETRY);
INSERT INTO t VALUES (1,'POINT(1 2)'),(2,'POINT(2 3)');
PostGIS几何格式 - 正如您目前所做的那样。
SELECT seq, the_geom FROM t;
seq | the_geom
-----+--------------------------------------------
1 | 0101000000000000000000F03F0000000000000040
2 | 010100000000000000000000400000000000000840
(2 Zeilen)
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)
作为WKT - ST_AsText
SELECT seq, ST_AsText(the_geom) FROM t;
seq | st_astext
-----+------------
1 | POINT(1 2)
2 | POINT(2 3)
(2 Zeilen)
.. 或者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)
所以您的CTE
很可能看起来像这样
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论