postgis最短路径查询
资料准备：postgres、postgis、pgrouting
1. 下载shp文件
2. 通过PostGis Shapefile Import/Export Manager将shp导入到Postgres中。注意SRID设为4326,选项中勾选Generate
simple geometries instead of MULTIgeometries（为了将多线转成线）。
3. 通过pgAdmin 4查看数据
4. 通过SQL截取全国路网数据产生新表pg_roads;
5. 通过ST_DumpPoints函数切分线LINESTRING按顺序获取线中的所有点POINT从而生成pg_points表。
create temp sequence temp_point_seq;
create table pg_points as SELECTnextval(‘temp_point_seq’) as id,(ST_DumpPoints(geom)).geom aspoint,(ST_DumpPoints(geom)).path[1] as index,* FROM pg_roads;
6. 通过ST_MakeLine将pg_points表数据组合成两两相连的线段表pg_points_roads。
create temp sequence temp_roads_seq;
create table pg_points_roads asSELECT nextval(‘temp_roads_seq’) as road_id,gid,ST_MakeLine(pre_point,point)line,id point_id,pre_point_id, index,osm_id, code, oneway, layer, bridge, tunnel FROM
(SELECT lag(point) over (partition by gid order byindex) pre_point,lag(id) over (partition by gid order by index) pre_point_id,*FROM pg_points) tt where index>1;
7. 添加格式化所需字段SOURCE，TARGET，length，cost，reverse_cost
ALTER TABLE pg_points_roads ADD SOURCE integer;
ALTER TABLE pg_points_roads ADD TARGET integer;
ALTER TABLE pg_points_roads ADD length double precision;
ALTER TABLE pg_points_roads ADD cost double precision;
ALTER TABLE pg_points_roads ADDreverse_cost double precision;
8. 查看SRID是否为4326，若否则修改。
— 查看SRID是否为4326
SELECT Find_SRID(‘public’, ‘pg_points_roads’, ‘line’);
— 修改SRID为4326
SELECTUpdateGeometrySRID(‘pg_points_roads’,’line’,4326);
9. 格式化路网前准备：初始化cost等数据。若SOURCE和TARGET非空，则置空。
UPDATE pg_points_roads SET source=null;
UPDATE pg_points_roads SET target=null;
UPDATE pg_points_roads SET length=ST_Length(line);
— 设置双向路线耗费，999999999代表禁止行驶（oneway中B—双向，T—仅反向，F—仅正向）。
UPDATE pg_points_roads SET cost = CASE WHEN oneway=’B’THEN length —双向
WHEN oneway=’T’ THEN 999999999 — 反向
WHEN oneway=’F’ THEN length — 正向
ELSE length END;
UPDATEpg_points_roads SET reverse_cost = CASE WHEN oneway=’B’ THEN length —双向
WHEN oneway=’T’ THEN length — 反向
WHEN oneway=’F’ THEN 999999999 — 正向
ELSE length END;
10. 格式化路网
SELECTpgr_createTopology(‘pg_points_roads’,0.0000001,’line’,’road_id’);
11. 确认road_id为integer，若否，则改为integer（原因：最短路径使用integer）
— 修改road_id类型为integer;
alter table pg_points_roads alterroad_id type integer;
12. 求最短路径：不含方向
SELECT seq, id1 AS node, id2 AS edge, cost FROM
pgr_dijkstra(‘
SELECT road_id ASid,
source::integer,
target::integer,
length::double precision AS cost
FROM pg_points_roads’,
1, 2288,false, false);
13. 求最短路径：含方向
SELECT seq, id1 AS node, id2 AS edge, cost FROM
pgr_dijkstra(‘
SELECT road_id ASid,
source::integer,
target::integer,
cost::double precision,
reverse_cost ::double precision
FROM pg_points_roads’,
1, 2288,true, true);
14. 求按顺序经过多个点的最短路径：从1->2288->1
SELECT * FROM
pgr_dijkstraVia(‘
SELECT road_id AS id,
source::integer,
target::integer,
cost::doubleprecision,
reverse_cost::double precision
FROMpg_points_roads order by id’,
ARRAY[1, 2288,1],true,false,true);
15. 生成选择的点位表和最短路径对应的路径表（可使用联表查询）
16. 安装uDig
17. 打开uDig运行程序，或进入安装目录启动udig.bat
18. 进入uDig界面
19. 选择Layer–>Add..
20. 选择PosGis
21. 建立数据库连接
22. 选择需要查看的数据库和数据库表，可多选表
23. 点击Next–>Next直至结束（注意：shp数据过多可能加载慢或者无法加载，建议逐表添加）
参考资料：
1. http://docs.pgrouting.org/2.0/en/src/common/doc/functions/create_topology.html#pgr-create-topologypgr_createTopology文档
2. http://docs.pgrouting.org/2.0/en/doc/src/developer/sampledata.html#sampledatapgr_createTopology案例
3. postgis-2.2.0dev+ 手册中文版 伏念译.pdf
4. http://download.geofabrik.de/ OSM下载shp
5. http://udig.refractions.net/ uDig下载
转载自：https://blog.csdn.net/RedToMemory/article/details/78293447
