From 1e0b068d1f60747751cd839f814ef09ee15dcaaf Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期一, 10 四月 2023 15:25:28 +0800
Subject: [PATCH] 1
---
data/db_cx.sql | 71 ++++++++++++++++++-----------------
1 files changed, 37 insertions(+), 34 deletions(-)
diff --git a/data/db_cx.sql b/data/db_cx.sql
index e29cdda..0a1411e 100644
--- a/data/db_cx.sql
+++ b/data/db_cx.sql
@@ -1,37 +1,23 @@
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 concat(gb, '_', name) from bd.dlg_agnp limit 10;
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
-- 鍒犻櫎鍐椾綑鏁版嵁锛屼慨澶峵ab_guid鍊�
select id,name,path,tab,tab_guid from lf.sys_attach where tab != 'bd.b_borehole' order by id desc limit 200;
-select * from lf.sys_meta where name='鍕樻帰鐐瑰湴灞傝〃.xlsx' or type='fbx';
--delete from bs.s_explorationpointstratum where parentid in (select eventid from lf.sys_meta where name='鍕樻帰鐐瑰湴灞傝〃.xlsx');
---delete 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');
-select * from lf.sys_user where uid = 'admin'
-select * from lf.sys_dep order by id
-select * from lf.sys_dir where pid=0
-select * from lf.sys_apply order by id desc
-select * from lf.sys_flow order by id desc;
+
-- delete from lf.sys_apply where id > 47; delete from lf.sys_flow where applyid > 47;
-select * from lf.sys_download order by id desc
-select st_astext(geom) from bs.s_explorationpoint where exppointid in ('XTCK16')
select * from lf.sys_publish 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 case a.userid when 2 then guid else null end "guid", a.* from lf.sys_apply a order by id desc
-
-
-
-
-
-
+select modular2,count(*) from lf.sys_operate group by modular2
@@ -57,15 +43,15 @@
-- 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),
+ 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),
+ 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);
@@ -85,22 +71,29 @@
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),
+ 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),
+ 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);
-comment on table public.coal2000 is '鐓ゅ眰54琛�';
+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 '涓嬪簳楂樺害';
@@ -111,9 +104,19 @@
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
--
Gitblit v1.9.3