1.30

13693261870

2023-01-30 d755ae47b01c107960500127c9b14f1473d13b03

1.30

 data/fn.sql

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,229 @@
----------------------------------------------------------------------------------------------------- 01.GUID生成函数
create or replace function new_guid()
returns "pg_catalog"."varchar" as $body$
declare
  v_seed_value varchar(32);
begin
  select md5(inet_client_addr()::varchar || timeofday() || inet_server_addr()::varchar || to_hex(inet_client_port()))
    into v_seed_value;

  return (substr(v_seed_value,1,8) || '-' ||
          substr(v_seed_value,9,4) || '-' ||
          substr(v_seed_value,13,4) || '-' ||
          substr(v_seed_value,17,4) || '-' ||
          substr(v_seed_value,21,12));
end;
$body$ language 'plpgsql' volatile security definer;

select new_guid();
----------------------------------------------------------------------------------------------------- 02.递归查询函数
-- drop function rec_query_dep(id integer, tab varchar);
create or replace function fn_rec_query(id integer, tab varchar)
returns varchar as $$
  declare
    str varchar = '';
    rec varchar = '';
  begin
    if (id is null or tab is null) then
      return '';
    end if;
	
    for rec in execute 'with recursive rs as(' || 
      'select id,pid,name from lf.sys_' || tab || ' where id=' || id ||
      ' union select a.id,a.pid,a.name from lf.sys_' || tab || ' a, rs b where a.id=b.pid '||
      ') select name from rs order by id'
    loop
      str = str || '\' || rec;
    end loop;
	
    if (char_length(str) > 1) then
      str = substring(str, 2);
    end if;
   
    return str;
  end;
$$ language plpgsql;

select fn_rec_query(1, 'dep');
----------------------------------------------------------------------------------------------------- 03.查询用户名
-- execute format('select uname from lf.sys_user where id = %s', id) into str;
-- drop function fn_uname(id integer);
create or replace function fn_uname(id integer)
returns varchar as $$
  declare
    str varchar;
  begin
      if (id is null) then
        return null;
    end if;
  
    execute 'select uname from lf.sys_user where id = ' || id into str;
	
    return str;
  end;
$$ language plpgsql;

select fn_uname(1);
----------------------------------------------------------------------------------------------------- 04.查询版本名
-- drop function fn_ver(id integer);
create or replace function fn_ver(id integer)
returns varchar as $$
  declare
    str varchar;
  begin
      if (id is null) then
        return null;
    end if;
  
    execute 'select name from lf.sys_ver where id = ' || id into str;
	
    return str;
  end;
$$ language plpgsql;

select fn_ver(0);
----------------------------------------------------------------------------------------------------- 05.递归查询ID数组
-- drop function fn_rec_array(id integer, tab varchar);
create or replace function fn_rec_array(id integer, tab varchar)
returns integer[] as $$
  declare
    ids integer[];
    sid integer;
  begin
    for sid in execute 'with recursive rs as(' || 
      'select id,pid from lf.sys_' || tab || ' where id=' || id ||
      ' union select a.id,a.pid from lf.sys_' || tab || ' a, rs b where a.pid=b.id '||
      ') select id from rs order by id'
    loop
      select array_append(ids, sid) into ids;
    end loop;
	
    return ids;
  end;
$$ language plpgsql;

select fn_rec_array(1, 'dep');
select fn_rec_array(10, 'dir');
----------------------------------------------------------------------------------------------------- 06.获取实体名
/*create or replace function fn_get_entity(tab varchar)
returns varchar as $$
  declare
    str varchar;
    rs varchar = '';
  begin
    foreach str in array (select string_to_array(tab, '_')) loop
      if (length(rs) = 0 or length(str) = 1) then
        rs = rs || str;
      else
        rs = rs || initcap(str);
      end if;
    end loop;
	
    return rs;
  end;
$$ language plpgsql;*/

create or replace function fn_get_entity(tab varchar)
returns varchar as $$
  declare
  begin
    if (tab is null) then
      return '';
    end if;
	
    return replace(tab, '_', '');
  end;
