1

13693261870

2023-03-06 312a33472bda424df500abd004aa1dac43d7502d

1

 data/db_cx.sql

@@ -1,5 +1,48 @@
st_length( st_geographyfromtext( st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) AS acrossLength;
---------------------------------------------------------------------------------------------- 00.创建空间扩展
---------------------------------------------------------------------------------------------- -1.SQL测试
update bs.s_survey_information set workname='汉江穿越' where gid=1;
select * from bs.s_explorationpoint where upper(exppointid) like '%XK0%';

select * from bd.b_pac_geologic_hazard where gid=2;
select * from bd.b_pac_regional_geology where gid=2;
select photono from bs.s_explorationpoint;
select * from lf.sys_attach where tab='lf.sys_style';
select * from lf.sys_attach order by tab,tab_guid;




















---------------------------------------------------------------------------------------------- 00.SQL查询
select modular1,modular2 from lf.sys_operate where modular1 not in ('综合展示','数据质检','数据管理','数据交换','服务管理','系统对接','运维管理');
delete from lf.sys_operate where modular1 not in ('综合展示','数据质检','数据管理','数据交换','服务管理','系统对接','运维管理');
select id,cn_name,elev from lf.sys_layer where cn_name in ('火车站','地下管线','中卫站','庆阳站','地层示例');

select * from lf.sys_user where uid='FME';
select * from lf.sys_token where token='c36e4f94-dfde-401e-9967-2c4a449f1300';
update lf.sys_token set type=1,create_user=61,duration=5256000,create_time='2023-03-03 19:00:00',expire='2033-03-03 19:00:00'
where token='c36e4f94-dfde-401e-9967-2c4a449f1300';

select gid, pipename, segname, ST_AsText(ST_LineMerge(geom)) "wkt"
from bs.m_pipesegment
where pipename is not null and not ST_IsEmpty(geom)
order by pipename, segname;
---------------------------------------------------------------------------------------------- 01.创建空间扩展
create extension postgis;
create extension pgrouting;
create extension postgis_raster;
@@ -27,7 +70,7 @@
select replace('aabbcc', 'bc', 'xy');         -- 替换
select left('abcde', 2);                      -- 左截取
select right('abcde', 2);                     -- 右截取
---------------------------------------------------------------------------------------------- 01.查询连接数
---------------------------------------------------------------------------------------------- 02.查询连接数
show max_connections;
select count(1) from pg_stat_activity;

@@ -50,242 +93,7 @@
select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename from pg_constraint inner join pg_class 
on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_class.relname = 'sys_user' and pg_constraint.contype='p';
---------------------------------------------------------------------------------------------- 02.递归查询
with recursive rs as(
  select * from lf.sys_menu where cn_name='管道基础大数据平台'
union
  select a.* from lf.sys_menu a, rs b where a.pid=b.id
) select * FROM rs order by order_num; -- 查询菜单

with recursive rs as (
  select * from lf.sys_dep where name='中国石油天然气管道工程有限公司'
union
  select a.* from lf.sys_dep a, rs b where a.pid=b.id
) select * FROM rs order by order_num; -- 查询单位

with recursive rs as(
  select * from lf.sys_dir where name='中俄东线管道工程南段'
union
  select a.* from lf.sys_dir a, rs b where a.pid=b.id
) select * FROM rs order by order_num; -- 数据目录

with recursive rs as(select id, pid from lf.sys_dir where id in (2,5,7,9,12)
union select a.id,a.pid from lf.sys_dir a, rs b where a.pid = b.id)
select distinct id from rs order by id; -- 数据目录
---------------------------------------------------------------------------------------------- 03.查询表结构
select * from pg_tables; select * from pg_class order by relnamespace;
select relnamespace,relkind,relname from pg_class where relnamespace in (select oid from pg_namespace) and relkind='r' order by 1,2;

select c.relname as "表名", cast(obj_description(c.oid) as varchar) as "表名描述",a.attnum as "序号", a.attname as "列名", 
  concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\(.*\)')) as "字段类型",d.description as "备注"
from pg_attribute a left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_class c on a.attrelid = c.oid left join pg_type t on a.atttypid = t.oid
where a.attnum >= 0 and c.relname like 'sys_%' and obj_description(c.oid) is not null --and relnamespace=20582
order by c.relname desc, a.attnum asc;
---------------------------------------------------------------------------------------------- 04.索引
alter table lf.sys_role_user add constraint idx_unique_role_user unique (roleid, userid); -- 联合唯一索引
insert into lf.sys_role_user (roleid,userid) values (1,1); -- 测试
select * from lf.sys_role_user;

alter table lf.sys_menu_auth add constraint idx_unique_menu_auth unique (menuid, authid); -- 联合唯一索引
insert into lf.sys_menu_auth (menuid, authid) values (1,1); -- 测试
select * from lf.sys_menu_auth;

alter table lf.sys_role_res add constraint idx_unique_role_res unique (roleid,resid); -- 联合唯一索引
insert into lf.sys_role_res (roleid,resid) values (1,1);
select * from lf.sys_role_res;

alter table lf.sys_role_menu_auth add constraint idx_unique_role_menu_auth unique (roleid,menu_auth_id); -- 联合唯一索引
insert into lf.sys_role_menu_auth (roleid,menu_auth_id) values (1,1);
select * from lf.sys_role_menu_auth;
---------------------------------------------------------------------------------------------- 05.联合查询
select a.*,fn_uname(a.create_user) createUserName,fn_uname(a.update_user) updateUserName from lf.sys_blacklist a order by a.id;
select * from lf.sys_user a where uname like '%' and a.depid=ANY(fn_rec_array(1,'dep'));
select * from lf.sys_user a left join lf.sys_role_user b on a.id=b.userid;

select a.* from lf.sys_user a where not exists (select b.id from lf.sys_role_user b where b.userid = a.id and b.roleid = 1)
and uname like '%室%' and a.depid = ANY(fn_rec_array(1, 'dep'))
order by a.id limit 10 offset 0;

select a.*,fn_rec_query(a.depid, 'dep') depName,fn_rec_query(a.dirid, 'dir') dirName from lf.sys_style a;
------------------------------------------------------------
select * from lf.sys_auth a left join lf.sys_menu;
select a.* from lf.sys_auth a where not exists (select b.id from lf.sys_menu_auth b where b.authid = a.id and b.menuid = 1);

select a.* from lf.sys_res a where not exists (select b.id from lf.sys_role_res b where b.resid = a.id and b.roleid = 1);

select a.*,c.name from lf.sys_menu_auth a inner join lf.sys_auth c on a.authid = c.id
where not exists (select b.id from lf.sys_role_menu_auth b where b.menu_auth_id = a.id and b.roleid = 1) and a.menuid = 1 order by c.id;

select a.*,b.menuid,c.name from lf.sys_role_menu_auth a inner join lf.sys_menu_auth b on a.menu_auth_id = b.id
inner join lf.sys_auth c on b.authid = c.id where a.roleid = 1 and b.menuid = 1 order by c.id;
----------------------------------------------------------------------------------------------
update lf.sys_operate set modular1='运维管理',modular2='菜单管理' where position('/Menu/select' in url)>0 and (modular1 is null or modular2 is null);
update lf.sys_operate set modular1='运维管理',modular2='用户管理' where position('/user/select' in url)>0 and (modular1 is null or modular2 is null);
update lf.sys_operate set modular1='运维管理',modular2='资源管理' where position('/res/select' in url)>0 and (modular1 is null or modular2 is null);

delete from lf.sys_operate where modular1 is null or modular2 is null;
---------------------------------------------------------------------------------------------- 06.查询授权
select gid,gb,name,st_astext(a.geom) from bd.dlg_agnp a;

-- 根据用户Uid查询资源
select distinct d.id,d.name,d.server from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_res c on b.roleid = c.roleid
inner join lf.sys_res d on c.resid = d.id
where a.uid = 'admin';

-- 根据用户Uid查询菜单
select distinct e.id,e.pid,e.cn_name,e.en_name,e.url,e.type,e.icon,e.level,e.order_num,e.is_show
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
where a.uid = 'admin';

-- 根据用户Uid查询权限
select distinct f.id,e.cn_name,f.name,e.perms,f.tag
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
inner join lf.sys_auth f on d.authid = f.id
where a.uid = 'admin';

-- 根据用户Uid查询权限2
select distinct e.perms || f.tag as "perms"
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
inner join lf.sys_auth f on d.authid = f.id
where a.uid = 'admin';
---------------------------------------------------------------------------------------------- 07.数据统计 
select modular2,count(*) from lf.sys_operate group by modular2;

select to_char(optime,'yyyy-mm-dd') as optime,count(*) from lf.sys_login where optime between (select optime - interval '30 day')
and optime group by to_char(optime, 'yyyy-mm-dd') order by to_char(optime, 'yyyy-mm-dd') asc;

select a.*,coalesce(a.native,'') from lf.sys_user a;
select ST_PointFromText('POINT(95.80461853400004 34.13862467200005)');
select ST_LineFromText('LINESTRING(04.98985101830993 37.43840773692756,104.99318913447104 37.43883729720358)', 4326);
select ST_PolygonFromText('POLYGON((104.9907822932683 37.43532941961706,104.99088987736599 37.43532941961706,104.9908670336867 37.4349030213574,104.99078327712658 37.4349030213574,104.9907822932683 37.43532941961706))',4326);
---------------------------------------------------------------------------------------------- 
-- 114ms，180/3248
select count(1) from bd.dlg_agnp a where ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4326))

