set autocommit off; 
st_length( st_geographyfromtext( st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) AS acrossLength;
---------------------------------------------------------------------------------------------- -1.SQL测试
select case a.userid when 2 then guid else null end "guid", a.* from lf.sys_apply a order by id desc
select * from bs.s_explorationpoint where upper(exppointid) like '%XK0%';
select * from lf.sys_layer where url is not null and serve_type='WMS' and data_type in ('工程项目','工程项目-地灾类','工程项目-测量类','工程项目-洞库类','工程项目-勘察类') order by id;
insert into lf.sys_layer (pid,cn_name,en_name,url,type,level,order_num,is_show,create_user,serve_type,data_type,elev) values
  (423, '地面层', 'fushun_site', 'tileset/o/39HBFG/tileset.json', 2, 3, 5, 0, 1, 'Tileset', '工程测量模型', 1200);
select ST_AsText(geom) "geom", a.*, fn_get_fullname(a.depid, 1) depName, fn_get_fullname(a.dirid, 2) dirName, fn_uname(create_user) createName, fn_uname(update_user) updateName from lf.sys_publish a
select fn_get_fullname(depcode, 1) "m1", cast(sum(sizes) as decimal(18, 3)) "sizes" from lf.sys_meta group by depcode order by depcode;

-- 删除冗余数据，修复tab_guid值
select id,name,path,tab,tab_guid from lf.sys_attach where tab != 'bd.b_borehole' order by id desc limit 200; 
--delete from bs.s_explorationpointstratum where parentid in (select eventid from lf.sys_meta where name='勘探点地层表.xlsx');
select * from lf.sys_meta where type = 'tif' or type = 'tiff' or lower(name) in ('平面图索引文件.xlsx', '穿跨越地形图.dwg', '中线成果表.xlsx', '离散点.xlsx', '控制点.xlsx', '地面线.xlsx');
             
-- delete from lf.sys_apply where id > 47; delete from lf.sys_flow where applyid > 47;
select * from lf.sys_layer where is_project=1 order by id desc;
select * from lf.sys_layer order by id desc;
select a.*,st_astext(geom) from lf.sys_publish a order by id desc;
select * from lf.sys_meta_pub order by id desc;
select * from lf.sys_download order by id desc limit 20; --downid
select * from lf.sys_apply order by id desc;
select * from lf.sys_flow order by id desc;
select * from lf.sys_meta order by id desc limit 100;
select * from lf.sys_fme_log order by id desc;
select * from lf.sys_pub_down order by id desc;
select id, pid, name, code from lf.sys_dir order by code;
select * from public.coal2000 order by gid limit 20;
select * from lf.sys_dir where pid = 0 order by id;
select * from lf.sys_dict order by id desc;
select * from lf.sys_meta where type in ('3dml');
select * from lf.sys_ars;
select * from lf.sys_report;

-- ALL -> 560, type=2 -> 442, WMS -> 340, url=null -> 77, type=2 & url -> 25, .json -> 17
select serve_type from lf.sys_layer group by serve_type;
update lf.sys_layer set serve_type = null where type = 2;
update lf.sys_layer set serve_type = 'WMS' where type = 2 and position('LF:' in url) > 0;
update lf.sys_layer set serve_type = '3DML' where type = 2 and serve_type is null and position('b3dm' in url) > 0;
update lf.sys_layer set serve_type = 'Tileset' where type = 2 and serve_type is null and position('.json' in url) > 0;
update lf.sys_layer set serve_type = 'TMS' where type = 2 and serve_type is null and position('.png' in url) > 0;
update lf.sys_layer set serve_type = 'Mpt' where type = 2 and serve_type is null and position('.mpt' in url) > 0;
update lf.sys_layer set serve_type = 'DEM' where type = 2 and serve_type is null and position('terrain/' in url) > 0;

select id, pid, cn_name, en_name, url, serve_type, data_type, elev, pubid, is_project, bak
from lf.sys_layer where type = 2 and serve_type is not null order by id;
-- update lf.sys_publish a set url = replace(url, '192.168.20.205:80', '{host}') where position('192.' in url) > 0; 

select * from bs.s_explorationpoint where createtime < '2023-05-26 10:00:00';
select * from bs.s_surveyworksite where createtime < '2023-05-26 10:00:00';
select * from lf.sys_meta where type='cpt';
select id,name,code from lf.sys_dir where pid=0 order by code

select * from bs.dlg_resnt2000;
alter table bs.dlg_resnt2000 alter column floors type varchar(20);
select * from lf.sys_layer l where cn_name='倾斜摄影数据';

select id, pid, type, cn_name, en_name, url, serve_type, data_type, elev, pubid, is_project, bak from lf.sys_layer order by id;  --  8-299
-- update lf.sys_layer set is_project = 1 where id between 8 and 299;
update lf.sys_layer set is_project = null where id between 8 and 299 and type = 1;




















---------------------------------------------------------------------------------------------- 
-- update lf.sys_publish a set geom = ST_GeomFromText('POINT Z (31.231025 113.223529 12)') where id = 1;
select id from lf.sys_layer where cn_name = '三维地质模型' and pid = (select id from lf.sys_layer where cn_name = '勘察' order by pid limit 1);
select id from lf.sys_layer where cn_name = '单体模型' and pid = (select id from lf.sys_layer where cn_name = '测绘' order by pid limit 1);
select id from lf.sys_layer where cn_name = '数字正射影像图' and pid = (select id from lf.sys_layer where cn_name = '测绘' order by pid limit 1);
select id from lf.sys_layer where cn_name = '三维地形模型' and pid = (select id from lf.sys_layer where cn_name = '测绘' order by pid limit 1);
select id from lf.sys_layer where cn_name = '三维地质模型' and pid = (select id from lf.sys_layer where cn_name = '地灾' order by pid limit 1);
select id from lf.sys_layer where cn_name = '洞库模型' and pid = (select id from lf.sys_layer where cn_name = '洞库' order by pid limit 1);
select id from lf.sys_layer where cn_name = '其他' and pid = (select id from lf.sys_layer where cn_name = '工程数据' order by pid limit 1);

insert into lf.sys_layer (pid, cn_name, en_name, url, type, level, order_num, is_show, create_user, data_type, serve_type, pubid, elev) values
(@pid, @cnName, @enName, @url, 2, (select coalesce(level, 0) + 1 from lf.sys_layer where id = @pid), (select coalesce(max(order_num), 0) + 1 from lf.sys_layer where pid = @pid), 0, @user, @name, @serve, @pubid, @elev)
select fn_get_fullname(a.depcode, 1) depName, fn_get_fullname(a.dircode, 2) dirName, a.* from lf.sys_meta a order by id desc limit 10;
---------------------------------------------------------------------------------------------- SY1.煤层54表
-- drop table public.coal54;
create table if not exists public.coal54 (
    gid serial primary key,
	top numeric(12, 3),
	bottom numeric(12, 3),
    clong numeric(12, 3),
	width numeric(12, 3),
	height numeric(12, 3),
	ctype varchar(8),
	density numeric(12, 3),
	gangue numeric(12, 5),
	volume numeric(12, 3),
    geom geometry(point, 2435)
);
create index idx_coal54_geom on public.coal54 using gist (geom);
comment on table public.coal54 is '煤层54表';
comment on column public.coal54.gid is '主键ID';
comment on column public.coal54.top is '上顶高度';
comment on column public.coal54.bottom is '下底高度';
comment on column public.coal54.clong is '长度';
comment on column public.coal54.width is '宽度';
comment on column public.coal54.height is '高度';
comment on column public.coal54.ctype is '类别';
comment on column public.coal54.density is '视密度';
comment on column public.coal54.gangue is '含矸率';
comment on column public.coal54.volume is '体积';
comment on column public.coal54.geom is '空间';

select count(*) from public.coal54; -- 12701714
select a.*, ST_AsText(geom) from public.coal54 a order by gid desc limit 100;
---------------------------------------------------------------------------------------------- SY2.煤层2000表
-- create sequence public.coal2000_gid_seq increment  by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1;
-- alter sequence public.coal2000_gid_seq restart with 12701715; vacuum analyze public.coal2000;

-- drop table public.coal2000;
create table if not exists public.coal2000 (
    gid serial primary key,
	top numeric(12, 3),
	bottom numeric(12, 3),
    clong numeric(12, 3),
	width numeric(12, 3),
	height numeric(12, 3),
	ctype varchar(8),
	density numeric(12, 3),
	gangue numeric(12, 5),
	volume numeric(12, 3),
	coal_prod numeric(15, 3),
	gangue_prod numeric(15, 5),
	soil_prod numeric(15, 5),
    geom geometry(point, 4490)
);
-- drop index if exists public.idx_coal2000_geom;
create index idx_coal2000_geom on public.coal2000 using gist (geom);
create index idx_coal2000_top on public.coal2000 (top);
create index idx_coal2000_bottom on public.coal2000 (bottom);
comment on table public.coal2000 is '煤层2000表';
comment on column public.coal2000.gid is '主键ID';
comment on column public.coal2000.top is '上顶高度';
comment on column public.coal2000.bottom is '下底高度';
comment on column public.coal2000.clong is '长度';
comment on column public.coal2000.width is '宽度';
comment on column public.coal2000.height is '高度';
comment on column public.coal2000.ctype is '类别';
comment on column public.coal2000.density is '视密度';
comment on column public.coal2000.gangue is '含矸率';
comment on column public.coal2000.volume is '体积';
comment on column public.coal2000.coal_prod is '煤量';
comment on column public.coal2000.gangue_prod is '矸石量';
comment on column public.coal2000.soil_prod is '土量';
comment on column public.coal2000.geom is '空间';
-- drop index if exists public.idx_coal2000_ctype;
create index idx_coal2000_ctype on public.coal2000 (ctype);

--煤量 = 体积 * 视密度，矸石量 = 体积 * 含矸率，土量 = 总体积 - 煤量- 矸石量
select count(*) from public.coal2000; -- 12701714
select * from public.coal2000 order by gid desc limit 100;
select ctype, sum(volume) "volume", sum(coal_prod) "coal_prod", sum(gangue_prod) "gangue_prod", 0 "soil_prod" from public.coal2000
where (top between 640 and 690 or bottom between 640 and 690) and ST_Intersects(geom, ST_PolygonFromText('POLYGON ((113.5807 45.4897,113.5898 45.4893,113.5857 45.4837,113.5807 45.4897))', 4490)) group by ctype order by ctype;
-- select ST_GeometryFromText('POLYGON ((113.5807 45.4897,113.5898 45.4893,113.5857 45.4837,113.5807 45.4897))', 4490)
---------------------------------------------------------------------------------------------- -1.更新钻孔
select count(*) from bd.b_borehole; -- 357824
select count(*) from lf.sys_attach; -- 357880 = 56 + 357824
select * from bd.b_borehole limit 10; -- 基础钻孔

-- update bd.b_borehole set path = 'Drill\' || path;
-- insert into lf.sys_attach (name, guid, path, tab, tab_guid)
select substr(path, length(path) - position('\' in reverse(path)) + 2), eventid, path, 'bd.b_borehole', eventid from bd.b_borehole;
---------------------------------------------------------------------------------------------- 00.SQL查询
insert into lf.sys_meta_pub (metaid, pubid) select 1, 1 from (select 1) temp where not exists (select 1 from lf.sys_meta_pub where metaid = 1 and pubid = 1);

select * from bd.dlg_agnp where ST_Intersects(geom, ST_Buffer(ST_PointFromText('POINT (100.746244878 35.588071316)', 4326), 10.0 / 1852 / 60)) limit 20;
select * from bd.dlg_agnp where ST_Intersects(geom, ST_Buffer(ST_PointFromText('POINT (100.746244878 35.588071316)', 4326), 10.0 * 0.00000899928)) limit 20;

select * from bd.dlg_agnp where ST_Intersects(geom, 
  ST_GeomFromText('POLYGON((100.746245 35.588161,100.746167 35.588135,100.746134 35.588071,100.746167 35.588008,100.746245 35.587981,100.746323 35.588008,100.746355 35.588071,100.746323 35.588135,100.746245 35.588161))', 4326)
)

select * from bd.dlg_25w_boul where ST_DWithin(geom, ST_GeomFromText('POINT (101.9281 36.58675)', 4490), 10000.0 / 1852 / 60) = true limit 20;

select id,cn_name,en_name,url,serve_type,data_type from lf.sys_layer where url is not null and
  serve_type='WMS' and data_type in ('工程项目','工程项目-地灾类','工程项目-测量类','工程项目-洞库类','工程项目-勘察类') order by id;

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='[REDACTED]';
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='[REDACTED]';

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;
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);                     -- 右截取
---------------------------------------------------------------------------------------------- 02.查询连接数
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 = 'updateuser' 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';
---------------------------------------------------------------------------------------------- 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;

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的表
----------------------------------------------------------------------------------------------
