如何使用postGis实现最短领规划？

Question

如何使用PostGIS和pgRouting实现最短路径规划？
条件是有两张表:

CREATE TABLE "public"."line" (
  "smid" int4 NOT NULL DEFAULT nextval('line_smid_seq'::regclass),
  "smuserid" int4 NOT NULL DEFAULT 0,
  "smtopoerror" int4 NOT NULL DEFAULT 0,
  "smlength" float8 NOT NULL DEFAULT 0,
  "smgeometry" "public"."geometry",
  "smgeoparam" bytea,
  "line_id" varchar(50) COLLATE "pg_catalog"."default",
  "line_length" float8,
  "left_point_id" varchar(50) COLLATE "pg_catalog"."default",
  "left_depth" float8,
  "left_height" float8,
  "left_longitude" float8,
  "left_latitude" float8,
  "right_point_id" varchar(50) COLLATE "pg_catalog"."default",
  "right_depth" float8,
  "right_height" float8,
  "right_longitude" float8,
  "right_latitude" float8,
  "life_length" float8
);

CREATE TABLE "public"."point" (
  "smid" int4 NOT NULL DEFAULT nextval('point_smid_seq'::regclass),
  "smuserid" int4 NOT NULL DEFAULT 0,
  "smgeometry" "public"."geometry",
  "point_id" varchar(50) COLLATE "pg_catalog"."default",
  "longitude" float8,
  "latitude" float8,
  "x" varchar(50) COLLATE "pg_catalog"."default",
  "y" varchar(50) COLLATE "pg_catalog"."default",
  "height" float8,
  "depth" float8,
  "line_id" varchar(50) COLLATE "pg_catalog"."default"
);

需要通过这两张表计算出两个点之间的最短路径规划；以下是我写的实现方案，但是结果是不正确的（查出来的路径乱七八糟的，有连接的，也有不连接的）。

--pgrouting依赖postgis
CREATE EXTENSION PostGIS;
CREATE EXTENSION pgRouting;
--创建字段
ALTER TABLE line ADD COLUMN "source" INTEGER  default null;
ALTER TABLE line ADD COLUMN "target" INTEGER  default null;
ALTER TABLE line ADD COLUMN "the_geom" geometry default null;
ALTER TABLE line ADD COLUMN "cost" DOUBLE PRECISION  default null;
--创建拓扑
-- select pgr_createtopology('line',0.000001,the_geom:='the_geom',id:='gid',source:='source',target:='target',rows_where:='true', clean:=false)
select pgr_createtopology('line',0.000001,the_geom:='the_geom',id:='smid',source:='source',target:='target',rows_where:='true', clean:=TRUE);
-- -- 计算线的长度
UPDATE line SET cost =st_length(ST_Transform(smgeometry,4326));
UPDATE line SET the_geom = st_makeline(st_point(left_longitude,left_latitude),st_point(right_longitude,right_latitude));  
--计算最短距离 测试语句
select b2.SmId as smId,b2.line_id as lineId,a1.seq as seq,b2.left_longitude as leftLongitude,b2.left_latitude as leftLatitude,
       b2.right_longitude as rightLongitude,b2.right_latitude as rightLatitude,a1.agg_cost as aggCost
from pgr_dijkstra('select smid as id,source,target,cost from line',78015,61218,false) a1
         join line b2 on a1.node=b2.SmId order by a1.seq;

78015,61218 为起点线的ID与终点线的ID；

卡住我太久了，求解决办法。

Answer 1

用pgr_withPoints函数来计算。

用Postgis算最短路径（在任意位置选择起点终点）_点到点最短距离 postgis-CSDN博客 文章浏览阅读4.3k次，点赞4次，收藏31次。1.前言阅读本文需要知道什么是shapfile，什么是路径分析，什么是GIS。相比Arcgis的路径分析功能，本文介绍的方法稍微复杂，需要注意的细节更多，但却是完全免费的。PostGis+QGIS+Geoserver开源Gis三剑客用起来真的很舒服。2.软件环境Postgresql+Postgis+pgRouting+QGIS。2.数据准备1.将道路Shp数据导入Postgis打开P..._点到点最短距离 postgis https://blog.csdn.net/qq_40482342/article/details/104019568