-- 1101：gid >= 50 and name like '县' and objectid < 5000.0 and pac = '360430'
select count(*) from bd.dlg_agnp where gid >= 50 and name like '%县%' and objectid < 5000 --and pac = '360430'
and ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4326))

select a.* from lf.sys_domain a inner join lf.sys_dict b on a.dom_name = b.domain_na
where b.ns = 'bd' and b.tab = 'dlg_25w_aanp' and b.domain_na is not null;

select * from lf.sys_dict where ns = 'bd' and tab = 'dlg_25w_aanp' and domain_na is not null;
select * from lf.sys_domain where dom_name='dlg25gbcode';
select * from bd.dlg_25w_aanp limit 10;

select * from bd.dlg_25w_agnp
where ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 0))
limit 10;
select st_srid(geom) from bd.dlg_25w_agnp limit 1;
select * from bd.dlg_25w_agnp where class != 'BB' limit 1;

select a.* from lf.sys_domain a where exists (select * from lf.sys_dict b where b.ns = 'bd' and b.tab = 'dlg_25w_hyda') and a.dom_code like '210%' order by a.id
select * from lf.sys_download a where create_user = 1 and type = 2;
---------------------------------------------------------------------------------------------- 08.查询空间数据
select to_char(create_time, 'yyyy-mm-dd hh24:mi:ss') from lf.sys_meta;
select to_timestamp('2018-08-15 12:10:10', 'yyyy-MM-dd hh24:mi:ss');
select a.*,fn_uname(a.create_user) uname from lf.sys_token a
select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tabletype from lf.sys_dict order by tab;

-- 3248：POINT(95.8046 34.1386)
select * from bd.dlg_agnp;
select count(*) from bd.dlg_agnp;
delete from bd.dlg_agnp;
select ST_AsText(geom) from bd.dlg_agnp limit 1;

-- 7348：MULTILINESTRING((113.6007 6.9434,113.6015 6.9444,113.6007 6.9434))
select * from bd.dlg_25w_boul;
select count(*) from bd.dlg_25w_boul;
delete from bd.dlg_25w_boul;
select ST_AsText(geom) from bd.dlg_25w_boul limit 1;

-- 156847：MULTIPOLYGON(((108.6790 19.0108,108.6791 19.0104,108.6796 19.0104,108.6790 19.0108)))
select * from bd.dlg_25w_resa;
select count(*) from bd.dlg_25w_resa;
delete from bd.dlg_25w_resa;
select ST_AsText(geom) from bd.dlg_25w_resa limit 1;

-- MultiLineString MultiPolygon Point
select GeometryType(geom) from bd.dlg_25w_boul where gid=20;
select st_srid(geom) from bd.dlg_25w_boul where gid=20 limit 1;
select ST_AsText(geom) from bd.dlg_agnp where gid=20;

select * from lf.sys_dict where ns='bd' and tab='dlg_25w_boul' and field='geom';
select * from lf.sys_meta; -- delete from lf.sys_meta where id<113;
select * from lf.sys_meta_file; -- delete from lf.sys_meta_file where metaid<113;

select * from lf.sys_meta_file a where metaid in (113,120,121,122);
alter sequence bd.dlg_agnp_gid_seq restart with 4000;
select max(gid) from  bd.dlg_agnp;
---------------------------------------------------------------------------------------------- 09.管线分析
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_hydl  limit 1;
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_lrdl  limit 1;
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_lrrl  limit 1;
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_hyda  limit 1;
select ST_SRID(geom), ST_GeometryType(geom) from bs.m_pipesegment limit 1;

