postgis安装_PostGIS 拓扑笔记

一、ShapeFiles 导入 PostgreSQL

PostGIS PostGIS Bundle 3 for PostgreSQL x64 12 Shapefile and DBF Loader Exporter

Import Options:

*notice: shapefiles不要放在中文路径里

SRID: nyc_roads 2908

二、操作PostgreSQL

*notice: 确保已经安装了postgis、pgrouting，可以使用SELECT pgr_version()查看版本，如果版本显示正常，可以执行后续步骤，否则，执行如下语句：

CREATE EXTENSION postgis;

CREATE EXTENSION pgRouting;

2.1 获取导航路径

//添加起止点，存储线段的首尾编号

ALTER TABLE nyc_roads ADD COLUMN source integer;

ALTER TABLE nyc_roads ADD COLUMN target integer;

//添加道路权重值

ALTER TABLE nyc_roads ADD COLUMN length double precision;

//创建拓扑，生成线段的首尾编号

SELECT pgr_createTopology('nyc_roads', 0.00001, 'geom', 'gid');

//表名、容差、线段列名、gid
//第一步默认创建起止点的索引，如果没有创建，可以执行以下sql创建

CREATE INDEX source_idx ON nyc_roads ("source");

CREATE INDEX target_idx ON nyc_roads ("target");

//为权重赋值，这里将路段的长度赋值给权重值

UPDATE nyc_roads SET length = st_length(geom);

//回程成本设置，则可支持回程(可选)

ALTER TABLE nyc_roads ADD COLUMN reverse_cost double precision;

UPDATE nyc_roads SET reverse_cost = length;

//生成最短路径

SELECT seq, node, edge, cost, geom into dijkstra_res FROM pgr_dijkstra('SELECT gid AS id, source::integer, target::integer, length::double precision AS cost FROM nyc_roads', 2, 26, false) as di join nyc_roads pt on di.edge = pt.gid;

QGIS验证

2.2 创建topo，方法二

SELECT topology.CreateTopology('roads_topo', 2154);SELECT topology.AddTopoGeometryColumn('roads_topo', 'public', 'roads', 'topo_geom', 'LINESTRING');

http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html