$$ language plpgsql;

select fn_get_entity('dlg_25w_boua_s');
----------------------------------------------------------------------------------------------------- 07.FME日志触发器 *
create or replace function fn_meta_insert() returns trigger as $$
  begin
    update lf.sys_meta set tab = new.pg_ns || '.' || new.tcdm, layer = new.tcmc, rows = new.count where eventid = new.parentid;
	
    return new;
  end;
$$ language plpgsql;

create or replace trigger fme_log_trigger after insert on lf.sys_fme_log
for each row execute procedure fn_meta_insert();

select * from lf.sys_meta;
----------------------------------------------------------------------------------------------------- 08.路径分析
-- åˆ é™¤å·²å­˜åœ¨çš„函数
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 || ')'',4490) 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 || ')'',4490) limit 1' into rec;
target := rec.id;

-- æœ€çŸ­è·¯å¾„查询
seq := 0;
sql := 'select gid, geom, node as name, cost, source, target, st_reverse(geom) as flip_geom from '
    || 'pgr_dijkstra(''select gid as id,source::integer,target::integer,'
    || 'length::float as cost from '
    || quote_ident(tbl) || ''', '
    || source || ', ' || target
    || ' ,false) as di, '
    || quote_ident(tbl) || ' where di.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;
        name := rec.name;
        cost := rec.cost;
        geom := rec.geom;
        return next;
    end loop;
return;
end;
$body$ language 'plpgsql' volatile strict;

select ST_astext(ST_Union(geom)) as route from pgr_fromAtoB('lrdl'::text, 116.78999, 39.9468, 116.80458, 39.94758);
select st_astext(geom) route from pgr_fromAtoB('lrdl', 116.78999, 39.9468, 116.80458, 39.94758);
----------------------------------------------------------------------------------------------------- 

 data/·¾¶¹æ»®_PG.txt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,137 @@
--1）创建数据表：
--添加起点id

ALTER TABLE public.lrdl ADD COLUMN source integer;

--添加终点id

ALTER TABLE public.lrdl ADD COLUMN target integer;

--添加道路权重值

ALTER TABLE public.lrdl ADD COLUMN length double precision;

--为sampledata表创建拓扑布局，即为source和target字段赋值

SELECT pgr_createTopology('public.lrdl',0.0001, 'geom', 'gid');

--为source和target字段创建索引

CREATE INDEX source_idx ON public.lrdl("source");

CREATE INDEX target_idx ON public.lrdl("target");

--为length赋值

update public.lrdl set length =st_length(geom);

--为road_xblk表添加reverse_cost字段并用length的值赋值

ALTER TABLE public.lrdl ADD COLUMN reverse_cost double precision;

UPDATE public.lrdl SET reverse_cost =length;



--2）创建最短路径函数：
--删除已存在的函数
DROP FUNCTION pgr_fromAtoB(tbl varchar,startx float, starty float,endx float,endy float);
 
--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 || ')'',4490) 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 || ')'',4490) LIMIT 1' INTO rec;
                    target := rec.id;

-- æœ€çŸ­è·¯å¾„查询
seq := 0;
sql := 'SELECT gid, geom, node as name, cost, source, target,
                                ST_Reverse(geom) AS flip_geom FROM ' ||
                           'pgr_dijkstra(''SELECT gid as id,
                                source::integer,target::integer,'
                               || 'length::float AS cost FROM '
                               || quote_ident(tbl) || ''', '
                               || source || ', ' || target
                               || ' ,false) as di, '
                               || quote_ident(tbl) || ' WHERE di.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;
                            name    := rec.name;
                            cost    := rec.cost;
                            geom    := rec.geom;
                            RETURN NEXT;
                        END LOOP;
                    RETURN;
                END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;