SELECT a.name as acrossName, b.remarks, b.pipename as pipeName,
       st_length( st_geographyfromtext( st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) AS acrossLength,
       st_astext(b.geom) as wkt
FROM bd.dlg_25w_hyda AS a, (SELECT * FROM bs.m_pipesegment WHERE segname = 'DD' ) AS b WHERE ST_Intersects(a.geom, b.geom);

select row_number() over() as gid, a.name as acrossName, b.segname as segName, b.remarks, b.pipename as pipeName,
  cast( st_length( st_geographyfromtext( st_astext( st_intersection(ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) as decimal(12, 3) ) AS acrossLength,
  st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) as wkt
from bd.dlg_25w_hyda as a, (select segname, remarks, pipename, geom from bs.m_pipesegment where gid = 24) as b
where ST_Intersects(a.geom, b.geom);

select *, st_astext(geom) as wkt from bs.m_pipesegment;
select pipename,segname from bs.m_pipesegment where gid = 24;
---------------------------------------------------------------------------------------------- 10.查询位置
select '国' "key", cname "value" from bs.th_globe_country where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
union all
select '省' "key", cname "value" from bs.th_province_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
union all
select '市' "key", cname "value" from bs.th_district_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
union all
select '县' "key", cname "value" from bs.th_county_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom);
---------------------------------------------------------------------------------------------- 11.根据用户ID查询权限
select * from lf.sys_role a inner join lf.sys_role_user b on a.id=b.roleid inner join lf.sys_user c on b.userid=c.id
where c.uid = 'kc';

select distinct e.id,e.pid,e.cn_name,e.en_name,e.url,e.perms,e.type,e.css,e.icon,e.level,e.order_num,e.is_show
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
where a.uid = 'kc'
order by e.order_num;

with recursive rs as(
    select a.* from lf.sys_menu a where a.id = 64
union
    select a.* from lf.sys_menu a, rs b where a.pid = b.id
)
select * FROM rs where rs.id in (
    select *
      from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
      inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
      inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
      inner join lf.sys_menu e on d.menuid = e.id
      inner join lf.sys_auth f on d.authid = f.id
      where a.uid = 'admin')
order by order_num;
---------------------------------------------------------------------------------------------- 12.查询
---------------------------------------------------------------------------------------------- 03.查询
select depid from bd.dlg_25w_boul where depid > 0 and depid != ALL(fn_rec_array(38, 'dep'))
and ST_Intersects(geom, ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4490))
group by depid;
@@ -301,63 +109,4 @@
select fn_rec_query(depid,'dep') depName,fn_rec_query(dirid,'dir') dirName,fn_ver(verid) verName,fn_uname(createuser) createName,fn_uname(updateuser) updateName from bd.dlg_agnp limit 5;

select ns,tab from lf.sys_dict a where not exists (select id from lf.sys_dict b where b.ns=a.ns and b.tab=a.tab and b.field='gid') group by ns,tab; -- 查询字典中不存在gid的表
---------------------------------------------------------------------------------------------- SQL测试
select (select string_agg(code, ',') from lf.sys_dir where name = a.name) "key", name "value"
from lf.sys_dir a
where name in ('基础测绘', '基础地灾', '基础勘察', '合规数据', '管理数据', '测量（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
group by name
order by key;

-- 按照大类统计
select name,
 (select count(b.id) from lf.sys_meta b where dircode similar to '(' || (select string_agg(code, '|') from lf.sys_dir c where c.name = a.name) || ')%') "count",
 (select coalesce(sum(b.sizes), 0) from lf.sys_meta b where dircode similar to '(' || (select string_agg(code, '|') from lf.sys_dir c where c.name = a.name) || ')%') "sizes"
from lf.sys_dir a
where code like '01%' and name in ('基础测绘', '基础地灾', '基础勘察', '合规数据', '管理数据', '测量（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
group by name
order by name;

select name,
 (select count(b.id) from lf.sys_meta b where dircode similar to '(' || (select string_agg(code, '|') from lf.sys_dir c where c.name = a.name) || ')%') "count",
 (select coalesce(sum(b.sizes), 0) from lf.sys_meta b where dircode similar to '(' || (select string_agg(code, '|') from lf.sys_dir c where c.name = a.name) || ')%') "sizes"
from lf.sys_dir a
where code like '01%' and name in ('基础测绘', '基础地灾', '基础勘察', '合规数据', '管理数据', '测量（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
group by name
order by name;

select modular1,modular2 from lf.sys_operate where modular1 not in ('综合展示','数据质检','数据管理','数据交换','服务管理','系统对接','运维管理');
delete from lf.sys_operate where modular1 not in ('综合展示','数据质检','数据管理','数据交换','服务管理','系统对接','运维管理');
select id,cn_name,elev from lf.sys_layer where cn_name in ('火车站','地下管线','中卫站','庆阳站','地层示例');

select gid, pipename, segname, ST_AsText(ST_LineMerge(geom)) "wkt"
from bs.m_pipesegment
where pipename is not null and not ST_IsEmpty(geom)
order by pipename, segname;

select * from bs.s_surveyworksite;
select * from bs.s_survey_information;
update bs.s_survey_information set workname='汉江穿越' where gid=1;
select * from bs.s_explorationpoint where upper(exppointid) like '%XK0%';

select * from lf.sys_user where uid='FME';
select * from lf.sys_token where token='c36e4f94-dfde-401e-9967-2c4a449f1300';
update lf.sys_token set type=1,create_user=61,duration=5256000,create_time='2023-03-03 19:00:00',expire='2033-03-03 19:00:00'
where token='c36e4f94-dfde-401e-9967-2c4a449f1300';

select * from bd.b_pac_geologic_hazard where gid=2;
select * from bd.b_pac_regional_geology where gid=2;















----------------------------------------------------------------------------------------------

 data/update.sql

@@ -274,6 +274,13 @@
order by name;
-- select * from lf.sys_meta where dircode similar to fn_dir_similar('基础测绘');

-- 查询分类键
select (select string_agg(code, ',') from lf.sys_dir where name = a.name) "key", name "value"
from lf.sys_dir a
where name in ('基础地灾', '基础勘察', '合规数据', '管理数据', '测量（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
group by name
order by key;

-- 单个项目的下载个数、下载次数和数据总量
select count(*) "下载个数", sum(c.dcount) "下载数量", round(sum(c.dcount * c.sizes)::numeric, 3) "数量总量(MB)"
from lf.sys_meta a inner join lf.sys_meta_down b on a.id = b.metaid
@@ -306,58 +313,241 @@
from lf.sys_dir d
where pid = 0
order by d.code;
--------------------------------------------------------- 
select (select string_agg(code, ',') from lf.sys_dir where name = a.name) "key", name "value"
from lf.sys_dir a
where name in ('基础地灾', '基础勘察', '合规数据', '管理数据', '测量（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
group by name
order by key;
--------------------------------------------------------- 02.递归查询
with recursive rs as(
  select * from lf.sys_menu where cn_name='管道基础大数据平台'
union
  select a.* from lf.sys_menu a, rs b where a.pid=b.id
) select * FROM rs order by order_num; -- 查询菜单

select * from lf.sys_meta order by id desc;
select * from lf.sys_download order by id desc;
select * from lf.sys_downlog order by id desc;
select * from lf.sys_meta_down order by id desc;
select downid,count(*),max(create_time) from lf.sys_downlog group by downid having count(*)>1;
--delete from lf.sys_download where id=324;
--delete from lf.sys_meta_down where downid=324;
select * from lf.sys_apply
with recursive rs as (
  select * from lf.sys_dep where name='中国石油天然气管道工程有限公司'
union
  select a.* from lf.sys_dep a, rs b where a.pid=b.id
) select * FROM rs order by order_num; -- 查询单位

select * from lf.sys_user order by id desc
select * from lf.sys_dep where id=36
with recursive rs as(
  select * from lf.sys_dir where name='中俄东线管道工程南段'
union
  select a.* from lf.sys_dir a, rs b where a.pid=b.id
) select * FROM rs order by order_num; -- 数据目录

select * from lf.sys_attach where guid in ('bbfcfb74cd4f31d755d2795f8da48bfe','deb91a0cfe0df67bb71b66c366cac043')
delete from lf.sys_attach where guid in ('bbfcfb74cd4f31d755d2795f8da48bfe','deb91a0cfe0df67bb71b66c366cac043')
select * from lf.sys_meta where guid='6bf9b041-29b6-465a-b247-b05d1b29e6f6'
with recursive rs as(select id, pid from lf.sys_dir where id in (2,5,7,9,12)
union select a.id,a.pid from lf.sys_dir a, rs b where a.pid = b.id)
select distinct id from rs order by id; -- 数据目录
--------------------------------------------------------- 03.查询表结构
select * from pg_tables; select * from pg_class order by relnamespace;
select relnamespace,relkind,relname from pg_class where relnamespace in (select oid from pg_namespace) and relkind='r' order by 1,2;

select exts,checks from lf.sys_dir where code='0200000002'
select st_astext(geom) from bs.m_pipeline
select * from lf.sys_dir where pid=0 order by code
select c.relname as "表名", cast(obj_description(c.oid) as varchar) as "表名描述",a.attnum as "序号", a.attname as "列名", 
  concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\(.*\)')) as "字段类型",d.description as "备注"
from pg_attribute a left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_class c on a.attrelid = c.oid left join pg_type t on a.atttypid = t.oid
where a.attnum >= 0 and c.relname like 'sys_%' and obj_description(c.oid) is not null --and relnamespace=20582
order by c.relname desc, a.attnum asc;
--------------------------------------------------------- 04.索引
alter table lf.sys_role_user add constraint idx_unique_role_user unique (roleid, userid); -- 联合唯一索引
insert into lf.sys_role_user (roleid,userid) values (1,1); -- 测试
select * from lf.sys_role_user;

select ST_GeomFromText('MULTILINESTRING ((103.67272724 36.089968492,103.656203297 36.0911306690001,103.649390844 36.090980244,103.644883185 36.090890196,103.646134979 36.090610564,103.645396235 36.0890745,103.646101335 36.088113311,103.646058726 36.0876219790001,103.644862539 36.087707257,103.643858659 36.0875148540001,103.643004284 36.0873865610001,103.642171269 36.0872582700001,103.641487757 36.0871940860001,103.641081945 36.087065855,103.638583273 36.085014749,103.636468946 36.083583193,103.633756642 36.0816602260001,103.633180016 36.081232904,103.632475301 36.080485133,103.632112266 36.0801005660001,103.631685281 36.0791392150001,103.631172859 36.07815649,103.63072446 36.0774301180001,103.629784821 36.076511419,103.629357932 36.0751228280001,103.629913387 36.07471703,103.630404884 36.0737558100001,103.631451646 36.073243271,103.631900315 36.072773372,103.632156992 36.0711926220001,103.632541886 36.0693555450001,103.631346368 36.066471506,103.63299138 36.0652113830001,103.632564378 36.064314118,103.633291037 36.0624770910001,103.637050854 36.060533687,103.636496349 36.0567098110001,103.635172682 36.05378303,103.634363105 36.0444049980001,103.634112336 36.0408244560001,103.632404466 36.036646756,103.630809818 36.0355831750001,103.628645676 36.0340258140001,103.628228665 36.030911652,103.626293407 36.0247211440001,103.625078723 36.022632242,103.625344709 36.0218727440001,103.625041313 36.0201257620001,103.624813966 36.017923072,103.623181251 36.017277232,103.62230813 36.0160618470001,103.621093276 36.014732483,103.620030391 36.0130613490001,103.616119119 36.0130607900001,103.613764939 36.012262939,103.613158446 36.007439789,103.610386754 36.0057684110001,103.609855389 36.0045910520001,103.609361903 36.0038314440001,103.608109306 36.0014766990001,103.607425816 36.0013246950001,103.606666336 36.0013625610001,103.60465356 36.002159788,103.601768128 35.9996908800001,103.602110769 35.99577931,103.598240476 35.982372921,103.597330132 35.9778155650001,103.593533787 35.9733337530001,103.592926414 35.972422221,103.584610569 35.970826005,103.583363471 35.9679689270001),(103.594864915 35.974450242,103.594669677 35.9740031430001,103.591290009 35.970698993,103.588669745 35.9690658600001,103.586543153 35.9677365650001,103.584758309 35.966976941,103.581796465 35.9626094590001,103.578720531 35.9603306980001,103.577353413 35.9597989550001,103.576290063 35.9598368810001,103.572834291 35.958469543,103.569074876 35.954899536,103.565847014 35.952886606,103.564745716 35.952544759,103.562923112 35.948974848,103.560037114 35.946012513,103.558442132 35.9455187360001,103.556847257 35.943619814,103.555176334 35.942974126,103.554758782 35.940505612,103.554910809 35.938948569,103.556278224 35.9356446520001,103.55646823 35.9340496340001,103.557075886 35.933669896,103.558557012 35.933214246,103.559734491 35.930593903,103.561139655 35.9302521810001,103.562582876 35.9288471090001,103.562089429 35.925619053,103.562583437 35.9215935330001,103.561938074 35.9184794020001,103.560761068 35.914985475,103.559698005 35.911301671,103.559052558 35.9092746250001,103.558483059 35.9073092960001,103.557942016 35.905714242,103.556347137 35.903862793,103.556062423 35.902438649,103.554752335 35.9010429350001,103.554809368 35.900159977,103.55429671 35.8998181590001,103.552331581 35.897681866,103.551591162 35.8960583200001,103.551448956 35.893380947,103.552161213 35.8908460270001,103.553386065 35.889507404,103.554126753 35.887656071,103.553671096 35.8868300520001,103.550823147 35.882927792,103.549740992 35.8805921650001,103.547747307 35.879424278,103.545611156 35.8789399670001,103.545297873 35.8786266440001,103.544813803 35.87688918,103.543076499 35.875237102,103.540399472 35.8709645760001,103.53840593 35.86794532,103.534789148 35.861508069,103.532134474 35.854648373,103.529502284 35.843508371,103.530819451 35.835153533,103.540086339 35.8276598950001,103.5416056 35.82512818,103.542821337 35.818849405,103.550568777 35.8162673620001,103.553961462 35.814950998,103.555379399 35.813128179,103.561101464 35.8099384080001,103.562924859 35.803254575,103.56454545 35.79996333,103.569862517 35.795659551,103.576343969 35.7943433390001,103.578825214 35.793026931,103.589307954 35.777735443,103.593865635 35.7715074660001,103.59730899 35.769735385,103.598372792 35.7638110500001,103.600702372 35.7593552160001,103.607538286 35.7578871160001,103.610728362 35.757431551,103.615386897 35.756621608,103.617058069 35.7540392660001,103.618070971 35.751558163,103.632452859 35.7335325340001,103.639491527 35.729330111,103.639795743 35.724064006,103.643492876 35.7144940260001,103.65331685 35.7057345210001,103.658026401 35.699962274,103.659494922 35.698797723,103.674939873 35.684468559,103.680510042 35.681126872,103.694688356 35.676519715,103.709288898 35.6684590310001,103.717211252 35.659337479,103.723092517 35.6588576670001,103.74469828 35.6425352580001,103.748179117 35.641095122,103.756101425 35.632573698,103.770025529 35.616971064,103.775067694 35.602448221,103.77722832 35.6001678420001,103.78058915 35.59848765,103.782389857 35.5941668190001,103.780709939 35.588525535,103.781910815 35.5803638570001,103.806758465 35.548198235,103.826164714 35.5194930820001,103.828885993 35.51021124,103.831446736 35.5074907870001,103.839448555 35.505570773,103.846490147 35.503970779,103.857693038 35.4967697980001,103.863454633 35.4916489920001,103.864735341 35.485887828,103.868736385 35.483167447,103.872258501 35.4650837720001,103.874660003 35.451961097,103.882181661 35.450841229,103.884902767 35.4437998650001,103.882662738 35.4380385270001,103.880582499 35.4354778810001,103.880423002 35.4284363740001,103.878823424 35.4185141830001,103.876903426 35.413232964,103.878344348 35.4050712980001,103.874663788 35.4023505380001,103.872263982 35.3932284790001,103.872584793 35.383466416,103.878186328 35.3786656710001,103.886028356 35.373544967,103.891629562 35.373065141,103.894830075 35.375145741,103.900431476 35.37210537,103.907793295 35.3684249500001,103.918515937 35.363144354,103.918036238 35.3578632050001,103.923477376 35.357863475,103.927798778 35.35130229,103.944282417 35.3487425590001,103.95148402 35.347462641,103.96284659 35.344902656,103.982370548 35.346503958,103.998854238 35.343304087,104.01133743 35.3356230690001,104.036826048 35.3118474120001,104.042137308 35.3050195530001,104.047448625 35.2974330160001,104.054276949 35.294398632,104.077040088 35.2572244340001,104.091835358 35.2431895830001,104.110424992 35.2162573590001,104.168088857 35.158221182,104.177193387 35.15291087,104.184401278 35.146841786,104.209816734 35.1498777580001,104.243199149 35.1417235930001,104.275671385 35.1304967290001,104.30662668 35.113807294,104.343651578 35.094690384,104.370964303 35.090139649,104.389476695 35.081339874,104.409202603 35.0773957100001,104.418609996 35.080127422,104.431659681 35.0743620970001,104.445922998 35.071935023,104.458365513 35.069204389,104.474449288 35.0716329570001,104.4966034 35.061922946,104.515115862 35.052212754,104.522398913 35.0555513050001,104.540304295 35.047661916,104.558512665 35.0461454530001,104.571561871 35.0467530400001,104.587646199 35.041898281,104.60767597 35.0327951090001,104.617691084 35.025208804,104.632864724 35.0239956620001,104.641665449 35.0230856800001,104.656232996 35.010644046,104.664124639 34.992132644,104.669588536 34.9733176500001,104.695384026 34.9672494810001,104.708738466 34.9444897390001,104.719665343 34.9189986310001,104.733929165 34.909895174,104.754564803 34.9150552150001,104.78430473 34.918091401,104.812223973 34.9193066650001,104.823756189 34.9150586270001,104.840144789 34.899582363,104.872617397 34.8834999480001,104.882632809 34.871968505,104.910552348 34.8692386330001,104.937865173 34.863474013,104.954556172 34.862260947,104.961233539 34.8489085080001,104.966697637 34.8273622660001,104.967608484 34.8215963430001,104.973981855 34.8152237470001,104.993710071 34.7806289110001,105.005546193 34.7706149200001,105.012222556 34.7706152490001,105.028002981 34.771526443,105.035589894 34.769705985,105.041356299 34.7636368290001,105.044088973 34.7445182270001,105.049249143 34.72904141,105.051677502 34.7211512580001,105.047429452 34.713867722,105.049857789 34.7062810400001,105.051679322 34.696873499,105.046520813 34.6901968610001,105.044093636 34.682306468,105.040149171 34.6735055870001,105.042273764 34.6695605560001,105.037722086 34.664401308,105.041667753 34.6571181740001,105.04348933 34.6471036880001,105.038027463 34.638909674,105.045615738 34.618880898,105.057147706 34.617971051,105.063217602 34.611598439,105.068376747 34.6097778610001,105.07475091 34.592783743,105.077482444 34.5888387420001,105.08203442 34.5900528550001,105.091139254 34.5806456710001,105.097816387 34.5703279520001,105.10024479 34.561830856,105.106921538 34.5566721610001,105.110867321 34.5478716690001,105.112081726 34.540891872,105.119669566 34.526629063,105.140610337 34.509635664,105.150323221 34.4853583840001,105.183099499 34.466544737,105.211627407 34.44469616,105.227713273 34.419205305,105.245314212 34.424365197,105.256846926 34.413440774,105.26321975 34.4143515050001,105.274448836 34.4055513690001,105.278091166 34.3964473890001,105.293265262 34.3891648100001,105.31845501 34.367012597,105.330595861 34.3400041890001,105.343343382 34.3166374740001,105.362159402 34.3057134090001,105.376726713 34.296306497,105.397970276 34.2884172730001,105.412841126 34.278099958,105.424374177 34.262623455,105.435300554 34.2435052590001,105.443495264 34.2301528950001,105.444407182 34.209820316,105.455030109 34.190398633,105.464439353 34.168245642,105.475668302 34.1612663380001,105.483255618 34.153983386,105.484166727 34.1445757990001,105.498127475 34.130009833,105.496307052 34.1245472460001,105.500556768 34.109373856,105.500557642 34.0975384490001,105.506627535 34.0911658360001,105.506931723 34.0814547470001,105.521802592 34.0708339620001,105.525748971 34.053839725,105.517555675 34.0480733520001,105.519680287 34.043824849,105.52514279 34.043521647,105.51695032 34.0265268110001,105.528483969 34.0028565640001,105.534554241 33.991324929,105.527879393 33.9706885030001,105.537895236 33.9530876210001,105.543055071 33.941859413,105.535165602 33.931237504,105.560052087 33.906050556,105.557322117 33.8887525170001,105.565517198 33.8702411300001,105.569766997 33.853853852,105.587369281 33.840805425,105.611345106 33.8189566250001,105.619540184 33.8004452380001,105.616809498 33.792858304,105.621058428 33.7883064320001,105.623486708 33.7813266960001,105.629556132 33.7813269960001,105.641089037 33.767671325,105.655657051 33.7485533080001,105.65080258 33.733986413,105.659907513 33.723061871,105.646556601 33.6981765090001,105.651109888 33.6814857740001,105.643220235 33.673291641,105.647166891 33.6523522690001,105.654147304 33.6444623410001,105.653541313 33.6314130150001,105.641403618 33.615631873,105.635941867 33.6056170270001,105.638977177 33.5974234350001,105.650509346 33.5937823400001,105.655061569 33.59165826,105.653848702 33.5776984870001,105.649297519 33.565559384,105.652636631 33.5528137250001,105.655065061 33.5437096860001,105.648692544 33.5385503460001,105.652941801 33.5294463970001,105.6477833 33.522466287,105.649302002 33.5039545700001,105.652944738 33.4890846220001,105.649000628 33.475124716,105.635345064 33.4663233540001,105.63231119 33.4547912690001,105.631705371 33.4393141670001,105.638382398 33.4302103380001,105.639293628 33.418981919,105.621996189 33.413215098,105.618356384 33.3877232720001,105.606824788 33.3834740940001,105.605915496 33.3679969780001,105.59893676 33.352823034,105.604097001 33.335828857,105.600152666 33.32490367,105.605312313 33.316103238,105.610472245 33.303357668,105.618666603 33.2945573850001,105.618060957 33.2766525070001,105.607743465 33.2693686710001,105.615027891 33.253891959,105.612601198 33.2390217120001,105.61290515 33.2323453430001,105.621706428 33.2238485620001,105.630203992 33.218689956,105.637791737 33.205337564,105.638399381 33.1956264890001,105.637185934 33.18955699,105.643862364 33.1886469030001,105.650539322 33.1804534880001,105.65327054 33.180757095,105.65751964 33.1737774480001,105.66601701 33.1713500920001,105.670872833 33.1674051960001,105.673300426 33.1698330930001,105.673300515 33.168619204,105.678459678 33.1664951540001,105.683922158 33.1664954240001,105.690294856 33.1692269860001,105.689081431 33.1628540140001,105.69059916 33.157695065,105.689992567 33.152839483,105.689385975 33.1479839030001,105.696669742 33.1416113490001,105.708201517 33.14343275,105.714271268 33.1388809690001,105.712754088 33.1364531190001,105.712450966 33.131597551,105.722769599 33.1231008460001,105.728535442 33.124618491,105.730053084 33.1206734290001,105.734302072 33.1152111430001,105.743709854 33.1127838300001,105.751600566 33.1064113090001,105.753725279 33.1006454450001,105.755850363 33.0897205590001,105.753422835 33.0863822470001,105.753726742 33.0803128220001,105.763135027 33.070905655,105.768901328 33.0660503870001,105.773150187 33.062408933,105.775274639 33.0602847330001,105.77041993 33.048752558,105.760709113 33.0451104160001,105.751909191 33.0347919330001,105.753124034 33.021439226,105.753125125 33.006265625,105.757374703 32.9926095960001,105.764051439 32.987450902,105.769514661 32.977133124,105.773763193 32.9780437490001,105.780136654 32.9701537920001,105.7837782 32.971671331,105.784992302 32.9686366720001,105.77892327 32.963173877,105.784386123 32.9580151230001,105.778317004 32.953766214,105.783173675 32.9379859110001,105.784994916 32.9322200340001,105.795616843 32.9261511180001,105.793493088 32.918564212,105.791065993 32.9091564610001,105.786817418 32.908852779,105.781658843 32.9027830860001,105.782873293 32.8948928730001,105.784694578 32.8885200510001,105.781053183 32.8848782090001,105.788337015 32.8775952400001,105.782875183 32.86849081,105.790462876 32.8557453600001,105.792587501 32.851193385,105.797746598 32.849979752,105.787125908 32.8387507640001,105.781360108 32.836626177,105.777415243 32.832984319,105.780450433 32.8263080840001,105.771953174 32.8272180810001,105.76649117 32.820541427,105.754049197 32.815685262,105.74403454 32.817202127,105.738572383 32.812649778,105.713992042 32.80111663,105.704585086 32.792012006,105.703675191 32.7847286350001,105.714296792 32.783211798,105.73159738 32.74497518,105.749502593 32.7392100960001,105.754965767 32.729499262,105.753145265 32.724947092,105.755269757 32.7222159500001,105.755270149 32.7167534540001,105.763465753 32.690351794,105.764680525 32.677909503,105.756790749 32.67123273,105.752239588 32.6584866810001,105.74937432 32.646566835,105.753151774 32.6336188830001,105.76394236 32.6266058410001,105.772575197 32.615816151,105.774734213 32.6017891070001,105.780130293 32.5872227180001,105.760710167 32.5586279550001,105.75315795 32.5467584560001,105.758553876 32.534350089,105.776897253 32.53111396,105.775280158 32.511152166,105.790387925 32.487954164,105.775822569 32.4701497560001,105.770968179 32.4539643430001,105.744534681 32.422671704,105.71270423 32.4183540880001,105.66738663 32.407561737,105.646345655 32.4118767460001,105.623687581 32.3962299610001,105.611818829 32.391373824,105.614516961 32.382741864,105.608044092 32.3660168650001,105.539530974 32.3104443930001,105.503384453 32.3066660700001,105.494752309 32.3077446550001,105.464542276 32.276451831,105.261698375 32.138867856,105.250368937 32.1367092740001,105.075579655 31.9931921190001,105.038356023 31.962438455,105.033501203 31.9521876050001,105.025410076 31.931685988,105.005989133 31.9144208450001,104.966067299 31.893378151,104.935315903 31.8885210830001,104.891077925 31.8690966930001,104.884064486 31.86747783,104.869498551 31.8577660070001,104.850076971 31.849672463,104.835511302 31.8361841,104.825261665 31.8226959510001,104.827421036 31.80327385,104.830659415 31.783851801,104.823107147 31.7725218080001,104.802608285 31.739610946,104.782647535 31.726661823,104.775095379 31.713713312,104.762687837 31.698606539,104.765385995 31.6894350740001,104.757293621 31.686737147,104.747044432 31.6667749270001,104.730860622 31.647891428,104.708201902 31.6414162410001,104.70496529 31.6354815180001,104.705505544 31.6246914290001,104.701191045 31.6025714790001,104.69579608 31.601492202,104.688243923 31.588543691,104.680691202 31.5836877680001,104.65264073 31.5297358080001,104.632680987 31.5022200290001,104.612720309 31.488191896,104.602470479 31.477401274,104.599773966 31.4628344860001,104.578734243 31.449345803,104.558773714 31.433159647,104.540972094 31.4040254560001,104.519933784 31.3700355560001,104.480552784 31.3295706810001,104.40826241 31.2837091270001,104.338670411 31.2254390730001,104.281487589 31.1585375390001,104.219447342 31.1191505570001,104.161183732 31.0781452460001,104.128275178 31.0608794390001,104.078642558 31.0355202210001,103.987469066 30.9966713110001,103.982074471 30.9901969750001,104.000958836 30.9648411360001,103.997722992 30.9475767910001,104.002579336 30.935707904,104.005817574 30.9179043730001,104.023082396 30.9076546160001,104.028478133 30.8974042720001,104.037650396 30.8871541130001,104.041968155 30.8617975560001,104.036573704 30.853165199,104.043048522 30.8418358960001,104.045207859 30.822413795,104.044669309 30.808386619,104.065171263 30.796518502,104.060035369 30.7555669600001,104.064390838 30.6815043600001,104.081337091 30.5572318420001,104.098283343 30.427310574,104.132175848 30.297389305,104.194312107 30.0940342760001,104.29034087 29.901976749,104.482398397 29.704270471,104.708348429 29.5912954550001,104.92300096 29.5630517010001,105.069868481 29.5799979540001,105.233682254 29.5630517010001,105.380549775 29.534807947,105.533066046 29.495266692,105.719474823 29.461374187,105.956722356 29.4274816820001,106.165726136 29.4500766850001,106.33518866 29.472671688,106.499002433 29.5517542000001))')
alter table lf.sys_menu_auth add constraint idx_unique_menu_auth unique (menuid, authid); -- 联合唯一索引
insert into lf.sys_menu_auth (menuid, authid) values (1,1); -- 测试
select * from lf.sys_menu_auth;

{"code":500,"count":0,"msg":"\r\n### Error querying database.  Cause: org.postgresql.util.PSQLException: 错误: 函数 fn_uname(double precision) 不存在\n  建议：没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.\n  位置：366\r\n### The error may exist in com/lf/server/mapper/bs/MsitepointMapper.java (best guess)\r\n### The error may involve defaultParameterMap\r\n### The error occurred while setting parameters\r\n### SQL: SELECT gid, x, y, elev, segname, projname, pipename, belongs, belongsid, datastatus, remarks, version, datastage, sitename, sitedesc, sitetype, location, principal, useddate, eventid, parentid, dirid, depid, verid, createtime, createuser, updateuser, updatetime, fn_get_fullname(dirid, 2) AS dirName, fn_get_fullname(depid, 1) AS depName, fn_ver(verid) AS verName, fn_uname(createuser) AS createName, fn_uname(updateuser) AS updateName FROM bs.m_sitepoint WHERE (parentid = ?) ORDER BY gid ASC LIMIT ?\r\n### Cause: org.postgresql.util.PSQLException: 错误: 函数 fn_uname(double precision) 不存在\n  建议：没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.\n  位置：366\n; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 函数 fn_uname(double precision) 不存在\n  建议：没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.\n  位置：366","result":null,"time":1677582840698}
select * from bs.m_sitepoint
alter table lf.sys_role_res add constraint idx_unique_role_res unique (roleid,resid); -- 联合唯一索引
insert into lf.sys_role_res (roleid,resid) values (1,1);
select * from lf.sys_role_res;

alter table lf.sys_role_menu_auth add constraint idx_unique_role_menu_auth unique (roleid,menu_auth_id); -- 联合唯一索引
insert into lf.sys_role_menu_auth (roleid,menu_auth_id) values (1,1);
select * from lf.sys_role_menu_auth;
--------------------------------------------------------- 05.联合查询
select a.*,fn_uname(a.create_user) createUserName,fn_uname(a.update_user) updateUserName from lf.sys_blacklist a order by a.id;
select * from lf.sys_user a where uname like '%' and a.depid=ANY(fn_rec_array(1,'dep'));
select * from lf.sys_user a left join lf.sys_role_user b on a.id=b.userid;

select a.* from lf.sys_user a where not exists (select b.id from lf.sys_role_user b where b.userid = a.id and b.roleid = 1)
and uname like '%室%' and a.depid = ANY(fn_rec_array(1, 'dep'))
order by a.id limit 10 offset 0;

select a.*,fn_rec_query(a.depid, 'dep') depName,fn_rec_query(a.dirid, 'dir') dirName from lf.sys_style a;

select * from lf.sys_auth a left join lf.sys_menu;
select a.* from lf.sys_auth a where not exists (select b.id from lf.sys_menu_auth b where b.authid = a.id and b.menuid = 1);

select a.* from lf.sys_res a where not exists (select b.id from lf.sys_role_res b where b.resid = a.id and b.roleid = 1);

select a.*,c.name from lf.sys_menu_auth a inner join lf.sys_auth c on a.authid = c.id
where not exists (select b.id from lf.sys_role_menu_auth b where b.menu_auth_id = a.id and b.roleid = 1) and a.menuid = 1 order by c.id;

select a.*,b.menuid,c.name from lf.sys_role_menu_auth a inner join lf.sys_menu_auth b on a.menu_auth_id = b.id
inner join lf.sys_auth c on b.authid = c.id where a.roleid = 1 and b.menuid = 1 order by c.id;
----------------------------------------------------------------------------------------------
update lf.sys_operate set modular1='运维管理',modular2='菜单管理' where position('/Menu/select' in url)>0 and (modular1 is null or modular2 is null);
update lf.sys_operate set modular1='运维管理',modular2='用户管理' where position('/user/select' in url)>0 and (modular1 is null or modular2 is null);
update lf.sys_operate set modular1='运维管理',modular2='资源管理' where position('/res/select' in url)>0 and (modular1 is null or modular2 is null);

delete from lf.sys_operate where modular1 is null or modular2 is null;
--------------------------------------------------------- 06.查询授权
select gid,gb,name,st_astext(a.geom) from bd.dlg_agnp a;

-- 根据用户Uid查询资源
select distinct d.id,d.name,d.server from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_res c on b.roleid = c.roleid
inner join lf.sys_res d on c.resid = d.id
where a.uid = 'admin';

-- 根据用户Uid查询菜单
select distinct e.id,e.pid,e.cn_name,e.en_name,e.url,e.type,e.icon,e.level,e.order_num,e.is_show
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
where a.uid = 'admin';

-- 根据用户Uid查询权限
select distinct f.id,e.cn_name,f.name,e.perms,f.tag
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
inner join lf.sys_auth f on d.authid = f.id
where a.uid = 'admin';

-- 根据用户Uid查询权限2
select distinct e.perms || f.tag as "perms"
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
inner join lf.sys_auth f on d.authid = f.id
where a.uid = 'admin';
--------------------------------------------------------- 07.数据统计 
select modular2,count(*) from lf.sys_operate group by modular2;

select to_char(optime,'yyyy-mm-dd') as optime,count(*) from lf.sys_login where optime between (select optime - interval '30 day')
and optime group by to_char(optime, 'yyyy-mm-dd') order by to_char(optime, 'yyyy-mm-dd') asc;

select a.*, fn_tab_count(a.ns, a.tab, '1=1') "rows" from
(select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tableType from lf.sys_dict
order by tab
limit 10 offset 0) a;
select a.*,coalesce(a.native,'') from lf.sys_user a;
select ST_PointFromText('POINT(95.80461853400004 34.13862467200005)');
select ST_LineFromText('LINESTRING(04.98985101830993 37.43840773692756,104.99318913447104 37.43883729720358)', 4326);
select ST_PolygonFromText('POLYGON((104.9907822932683 37.43532941961706,104.99088987736599 37.43532941961706,104.9908670336867 37.4349030213574,104.99078327712658 37.4349030213574,104.9907822932683 37.43532941961706))',4326);

select dirid,depid from bd.dlg_25w_resa group by dirid,depid;
select * from lf.sys_dict where tab='dlg_25w_resa';
-- 114ms，180/3248
select count(1) from bd.dlg_agnp a where ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4326))

-- 1101：gid >= 50 and name like '县' and objectid < 5000.0 and pac = '360430'
select count(*) from bd.dlg_agnp where gid >= 50 and name like '%县%' and objectid < 5000 --and pac = '360430'
and ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4326))

select a.* from lf.sys_domain a inner join lf.sys_dict b on a.dom_name = b.domain_na
where b.ns = 'bd' and b.tab = 'dlg_25w_aanp' and b.domain_na is not null;

select * from lf.sys_dict where ns = 'bd' and tab = 'dlg_25w_aanp' and domain_na is not null;
select * from lf.sys_domain where dom_name='dlg25gbcode';
select * from bd.dlg_25w_aanp limit 10;

select * from bd.dlg_25w_agnp
where ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 0))
limit 10;
select st_srid(geom) from bd.dlg_25w_agnp limit 1;
select * from bd.dlg_25w_agnp where class != 'BB' limit 1;

