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;
|
select fn_get_fullname(code, 2) from lf.sys_dir where code='0A05'; -- 查询完整路径
|
|
-- 删除冗余数据，修复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;
|
|
select id, pid, type, cn_name, en_name, url, serve_type, data_type, elev, pubid, is_project, bak
|
from lf.sys_layer where serve_type = 'WMS' order by id;
|
select id,uid,uname,pwd from lf.sys_user order by uid;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
----------------------------------------------------------------------------------------------
|
-- 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='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;
|
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的表
|
----------------------------------------------------------------------------------------------
|
