CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;

-----------------------------------------
--以起始点坐标为参数进行最短路径分析
--以route表作为实例-- route 为表名
-----------------------------------------
ALTER TABLE route ADD COLUMN source integer;--起点 
ALTER TABLE route ADD COLUMN target integer;--终点
ALTER TABLE route ADD COLUMN length double precision;--增加路线长度字段（根据长度设置权重）
UPDATE route SET length = ST_Length(geom);--计算路线长度
select pgr_createTopology('route', 0.0001, 'geom', 'gid');--创建拓扑

--添加起始点坐标x,y字段
ALTER TABLE route ADD COLUMN x1 double precision;
ALTER TABLE route ADD COLUMN y1 double precision;
ALTER TABLE route ADD COLUMN x2 double precision;
ALTER TABLE route ADD COLUMN y2 double precision;
--计算起始点坐标
UPDATE route SET x1 =ST_x(ST_PointN(geom, 1));
UPDATE route SET y1 =ST_y(ST_PointN(geom, 1));
UPDATE route SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));
UPDATE route SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));

-----------------------------------------
--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 node 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, node, cost, source, target, 
                ST_Reverse(geom) AS flip_geom FROM ' ||
                        'pgr_astar(''SELECT gid as id, source::int, target::int, '
                                        || 'length::float AS cost,x1,y1,x2,y2 FROM '
                                        || quote_ident(tbl) || ''', '
                                        || source || ', ' || target 
                                        || ' ,false), '
                                || quote_ident(tbl) || ' WHERE edge = 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;
                node    := rec.node;
                cost    := rec.cost;
                geom    := rec.geom;
                RETURN NEXT;
        END LOOP;
        RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-----------------------------------------

--测试
SELECT gid, geom, node, cost, source, target, 
    ST_Reverse(geom) AS flip_geom FROM 
 pgr_astar('SELECT gid as id, source::int, target::int, length::float AS cost,x1,y1,x2,y2 FROM route',
						2, 17 ,false),route
                                 WHERE edge = gid ORDER BY seq

Sql语句
SELECT ST_MakeLine(route.geom) FROM (SELECT * FROM pgr_fromAtoB('route', %x1%, %y1%, %x2%, %y2%)ORDER BY seq) AS route
参数默认值 0
参数表达式 ^-?[\d.]+$
返回类型  lineString
坐标系    4326

<!doctype html>
<html lang="en">
  <head>
    <link rel="stylesheet" href="./ol.css" type="text/css">
    <style>
      .map {
        height: 800px;
        width: 1000px;
		border: 1px solid black;
      }
    </style>
    <script src="./ol.js" type="text/javascript"></script>
    <title>OpenLayers example</title>
  </head>
  <body>
	<div>
		<button id="clear">清除结果</button>
	</div>
	<div id="map" class="map"></div>
	<script type="text/javascript">
		var format = 'image/png';
		var bounds = [29.1337776184082, 19.6063480377197,
                    46.7637786865234, 35.0863494873047];//边界可在geoserver中获取
		var mousePositionControl = new ol.control.MousePosition({
			className: 'custom-mouse-position',
			target: document.getElementById('map'),
			coordinateFormat: ol.coordinate.createStringXY(5),
			undefinedHTML: ' '
		});
	  
		var params1 = {
            //LAYERS: 'shp:map',
			LAYERS: 'result01',//图层组名称
            FORMAT: 'image/png',
			VERSION: '1.1.1',  
            exceptions: 'application/vnd.ogc.se_inimage',
        };
		
		var baseMap = new ol.layer.Image({
            source: new ol.source.ImageWMS({
                    url: 'http://localhost:8080/geoserver/wms',
                    params: params1
                })
        });
	 
		var projection = new ol.proj.Projection({
			code: 'EPSG:4326',//坐标系
			units: 'degrees',
			global: true
		});
		var map = new ol.Map({
			controls: ol.control.defaults({
				attribution: false
			}).extend([mousePositionControl]),
			target: 'map',
			layers: [
				baseMap //路径视图
			],
			view: new ol.View({
				projection: projection
			})
		});

		map.getView().fit(bounds, map.getSize());
		
		
		
		var startPoint;
		var destPoint;
		var vectorLayer;
		var result;
		
		startPoint = new ol.Feature();
        destPoint = new ol.Feature();
		
		vectorLayer = new ol.layer.Vector({
			source: new ol.source.Vector({
			features: [startPoint, destPoint]
			}),
			style:new ol.style.Style({
				image:new ol.style.Icon(({
					size:[24,36],
					anchor:[0.5,0.75],
					anchorXUnits:'fraction',
					anchorYUnits:'fraction',
					src:'./marker.png'
				}))
			})
		});
		map.addLayer(vectorLayer);
		
		//清空路径规划结果
		var clearButton = document.getElementById('clear');
		clearButton.addEventListener('click', function(event) {
			// Reset the "start" and "destination" features.
			startPoint.setGeometry(null);
			destPoint.setGeometry(null);
			// Remove the result layer.
			map.removeLayer(result);
		});
		
		var params = {
			LAYERS: 'shp:shortRoute01',//路径规划函数图层
			FORMAT: 'image/png',
			VERSION: '1.1.1',  
            exceptions: 'application/vnd.ogc.se_inimage',
		};
		
		function clickMap(event)
		{
			if (startPoint.getGeometry() == null) {
				// First click.
				startPoint.setGeometry(new ol.geom.Point(event.coordinate));console.info(event.coordinate);
			} else if (destPoint.getGeometry() == null) {
				// Second click.
				destPoint.setGeometry(new ol.geom.Point(event.coordinate));
				// Transform the coordinates from the map projection (EPSG:3857)
				// to the server projection (EPSG:4326).
				var startCoord = (startPoint.getGeometry().getCoordinates());
				var destCoord = (destPoint.getGeometry().getCoordinates());
				var viewparams = [
				'x1:' + startCoord[0], 'y1:' + startCoord[1],
				'x2:' + destCoord[0], 'y2:' + destCoord[1]
				];
				params.viewparams = viewparams.join(';');
				result = new ol.layer.Image({
				source: new ol.source.ImageWMS({
					url:'http://localhost:8080/geoserver/shp/wms',  
					params: params
				})
				});
				map.addLayer(result);
			}
		}
		
		map.on('click', clickMap);
		
    </script>
  </body>
</html>