select a.* from lf.sys_domain a where exists (select * from lf.sys_dict b where b.ns = 'bd' and b.tab = 'dlg_25w_hyda') and a.dom_code like '210%' order by a.id
select * from lf.sys_download a where create_user = 1 and type = 2;
--------------------------------------------------------- 08.查询空间数据
select to_char(create_time, 'yyyy-mm-dd hh24:mi:ss') from lf.sys_meta;
select to_timestamp('2018-08-15 12:10:10', 'yyyy-MM-dd hh24:mi:ss');
select a.*,fn_uname(a.create_user) uname from lf.sys_token a
select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tabletype from lf.sys_dict order by tab;

-- 3248：POINT(95.8046 34.1386)
select * from bd.dlg_agnp;
select count(*) from bd.dlg_agnp;
delete from bd.dlg_agnp;
select ST_AsText(geom) from bd.dlg_agnp limit 1;

-- 7348：MULTILINESTRING((113.6007 6.9434,113.6015 6.9444,113.6007 6.9434))
select * from bd.dlg_25w_boul;
select count(*) from bd.dlg_25w_boul;
delete from bd.dlg_25w_boul;
select ST_AsText(geom) from bd.dlg_25w_boul limit 1;

-- 156847：MULTIPOLYGON(((108.6790 19.0108,108.6791 19.0104,108.6796 19.0104,108.6790 19.0108)))
select * from bd.dlg_25w_resa;
select count(*) from bd.dlg_25w_resa;
delete from bd.dlg_25w_resa;
select ST_AsText(geom) from bd.dlg_25w_resa limit 1;