--3) åˆ›å»ºèŠ‚点表：
select pgr_createTopology('lrdl',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid < 50000')


--4）测试：
--随便选两个点测试一下，OK，已经返回了路径的线段序列。

select * from  pgr_fromatob('lrdl',116.78999,39.9468,116.80458,39.94758);

--因为最终我们需要将最短路径传到桌面端显示，为了方便，使用ST_Union将这些线段合并成一个完整的线路。使用方法如下
select ST_Union(geom) as route from pgr_fromAtoB('lrdl'::text,116.78999,39.9468,116.80458,39.94758) ;

--合并的路径我们看不出来是什么，不过将他转成wkt就能理解了：

select ST_astext(ST_Union(geom)) as route from pgr_fromAtoB('lrdl'::text,116.78999,39.9468,116.80458,39.94758) ;

 src/main/java/com/lf/server/controller/show/ComprehensiveController.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,45 @@
package com.lf.server.controller.show;

import com.lf.server.annotation.SysLog;
import com.lf.server.controller.all.BaseController;
import com.lf.server.entity.all.ResponseMsg;
import com.lf.server.service.all.BaseQueryService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * ç»¼åˆå±•ç¤º
 * @author WWW
 */
@Api(tags = "综合展示\\综合展示")
@RestController
@RequestMapping("/comprehensive")
public class ComprehensiveController extends BaseController {
    @Autowired
    BaseQueryService baseQueryService;

    @SysLog()
    @ApiOperation(value = "查询路网")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "x1", value = "X1", dataType = "Double", paramType = "query", example = "116.78999"),
            @ApiImplicitParam(name = "y1", value = "Y1", dataType = "Double", paramType = "query", example = "39.9468"),
            @ApiImplicitParam(name = "x2", value = "X2", dataType = "Double", paramType = "query", example = "116.80458"),
            @ApiImplicitParam(name = "y2", value = "Y2", dataType = "Double", paramType = "query", example = "39.94758")
    })
    @GetMapping(value = "/selectRoute")
    public ResponseMsg<Object> selectRoute(double x1, double y1, double x2, double y2) {
        try {
            String route = baseQueryService.selectRoute(x1, y1, x2, y2);

            return success(route);
        } catch (Exception ex) {
            return fail(ex.getMessage(), null);
        }
    }
}

 src/main/java/com/lf/server/mapper/all/BaseQueryMapper.java

@@ -74,4 +74,15 @@
     * @return å®žä½“类集合
     */
    public List<DomainEntity> selectDomains(String ns, String tab);

    /**
     * æŸ¥è¯¢è·¯ç½‘
     *
     * @param x1 X1
     * @param y1 Y1
     * @param x2 X2
     * @param y2 Y2
     * @return å¤šçº¿
     */
    public String selectRoute(double x1, double y1, double x2, double y2);
}

 src/main/java/com/lf/server/service/all/BaseQueryService.java

@@ -96,7 +96,7 @@
     * @param val     å€¼
     */
    private void addWrapper(QueryWrapper wrapper, String field, String express, Object val) {
        if (StringHelper.isDate(val.toString())){
        if (StringHelper.isDate(val.toString())) {
            wrapper.apply(String.format("%s %s '%s'", field, express, val));
            return;
        }
@@ -250,4 +250,9 @@
    public List<DomainEntity> selectDomains(String ns, String tab) {
        return baseQueryMapper.selectDomains(ns, tab);
    }

    @Override
    public String selectRoute(double x1, double y1, double x2, double y2) {
        return baseQueryMapper.selectRoute(x1, y1, x2, y2);
    }
}

 src/main/resources/mapper/all/BaseQueryMapper.xml

@@ -53,4 +53,8 @@
      select a.* from lf.sys_domain a inner join lf.sys_dict b on a.dom_name = b.domain_na
      where b.ns = #{ns} and b.tab = #{tab} and b.domain_na is not null
    </select>

    <select id="selectRoute" resultType="java.lang.String">
        select ST_astext(ST_Union(geom)) as route from pgr_fromAtoB('lrdl'::text, #{x1}, #{y1}, #{x2}, #{y2});
    </select>
</mapper>