--让数据库支持PostGIS和pgRouting的函数和基础表（安装后第一次使用时执行，以后都不再执行）
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
--注：使用postgis shapefile mport/export manager上传shp时，在Option中勾选“generate simple geometries instead of multi geometries”，以生成单个geometry
-----------------------------------------
--以节点为参数进行最短路径分析
--以road表作为实例--
-----------------------------------------
ALTER TABLE road ADD COLUMN source integer;--起点
ALTER TABLE road ADD COLUMN target integer;--终点
ALTER TABLE road ADD COLUMN length double precision;--增加路线长度字段（根据长度设置权重）
UPDATE road SET length = ST_Length(geom);--计算路线长度
select pgr_createTopology('road', 0.0001, 'geom', 'gid');--创建拓扑
SELECT * FROM pgr_dijkstra('SELECT gid as id,
source::integer,target::integer,length::double precision as cost
FROM road',30, 60, false, false); --路径分析
SELECT st_astext(geom) FROM pgr_dijkstra('SELECT gid AS id,source::integer,
target::integer,length::double precision AS cost FROM road',30, 60, false, false) as di
join road pt on di.id2 = pt.gid;--查询所经过的所有点
SELECT seq, id1 AS node, id2 AS edge, cost,geom into dijkstra_res FROM pgr_dijkstra('
SELECT gid AS id,source::integer,target::integer,length::double precision AS cost FROM road',
30, 60, false, false) as di join road pt on di.id2 = pt.gid;--查询结果存储到新的表格
select * from dijkstra_res;--查询表格内容
-----------------------------------------
--以起始点坐标为参数进行最短路径分析
--以gyroad表作为实例--
-----------------------------------------
ALTER TABLE gyroad ADD COLUMN source integer;--起点
ALTER TABLE gyroad ADD COLUMN target integer;--终点
ALTER TABLE gyroad ADD COLUMN length double precision;--增加路线长度字段（根据长度设置权重）
UPDATE gyroad SET length = ST_Length(geom);--计算路线长度
select pgr_createTopology('gyroad', 0.0001, 'geom', 'gid');--创建拓扑
--添加起始点坐标x,y字段
ALTER TABLE gyroad ADD COLUMN x1 double precision;
ALTER TABLE gyroad ADD COLUMN y1 double precision;
ALTER TABLE gyroad ADD COLUMN x2 double precision;
ALTER TABLE gyroad ADD COLUMN y2 double precision;
--计算起始点坐标
UPDATE gyroad SET x1 =ST_x(ST_PointN(geom, 1));
UPDATE gyroad SET y1 =ST_y(ST_PointN(geom, 1));
UPDATE gyroad SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));
UPDATE gyroad SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));
SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_astar('SELECT gid AS id,
source::integer,target::integer,length::double precision AS cost,
x1, y1, x2, y2 FROM gyroad',30, 60, false,false);--A*算法路径查询
SELECT seq, id1 AS source, id2 AS target,cost FROM pgr_kdijkstraCost('SELECT gid AS id,
source::integer,target::integer,length::double precision AS cost FROM gyroad',30,
array[60,70,100], false, false);--查询从出发点到目的地的消耗
SELECT seq, id1 AS path, id2 AS edge, cost FROM pgr_kdijkstraPath('SELECT gid AS id,
source::integer,target::integer,length::double precision AS cost
FROM gyroad',30, array[60,100], false, false);--pgr_kdijkstraPath函数路径分析
-----------------------------------------
--DROP FUNCTION pgr_fromAtoB(varchar, double precision, double precision,
-- double precision, double precision);
--基于任意两点之间的最短路径分析
CREATE OR REPLACE FUNCTION pgr_fromAtoB(
IN tbl varchar,--数据库表名
IN x1 double precision,--起点x坐标
IN y1 double precision,--起点y坐标
IN x2 double precision,--终点x坐标
IN y2 double precision,--终点y坐标
OUT seq integer,--道路序号
OUT gid integer,
OUT name text,--道路名
OUT heading double precision,
OUT cost double precision,--消耗
OUT geom geometry--道路几何集合
)
RETURNS SETOF record AS
$BODY$
DECLARE
sql text;
rec record;
source integer;
target integer;
point integer;
BEGIN
-- 查询距离出发点最近的道路节点
EXECUTE 'SELECT id::integer FROM '|| quote_ident(tbl) ||'_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;
source := rec.id;
-- 查询距离目的地最近的道路节点
EXECUTE 'SELECT id::integer FROM '|| quote_ident(tbl) ||'_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec;
target := rec.id;
-- 最短路径查询
seq := 0;
sql := 'SELECT gid, geom, name, cost, source, target,
ST_Reverse(geom) AS flip_geom FROM ' ||
'pgr_bdAstar(''SELECT gid as id, source::int, target::int, '
|| 'length::float AS cost,x1,y1,x2,y2 FROM '
|| quote_ident(tbl) || ''', '
|| source || ', ' || target
|| ' ,false, false), '
|| quote_ident(tbl) || ' WHERE id2 = gid ORDER BY seq';
-- Remember start point
point := source;
FOR rec IN EXECUTE sql
LOOP
-- Flip geometry (if required)
IF ( point != rec.source ) THEN
rec.geom := rec.flip_geom;
point := rec.source;
ELSE
point := rec.target;
END IF;
-- Calculate heading (simplified)
EXECUTE 'SELECT degrees( ST_Azimuth(
ST_StartPoint(''' || rec.geom::text || '''),
ST_EndPoint(''' || rec.geom::text || ''') ) )'
INTO heading;
-- Return record
seq := seq + 1;
gid := rec.gid;
name := rec.name;
cost := rec.cost;
geom := rec.geom;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-----------------------------------------
--测试
SELECT st_astext(ST_MakeLine(route.geom)) FROM (SELECT seq,gid,name,heading,cost,geom FROM pgr_fromAtoB('gyroad', 106.535, 26.905, 106.955, 27.040)ORDER BY seq) AS route
--Openlayers测试
--http://localhost:6060/geoserver/PostGIS/wms?service=WMS&version=1.1.0&request=GetMap&layers=PostGIS:shortgyroad&styles=&bbox=104,24,108,28&width=330&height=768&srs=EPSG:4326&format=application/openlayers&viewparams=x1:106.565;y1:26.915;x2:106.925;y2:28.040
