---------------------------------------------------------------------------------------------- 00.创建空间扩展
|
create extension postgis;
|
create extension pgrouting;
|
create extension postgis_raster;
|
create extension postgis_topology;
|
create extension fuzzystrmatch;
|
create extension postgis_tiger_geocoder;
|
create extension address_standardizer;
|
create extension "uuid-ossp";
|
|
select uuid_generate_v4(); -- uuid，36位
|
select split_part('12.34.56.78', '.', 1); -- 切割
|
select substr('FY2021', 3, 2); -- 提取
|
select substr('FY2021', 3); -- 提取
|
select substring('FY2021' FROM 3 for 2); -- 提取
|
select position('23' in 'w123456'); -- 位置
|
select md5(''); -- MD5
|
select concat('aa', 'bb'); -- 连接
|
select concat_ws('_', 'a', 'b'); -- 连接
|
select chr(97); -- ACSII值转字符
|
select ascii('x'); -- 字符转ACSII值
|
select to_hex(31); -- 转16进制
|
select length('aabbcc'); -- 字符长度
|
select reverse('aabbcc'); -- 反转
|
select initcap('aabbcc'); -- 首字母大写
|
select replace('aabbcc', 'bc', 'xy'); -- 替换
|
select left('abcde', 2); -- 左截取
|
select right('abcde', 2); -- 右截取
|
---------------------------------------------------------------------------------------------- 01.查询连接数
|
show max_connections;
|
select count(1) from pg_stat_activity;
|
|
-- 查询表架构和表名
|
select oid,table_catalog,table_schema,table_name
|
from information_schema.tables t1, pg_class t2
|
where table_schema = 'bd' and t1."table_name" = t2.relname
|
order by table_catalog,table_schema,table_name;
|
|
-- 查询字段信息
|
select e.table_catalog, e.table_schema, c.relname "tab", cast(obj_description(c.oid) as varchar) "desc", a.attnum "num", a.attname "col",
|
d.typname "type", concat_ws('', d.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '(.*)')) "type2", b.description "bak"
|
from pg_attribute a left join pg_description b on b.objoid = a.attrelid and b.objsubid = a.attnum
|
left join pg_class c on a.attrelid = c.oid
|
left join pg_type d on a.atttypid = d.oid
|
left join information_schema.tables e on e.table_name = c.relname
|
where a.attnum >= 0 and reltype > 0 and a.attname = 'verid' and d.typname != 'int4' --and relnamespace in (29257,20582)
|
order by c.relname desc, a.attnum asc;
|
|
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';
|
|
st_length( st_geographyfromtext( st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) AS acrossLength
|
---------------------------------------------------------------------------------------------- 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.查询
|
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;
|
|
select * from lf.sys_dir where id in (select min(id) from lf.sys_dir where name in ('测量（ESV）','勘察（EGE）','地灾（EGD）','洞库（EGD）') group by name);
|
select * from lf.sys_dir where pid = 1;
|
select * from lf.sys_dir order by id;
|
|
select * from lf.sys_token where create_time >= '2022-09-29' and create_time <= '2022-09-30';
|
|
select * from lf.sys_token where to_char(create_time,'yyyy-MM-dd') = '2022-09-30';
|
|
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的表
|
----------------------------------------------------------------------------------------------
|
-- 10进制转62进制
|
WITH RECURSIVE T(N, S) AS (
|
SELECT 3843::NUMERIC(30, 0) N, '' S
|
UNION ALL
|
SELECT trunc(N / 62)::NUMERIC(30, 0), substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', (N % 62)::INT + 1, 1) || S FROM T WHERE N > 0
|
)
|
SELECT S FROM T WHERE N = 0;
|
|
-- 62进制转10进制
|
WITH RECURSIVE T(S, N) AS (
|
SELECT 'ZZ' S, 0::NUMERIC N
|
UNION ALL
|
SELECT SUBSTR(S, 2), (POWER(62, LENGTH(S)::NUMERIC - 1) * (strpos('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', SUBSTR(S, 1, 1)) - 1) + N) FROM T WHERE LENGTH(S) > 0
|
)
|
SELECT N::NUMERIC(30, 0) FROM T WHERE LENGTH(S) < 1;
|
---------------------------------------------------------------------------------------------- -1.测试
|
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 * from lf.sys_meta order by id desc;
|
select * from lf.sys_meta where metaid>0 or ismeta>0;
|
