| | |
| | | <select id="countProjectType" resultType="java.util.Map"> |
| | | SELECT a.type, |
| | | count(a.type) AS number, |
| | | coalesce( cast(sum(a.sizes) as decimal(12, 2) ), 0) AS capacity |
| | | coalesce( cast(sum(a.sizes) as decimal(20, 2) ), 0) AS capacity |
| | | FROM lf.sys_meta a |
| | | INNER JOIN lf.sys_dir b ON a.dircode = b.code |
| | | WHERE a.dircode LIKE '${projectCode}%' |
| | |
| | | select '测绘(ESV)' "m1", '数字线划图' "m2", '平方千米' "m3", coalesce(sum(area), 0) "sizes" from lf.sys_line_buffer |
| | | union all |
| | | select '测绘(ESV)' "m1", '数字正射影像图' "m2", '平方千米' "m3", ( |
| | | select coalesce(sum(b.area), 0) from lf.sys_meta b |
| | | select cast( coalesce(sum(b.area), 0) / 1000000 as decimal(20, 2)) from lf.sys_meta b |
| | | inner join lf.sys_dir c on b.dircode = c.code |
| | | where c.name = '数字正射影像图' and b.type in ('img', 'tif', 'tiff') ) "sizes" |
| | | union all |
| | | select '测绘(ESV)' "m1", '管线数据' "m2", '千米' "m3", |
| | | cast( coalesce(sum( ST_Length( ST_GeographyFromText( ST_AsText(geom) ) ) ) / 1000, 0) as decimal(12, 2) ) "sizes" from bs.m_pipeline |
| | | cast( coalesce(sum( ST_Length( ST_GeographyFromText( ST_AsText(geom) ) ) ) / 1000, 0) as decimal(20, 2) ) "sizes" from bs.m_pipeline |
| | | union all |
| | | select '勘察(EGE)' "m1", '勘探点' "m2", '个' "m3", count(*) "sizes" from bs.s_explorationpoint |
| | | union all |
| | | select '勘察(EGE)' "m1", '三维地质模型' "m2", '平方米' "m3", ( |
| | | select coalesce(sum(b.area), 0) from lf.sys_meta b |
| | | inner join lf.sys_dir c on b.dircode = c.code |
| | | where b.type in ('fbx', 'ifc') ) "sizes" |
| | | select '勘察(EGE)' "m1", '三维地质模型' "m2", '平方千米' "m3", ( |
| | | select cast( coalesce(sum(b.area), 0) / 1000000 as decimal(20, 2)) |
| | | from lf.sys_meta b |
| | | where b.type in ('fbx', 'ifc') ) "sizes" |
| | | union all |
| | | select '地灾(EGD)' "m1", '地灾点' "m2", '个' "m3", count(*) "sizes" from bd.b_geologic_hazard; |
| | | </select> |
| | |
| | | |
| | | <select id="selectPipeAnalysis" resultType="com.lf.server.entity.show.PipelineEntity"> |
| | | 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, 2) ) AS acrossLength, |
| | | cast( st_length( st_geographyfromtext( st_astext( st_intersection(ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) as decimal(20, 2) ) AS acrossLength, |
| | | st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) as wkt |
| | | from ${tab} as a, (select segname, remarks, pipename, geom from bs.m_pipesegment where gid = #{gid} ) as b |
| | | where ST_Intersects(a.geom, b.geom) |