# pgRoutingDemo

**Repository Path**: zl_java/pgr_demo

## Basic Information

- **Project Name**: pgRoutingDemo
- **Description**: 路径规划GIS技术探索（基于pgRouting+SpringBoot+Vue2.x+Leaflet）
- **Primary Language**: Java
- **License**: MulanPSL-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 2
- **Created**: 2023-02-08
- **Last Updated**: 2023-02-08

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README


# 路线查询的例子
此教程将会使用深圳市的一些地点信息，这些地点的地名和对应的shenzhen_roads_vertices_pgr表中的顶点id值为：

- 世界之窗 —— A点
- 锦绣中华民俗村 —— B点
- 欢乐谷 —— C点
- 华侨城体育场 —— D点

![](https://upload-images.jianshu.io/upload_images/1458798-c8a0b9dcb33dd9d3.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

```SQL

ALTER TABLE shenzhen_roads
ADD COLUMN source INTEGER,
ADD COLUMN target INTEGER,
ADD COLUMN cost DOUBLE PRECISION,
ADD COLUMN reverse_cost DOUBLE PRECISION;

UPDATE shenzhen_roads
SET cost = ST_Length(geom), reverse_cost = -1
WHERE oneway = 'F';

UPDATE shenzhen_roads
SET reverse_cost = ST_Length(geom), cost = -1
WHERE oneway = 'T';

UPDATE shenzhen_roads
SET cost = ST_Length(geom), reverse_cost = ST_Length(geom)
WHERE oneway = 'B';

SELECT pgr_createTopology(
	'shenzhen_roads',
	0.001,
	'geom',
	'gid',
	'source',
	'target'
);

```


1. 步行路线：从6831（锦绣中华民俗村-B点）到44540（世界之窗-A点）
```postgres-psql
-- 步行路线：从6831（锦绣中华民俗村）到44540（世界之窗）

SELECT * FROM pgr_dijkstra(
  'SELECT gid AS id, source, target, cost, reverse_cost FROM shenzhen_roads',
  6831, 44540,
  directed := FALSE
);

```

![查询结果](https://upload-images.jianshu.io/upload_images/1458798-aa4d34da595efe38.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

# 第二步：整理途经的节点坐标
- 使用`LEFT JOIN 顶点表`可以一并查询出最短路径途径顶点的坐标

```
SELECT path.seq, path.node, ST_AsEWKT(nodes.the_geom) as node_position
FROM pgr_dijkstra
  (
  'SELECT gid AS id, source, target, cost, reverse_cost FROM shenzhen_roads',
  6831, 44540,
  directed := FALSE
  ) AS path
LEFT JOIN shenzhen_roads_vertices_pgr AS nodes
ON path.node = nodes.id;
```
# 第三步：因当前使用的是3857墨卡托坐标系，有时候需要转换回4326经纬度坐标系
- 使用`ST_Transform(the_geom, 4326)`完成参考坐标系转换操作
```
-- SELECT id, ST_Transform(the_geom, 4326) as the_geom4326
-- FROM shenzhen_roads_vertices_pgr
-- LIMIT 100;

SELECT id, ST_AsEWKT(ST_Transform(the_geom, 4326)) as the_geom4326_ewkt
FROM shenzhen_roads_vertices_pgr
LIMIT 100;
```
![3857墨卡托坐标转为4326经纬度坐标的结果](https://upload-images.jianshu.io/upload_images/1458798-bc839831812f4f6e.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)


---

2. 步行路线：从45268（欢乐谷-C点）到44540（世界之窗-A点）
```postgres-psql

SELECT * FROM pgr_dijkstra(
  'SELECT gid AS id, source, target, cost, reverse_cost FROM shenzhen_roads',
  45268, 44540,
  directed := FALSE
);

```



# PostGIS参考资料

CSDN博客及知乎专栏作者：不睡觉的怪叔叔
https://blog.csdn.net/qq_35732147

- 《PostGIS教程九：空间关系》
https://blog.csdn.net/qq_35732147/article/details/85615057

- 《PostGIS教程八：关于几何图形的练习》
https://blog.csdn.net/qq_35732147/article/details/85338695

- 《PostGIS教程七：几何图形（Geometry）》
https://blog.csdn.net/qq_35732147/article/details/85258273

- 《PostGIS教程六：简单的SQL语句》
https://blog.csdn.net/qq_35732147/article/details/85243978

- 《PostGIS教程五：数据》
https://blog.csdn.net/qq_35732147/article/details/85242296

- 《PostGIS教程四：加载空间数据》
https://blog.csdn.net/qq_35732147/category_8526561.html

- 《PostGIS教程三：创建空间数据库》
https://blog.csdn.net/qq_35732147/article/details/85226864

- 《PostGIS教程二：PostGIS的安装》
https://blog.csdn.net/qq_35732147/article/details/86299060

- 《PostGIS教程一：PostGIS介绍》
https://blog.csdn.net/qq_35732147/article/details/85158177

# pgRouting参考资料
知乎专栏作者：不睡觉的怪叔叔
 https://www.zhihu.com/people/li-yang-qiao-89

- 总目录：https://zhuanlan.zhihu.com/p/146895238
- pgRouting教程一：介绍 https://zhuanlan.zhihu.com/p/82225790
- pgRouting教程二：关于教程 https://zhuanlan.zhihu.com/p/82227139
- pgRouting教程三：安装pgRouting https://zhuanlan.zhihu.com/p/82408769
- pgRouting教程四：准备数据 https://zhuanlan.zhihu.com/p/121602526
- pgRouting教程五：pgRouting算法 https://zhuanlan.zhihu.com/p/121655755
- pgRouting教程六：高级路径查询 https://zhuanlan.zhihu.com/p/86960640
- pgRouting教程七：使用SQL存储过程 https://zhuanlan.zhihu.com/p/135285106
- pgRouting官方文档：pgr_dijkstraCost https://zhuanlan.zhihu.com/p/86539197
- pgRouting官方文档：pgr_dijkstra https://zhuanlan.zhihu.com/p/85905703
- pgRouting官方文档：简单数据 https://zhuanlan.zhihu.com/p/85913987