-- MultiLineString MultiPolygon Point
select GeometryType(geom) from bd.dlg_25w_boul where gid=20;
select st_srid(geom) from bd.dlg_25w_boul where gid=20 limit 1;
select ST_AsText(geom) from bd.dlg_agnp where gid=20;

select * from lf.sys_dict where ns='bd' and tab='dlg_25w_boul' and field='geom';
select * from lf.sys_meta; -- delete from lf.sys_meta where id<113;
select * from lf.sys_meta_file; -- delete from lf.sys_meta_file where metaid<113;

select * from lf.sys_meta_file a where metaid in (113,120,121,122);
alter sequence bd.dlg_agnp_gid_seq restart with 4000;
select max(gid) from  bd.dlg_agnp;
--------------------------------------------------------- 09.管线分析
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_hydl  limit 1;
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_lrdl  limit 1;
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_lrrl  limit 1;
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_hyda  limit 1;
select ST_SRID(geom), ST_GeometryType(geom) from bs.m_pipesegment limit 1;

SELECT a.name as acrossName, b.remarks, b.pipename as pipeName,
       st_length( st_geographyfromtext( st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) AS acrossLength,
       st_astext(b.geom) as wkt
FROM bd.dlg_25w_hyda AS a, (SELECT * FROM bs.m_pipesegment WHERE segname = 'DD' ) AS b WHERE ST_Intersects(a.geom, b.geom);

select row_number() over() as gid, a.name as acrossName, b.segname as segName, b.remarks, b.pipename as pipeName,
  cast( st_length( st_geographyfromtext( st_astext( st_intersection(ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) as decimal(12, 3) ) AS acrossLength,
  st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) as wkt
from bd.dlg_25w_hyda as a, (select segname, remarks, pipename, geom from bs.m_pipesegment where gid = 24) as b
where ST_Intersects(a.geom, b.geom);

select *, st_astext(geom) as wkt from bs.m_pipesegment;
select pipename,segname from bs.m_pipesegment where gid = 24;
--------------------------------------------------------- 10.查询位置
select '国' "key", cname "value" from bs.th_globe_country where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
union all
select '省' "key", cname "value" from bs.th_province_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
union all
select '市' "key", cname "value" from bs.th_district_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
union all
select '县' "key", cname "value" from bs.th_county_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom);
--------------------------------------------------------- 11.根据用户ID查询权限
select * from lf.sys_role a inner join lf.sys_role_user b on a.id=b.roleid inner join lf.sys_user c on b.userid=c.id
where c.uid = 'kc';

select distinct e.id,e.pid,e.cn_name,e.en_name,e.url,e.perms,e.type,e.css,e.icon,e.level,e.order_num,e.is_show
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
where a.uid = 'kc'
order by e.order_num;

with recursive rs as(
    select a.* from lf.sys_menu a where a.id = 64
union
    select a.* from lf.sys_menu a, rs b where a.pid = b.id
)
select * FROM rs where rs.id in (
    select *
      from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
      inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
      inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
      inner join lf.sys_menu e on d.menuid = e.id
      inner join lf.sys_auth f on d.authid = f.id
      where a.uid = 'admin')
order by order_num;
--------------------------------------------------------- A0 修改表结构
-- drop function fn_alter_tab_fields();
create or replace function fn_alter_tab_fields()