白云区拆迁项目 SQL 语句
赋值语句相关
一、拍照点赋值
1、设置拍照点【计量单位、数量】
UPDATE smjdattr SET jldw = NULL WHERE jldw = '0' OR jldw = '';
UPDATE smjdattr SET sl = NULL WHERE sl = '0' OR sl = '';
2、拍照点要素名称转要素代码
UPDATE SMJDATTR SET YSDM = ZLDICVALUE(YSMC,'建构筑物名称') WHERE YSMC NOT NULL OR YSMC <> '';
/*暂时由名称转换为代码，因为移动端采用的是名称。*/
/*UPDATE SMJDATTR SET YSMC = ZLDICNAME(YSDM,'建构筑物名称') WHERE YSDM NOT NULL OR YSDM <> '';*/
3、拍照点赋值给宗地
UPDATE
syqzdattr
SET
GLPZD_OID = (
SELECT
点ID
FROM
(
(
SELECT
z.objectid 面ID, geometry 面
FROM
syqzdattr z
LEFT JOIN geopy ON z.objectid = geopy.objectid
) zrz
LEFT JOIN (
SELECT
s.objectid 点ID, geometry 点, pzdlb
FROM
SMJDATTR s
LEFT JOIN geopt ON s.objectid = geopt.objectid
) pzd ON ST_Within(点, 面) AND pzdlb = 5)
WHERE 面ID = syqzdattr.objectid);
/*将宗地线内拍照点属性【PZDLB】为5的记录的 【OBJECTID】 赋值给宗地的 【GLPZD_OID】*/
/*将所属拍照点的【ZDYBH】赋值给当前宗地的【ZDYBH】属性*/
UPDATE
syqzdattr
SET
zdybh = (
SELECT zdybh FROM smjdattr
WHERE
smjdattr.objectid = glpzd_oid)
WHERE
glpzd_oid IN ( SELECT objectid FROM smjdattr)
4、宗地赋值拍照点
通过叠加分析进行赋值。
5、拍照点流水号赋值
UPDATE
smjdattr
SET
bh = (
SELECT
newbh
FROM
(
SELECT
objectid ID, ogc_id, pzdlb
, ROW_NUMBER() OVER (
PARTITION BY pzdlb1
) newbh
FROM
(
SELECT
*
, CASE
WHEN pzdlb IN(
2, 3, 4) THEN 2
ELSE pzdlb
END pzdlb1
FROM
smjdattr
ORDER BY
ogc_id))
WHERE objectid = id)
WHERE pzdlb <> 5;
6、拍照点【房屋编号】赋值【1建筑物】
UPDATE SMJDATTR SET FWBH= '' WHERE PZDLB <> 1;
UPDATE SMJDATTR SET FWBH= ZDYBH || '-' || BH WHERE PZDLB = 1;
/*将【PZDLB】为 1 的拍照点的【FWBH】赋值为【ZDYBH】-【BH】格式内容，【PZDLB】不为 1 的留空*/
7、拍照点【拍照点构筑物编号】赋值
UPDATE smjdattr SET gzwbh = '' WHERE pzdlb NOT IN(2,3,4);
/*将【PZDLB】不为 2、3、4 的【GZWBH】清空*//*将【PZDLB】为 2、3、4 的【GZWBH】赋值为【ZDYBH】-[BH]*/
UPDATE smjdattr SET gzwbh = zdybh || '-' || bh WHERE pzdlb IN(2,3,4);
/*清除清点图编号内容*/
UPDATE smjdattr SET qdt_bh = '';
/*将清点图编号赋值为【BH】*/
UPDATE smjdattr SET qdt_bh = bh WHERE pzdlb IN(2,3,4);
8、根据要素代码赋值拍照点计量单位
此语句有要素代码定义表格通过函数生成
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '10101';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '10102';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '20101';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '20102';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '20103';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '30101';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '30102';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '30103';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '30104';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40101';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40102';
UPDATE SMJDATTR SET JLDW = '立方米' WHERE YSDM = '40201';
UPDATE SMJDATTR SET JLDW = '立方米' WHERE YSDM = '40202';
UPDATE SMJDATTR SET JLDW = '立方米' WHERE YSDM = '40203';
UPDATE SMJDATTR SET JLDW = '立方米' WHERE YSDM = '40204';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40301';
UPDATE SMJDATTR SET JLDW = '口' WHERE YSDM = '40401';
UPDATE SMJDATTR SET JLDW = '口' WHERE YSDM = '40402';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '40501';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '40502';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '40503';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40601';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40602';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40603';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40701';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40702';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40801';
UPDATE SMJDATTR SET JLDW = '立方米' WHERE YSDM = '40802';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40803';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40804';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '40901';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41001';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41002';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41003';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41004';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '41005';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '41101';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '41102';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '41103';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '41201';
UPDATE SMJDATTR SET JLDW = '根' WHERE YSDM = '41301';
UPDATE SMJDATTR SET JLDW = '根' WHERE YSDM = '41302';
UPDATE SMJDATTR SET JLDW = '根' WHERE YSDM = '41303';
UPDATE SMJDATTR SET JLDW = '根' WHERE YSDM = '41304';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41305';
UPDATE SMJDATTR SET JLDW = '立方米' WHERE YSDM = '41401';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41501';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41601';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41602';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41603';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41604';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41701';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41702';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41703';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41704';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41705';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41706';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41707';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41708';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41709';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '41710';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '41801';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '41802';
UPDATE SMJDATTR SET JLDW = '立方米' WHERE YSDM = '41901';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '42001';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '42002';
UPDATE SMJDATTR SET JLDW = '米' WHERE YSDM = '42101';
UPDATE SMJDATTR SET JLDW = '米' WHERE YSDM = '42102';
UPDATE SMJDATTR SET JLDW = '米' WHERE YSDM = '42103';
UPDATE SMJDATTR SET JLDW = '米' WHERE YSDM = '42201';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '42301';
UPDATE SMJDATTR SET JLDW = '根' WHERE YSDM = '42401';
UPDATE SMJDATTR SET JLDW = '座' WHERE YSDM = '42501';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '42601';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '42701';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '42801';
UPDATE SMJDATTR SET JLDW = '线' WHERE YSDM = '42901';
UPDATE SMJDATTR SET JLDW = '线' WHERE YSDM = '42902';
UPDATE SMJDATTR SET JLDW = '线' WHERE YSDM = '42903';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '42904';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '42905';
UPDATE SMJDATTR SET JLDW = '户' WHERE YSDM = '42906';
UPDATE SMJDATTR SET JLDW = '台' WHERE YSDM = '42907';
UPDATE SMJDATTR SET JLDW = '' WHERE YSDM = '43001';
UPDATE SMJDATTR SET JLDW = '' WHERE YSDM = '43101';
UPDATE SMJDATTR SET JLDW = '穴' WHERE YSDM = '43201';
UPDATE SMJDATTR SET JLDW = '穴' WHERE YSDM = '43202';
UPDATE SMJDATTR SET JLDW = '穴' WHERE YSDM = '43203';
UPDATE SMJDATTR SET JLDW = '具' WHERE YSDM = '43204';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '43301';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '43302';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '43401';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '43501';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '43502';
UPDATE SMJDATTR SET JLDW = '个' WHERE YSDM = '43601';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '43701';
UPDATE SMJDATTR SET JLDW = '棵' WHERE YSDM = '50101';
UPDATE SMJDATTR SET JLDW = '棵' WHERE YSDM = '50102';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50201';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50202';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50203';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50301';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50302';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50303';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50401';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50402';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50403';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50501';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50502';
UPDATE SMJDATTR SET JLDW = '平方米' WHERE YSDM = '50503';
9、根据计量单位赋值拍照点计量方法
UPDATE
smjdattr
SET
jlff = CASE
WHEN jldw IN ('平方米', '㎡') THEN '2'
WHEN jldw = '立方米' THEN '3'
WHEN '米' THEN '4'
ELSE '5' END;
10、设置房屋的【计量单位、方法】
UPDATE smjdattr SET jlff = 1, jldw = '平方米' WHERE substr(YSDM, 1, 1) = '6';
二、宗地属性赋值
1、赋值宗地【是否有简房、构筑物】
UPDATE
syqzdattr
SET
sfyjyfw = (
SELECT
CASE
WHEN count(*) > 0 THEN 1
ELSE 0 END
FROM smjdattr
WHERE pzdlb = 2
AND smjdattr.zdybh = syqzdattr.zdybh);
UPDATE
syqzdattr
SET
sfygzw = (
SELECT
CASE
WHEN count(*) > 0 THEN 1
ELSE 0 END
FROM
smjdattr
WHERE
pzdlb IN(3, 4)
AND smjdattr.zdybh = syqzdattr.zdybh);
2、拍照点更新宗地权利人
UPDATE syqzdattr SET
qlr = (SELECT hzxm
FROM smjdattr s
WHERE GLPZD_OID = s.OBJECTID)
, lxr = qlr
WHERE
(SELECT 1 FROM smjdattr s
WHERE hzxm <> '' AND hzxm NOT NULL AND GLPZD_OID = s.OBJECTID
) = 1;
UPDATE syqzdattr SET
lxdh = (SELECT sjhm FROM smjdattr s
WHERE GLPZD_OID = s.OBJECTID)
WHERE
(SELECT 1 FROM smjdattr s
WHERE sjhm <> '' AND sjhm NOT NULL AND GLPZD_OID = s.OBJECTID) = 1;
UPDATE syqzdattr SET
zl = (SELECT tdzl FROM smjdattr s WHERE GLPZD_OID = s.OBJECTID)
WHERE
(SELECT 1 FROM smjdattr s WHERE pzdlb = 5 AND tdzl <> '' AND tdzl NOT NULL AND s.zdybh = zdybh) = 1;
3、拍照点更新宗地制表信息
update syqzdattr set zjr = (select hzxm from smjdattr s where GLPZD_OID = s.OBJECTID) where (select 1 from smjdattr s where hzxm <> '' and hzxm not null and GLPZD_OID = s.OBJECTID) = 1;
update syqzdattr set dcrq = (select dcsj from smjdattr s where GLPZD_OID = s.OBJECTID) where (select 1 from smjdattr s WHERE dcsj <> '' and dcsj not null and GLPZD_OID = s.OBJECTID) = 1;
update syqzdattr set dcy = (select dcry from smjdattr s where GLPZD_OID = s.OBJECTID) where (select 1 from smjdattr s where dcry <> '' and dcry not null and GLPZD_OID = s.OBJECTID) = 1;
update syqzdattr set clr = (select dcry from smjdattr s where GLPZD_OID = s.OBJECTID) where (select 1 from smjdattr s where dcry <> '' and dcry not null and GLPZD_OID = s.OBJECTID) = 1;
UPDATE syqzdattr SET dcrq = strftime('%Y%m%d') WHERE DCRQ = '' OR DCRQ IS NULL;
/*如果调查日期为空，则设置为当前日期。*/
update syqzdattr set zjrq=dcrq,dcsj=dcrq ,clrq=dcrq ,shrq=strftime('%Y%m%d',datetime(substr(dcrq,1,4) || '-' || substr(dcrq,5,2) || '-' || substr(dcrq,7,2),'+4 day')) where dcrq <> '' and dcrq not NULL;
/*设置时间信息。*/ /*从字典里获取默认信息*/
update syqzdattr set DCY = zlDicName('CLY','小组配置') where DCY = '' or DCY is null;
/*测量员*/
update syqzdattr set CLR = zlDicName('HTY','小组配置') where CLR = '' or CLR is null;
/*绘图员*/
update syqzdattr set SHR = zlDicName('JCY','小组配置') where SHR = '' or SHR is null;
/*审核员*/
update syqzdattr set djdcjgshyj =zlDicName('DJDCJGSHYJ','小组配置') where djdcjgshyj = '' or djdcjgshyj is null;
/*调查结果审核意见*/
update syqzdattr set xmmc = zlDicName('XMMC','小组配置') where xmmc = '' or xmmc is null;
update syqzdattr set CHDW = zlDicName('CLDW','小组配置') where CHDW = '' or CHDW is null;
三、居民面赋值
1、更新房屋面【结构类型】
UPDATE
respyattr
SET
JGLX = (
CASE
Dcode WHEN '0404000230' THEN '3' /*砼房*/
WHEN '0404000330' THEN '5' /*砖房*/
WHEN '0404000430' THEN '9' /*铁*/
WHEN '0404000530' THEN '1' /*钢*/
WHEN '0404000630' THEN '9' /*木*/
WHEN '0404000730' THEN '4' /*混合*/
WHEN '0404000830' THEN '9' /*土*/
WHEN '0404000930' THEN '9' /*石*/
END)
WHERE 1 = 1;
2、复合结构空值自动赋值
update respyattr AS r set FHJGXX = (select case when FWCS > 1 then '1-' || FWCS || 'A全' else '1A全' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404000230' and FHJGXX is null;
update respyattr AS r set FHJGXX = (select case when FWCS > 1 then '1-' || FWCS || 'B全' else '1B全' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404000730' and FHJGXX is null;
update respyattr AS r set FHJGXX = (select case when FWCS > 1 then '1-' || FWCS || 'C全' else '1C全' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404000330' and FHJGXX is null;
update respyattr AS r set FHJGXX = (select case when FWCS > 1 then '1-' || FWCS || 'M全' else '1M全' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404000530' and FHJGXX is null;
update respyattr AS r set FHJGXX = (select case when FWCS > 1 then '2-' || cast(FWCS+1 as text) || '飘楼全' else '2飘楼全' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404002130' and FHJGXX is null;
update respyattr AS r set FHJGXX = (select case when FWCS > 1 then '2-' || cast(FWCS+1 as text) || '不封闭阳台半' else '2不封闭阳台半' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404010630' and FHJGXX is null;
update respyattr AS r set FHJGXX = (select case when FWCS > 1 then '1-' || FWCS || '无上盖室外楼梯半' else '1无上盖室外楼梯半' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404019130' and FHJGXX is null;
3、主体房屋【注记内容】赋值
update respyattr set ZJNR = ZLFHJGXXZH(FHJGXX,1) where FHJGXX NOT NULL AND FHJGXX <> '';
UPDATE respyattr set ZJNR = replace(ZJNR,'M1-1','M') where ZLFHJGXX(FHJGXX,'M',1,4) = 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'N1-1','N') where ZLFHJGXX(FHJGXX,'N',1,4) = 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'A1-1','A') where ZLFHJGXX(FHJGXX,'A',1,4) = 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'B1-1','B') where ZLFHJGXX(FHJGXX,'B',1,4) = 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'C1-1','C') where ZLFHJGXX(FHJGXX,'C',1,4) = 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'M1-','M') where ZLFHJGXX(FHJGXX,'M',1,4) > 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'N1-','N') where ZLFHJGXX(FHJGXX,'N',1,4) > 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'A1-','A') where ZLFHJGXX(FHJGXX,'A',1,4) > 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'B1-','B') where ZLFHJGXX(FHJGXX,'B',1,4) > 1;
UPDATE respyattr set ZJNR = replace(ZJNR,'C1-','C') where ZLFHJGXX(FHJGXX,'C',1,4) > 1;
4、主体房屋更新示意图专用字段
update respyattr set ZJNR_WZSYT = ZJNR WHERE 1=1;
update respyattr AS r set ZJNR_WZSYT = '飘' || (select case when FWCS > 1 then FWCS else '' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404002130'
/*飘楼*/;
update respyattr AS r set ZJNR_WZSYT = '阳' || (select case when FWCS > 1 then FWCS else '' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404010630'
/*阳台*/;
update respyattr AS r set ZJNR_WZSYT = '梯' || (select case when FWCS > 1 then FWCS else '' END from GEOPY where GEOPY.objectid = r.objectid) where DCODE = '0404019130'
/*室外楼梯*/;
5、赋值居民地小编组号
UPDATE
respyattr
SET
xbzh = (
SELECT
groupid
FROM
geopy
WHERE
geopy.objectid = respyattr.objectid)
四、自然幢赋值
1、是否有二调图斑
update zrzattr set sfyedtb = case when edydmj is not null then '1' else '0' end;
2、赋值自然幢面积信息
update zrzattr set scjzmj = (select round(scjzmj ,ZLDICNAME('MJWS','小组配置')) from z where z.fzid = zrzattr.groupid);
update zrzattr set zzdmj = (select round(scmj,ZLDICNAME('MJWS','小组配置')) from geopy where geopy.objectid = zrzattr.objectid);
update zrzattr set zydmj = zzdmj;
3、赋值房屋结构
UPDATE
zrzattr
SET
fwjg = (
SELECT JGLX
FROM
(SELECT 面ID, JGLX
, st_area(ST_Intersection(面, 房)) 面积
FROM
((SELECT z.objectid 面ID, geometry 面
FROM ZRZATTR z
LEFT JOIN geopy ON z.objectid = geopy.objectid
) zrz
LEFT JOIN (
SELECT s.objectid 房ID, geometry 房, jglx
FROM respyattr s
LEFT JOIN geopy ON s.objectid = geopy.objectid
) pzd ON
ST_Contains(面, 房))
ORDER BY 面积 DESC)
WHERE 面ID = zrzattr.objectid AND 面积 > 1 LIMIT 1);
4、由房屋结构更新房屋类别
UPDATE
zrzattr
SET
fwlb = (
SELECT
CASE
fwjg WHEN 1 THEN 'M'
WHEN 2 THEN 'N'
WHEN 3 THEN 'A'
WHEN 4 THEN 'B'
WHEN 5 THEN 'C'
WHEN 6 THEN 'E'
WHEN 7 THEN 'E'
WHEN 8 THEN 'E'
WHEN 9 THEN 'E'
ELSE ''
END)
WHERE 1 = 1;
5、更新自然幢坐落
由叠加分析赋值。
五、宗地面积赋值
1、更新宗地面积信息
update syqzdattr set zdmj = (select round(scmj,zldicname('MJWS','小组配置')) from geopy where geopy.objectid = syqzdattr.objectid);
UPDATE syqzdattr SET JZZMJ = (SELECT ROUND(SUM(SCJZMJ),ZLDICNAME('MJWS','小组配置')) from zrzattr where syqzdattr.objectid = zrzattr.syqzd_oid);
UPDATE syqzdattr SET
jzwzdzMJ = (SELECTROUND(SUM(zzdmj), ZLDICNAME('MJWS', '小组配置'))
FROM zrzattr WHERE syqzdattr.objectid = zrzattr.syqzd_oid);
六、层属构筑物赋值
1、拍照点关联附属构筑物编组表
UPDATE FSGZWBZB AS f SET GLPZD_OID = (SELECT OBJECTID FROM SMJDATTR s WHERE s.PZDLB IN(2,3,4) AND s.GZWGLBH = f.BH);
UPDATE CQLN AS c SET GLPZD_OID = (SELECT GLPZD_OID FROM FSGZWBZB f WHERE f.XBZH = c.XBZH);
PDATE CQPY AS c SET GLPZD_OID = (SELECT GLPZD_OID FROM FSGZWBZB f WHERE f.XBZH = c.XBZH);
UPDATE CQPT AS c SET GLPZD_OID = (SELECT GLPZD_OID FROM FSGZWBZB f WHERE f.XBZH = c.XBZH);
2、拍照点更新关联构筑物字段
UPDATE CQPT SET
/*YSMC = (SELECT YSMC FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
CD = (SELECT CD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
KD = (SELECT KD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
GS = (SELECT GS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
HJ = (SELECT HJ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
BH = (SELECT BH FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
BZ = (SELECT BZ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
CS = (SELECT CS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID);
UPDATE CQLN SET
/*YSMC = (SELECT YSMC FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
CD = (SELECT CD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
KD = (SELECT KD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
GS = (SELECT GS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
HJ = (SELECT HJ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
BH = (SELECT BH FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
BZ = (SELECT BZ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
CS = (SELECT CS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID);
UPDATE CQPY SET
/*YSMC = (SELECT YSMC FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
CD = (SELECT CD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
KD = (SELECT KD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
GS = (SELECT GS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
HJ = (SELECT HJ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
BH = (SELECT BH FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
BZ = (SELECT BZ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
CS = (SELECT CS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID);
3、计算层属构筑物字段
UPDATE cqln SET
cd = round(ST_Length( GeomFromText(wkt)), zldicname('CDWS','小组配置'))
, bmj =ROUND((CASE WHEN gs > 0 THEN cd * gs WHEN hj > 0 THEN cd * hj ELSE bmj END)
,ZLDICNAME('MJWS','小组配置')),sl =round(BMJ * (CASE WHEN cs > 0 THEN CS ELSE 1 END)
,ZLDICNAME('MJWS','小组配置')), tj = ROUND((CASE WHEN gs > 0 AND hj > 0 THEN sl * hj ELSE NULL END)
,ZLDICNAME('TJWS','小组配置'));
UPDATE CQLN SET SL = CD where sl = '' or sl is null;
UPDATE cqpy SET
sptymj = round(ST_Area(GeomFromText(wkt)), ZLDICNAME('MJWS','小组配置'))
, sl =round(sptymj * (CASE WHEN cs > 0 THEN cs ELSE 1 END),ZLDICNAME('MJWS','小组配置'))
, tj =round((CASE WHEN gs > 0 THEN sl * gs ELSE NULL END),ZLDICNAME('TJWS','小组配置'));
4、层属构筑物明细编号赋值
UPDATE
CQPT
SET
bh = (
SELECT
newbh
FROM
(SELECT
OBJECTID ID, ogc_id, XBZH
, ROW_NUMBER() OVER (PARTITION BY XBZH
ORDER BY OBJECTID) newbh
FROM
(SELECT OBJECTID, ogc_id, XBZH FROM CQPT
UNION
SELECT OBJECTID, ogc_id, XBZH FROM CQLN
UNION
SELECT OBJECTID, ogc_id, XBZH FROM CQPY)
WHERE XBZH NOT NULL AND XBZH <> '')
WHERE OBJECTID = ID);
UPDATE
CQLN
SET
bh = (
SELECT
newbh
FROM
(SELECT
OBJECTID ID, ogc_id, XBZH
, ROW_NUMBER() OVER (PARTITION BY XBZH
ORDER BY OBJECTID) newbh
FROM
(SELECT OBJECTID, ogc_id, XBZH FROMCQPT
UNION
SELECT OBJECTID, ogc_id, XBZH FROM CQLN
UNION
SELECT OBJECTID, ogc_id, XBZH FROM CQPY)
WHERE XBZH NOT NULL AND XBZH <> '')
WHERE OBJECTID = ID);
UPDATE
CQPY
SET
bh = (
SELECT
newbh
FROM
(SELECT
OBJECTID ID, ogc_id, XBZH
, ROW_NUMBER() OVER ( PARTITION BY XBZH
ORDER BY OBJECTID) newbh
FROM
(SELECT OBJECTID, ogc_id, XBZH FROM CQPT
UNION
SELECT OBJECTID, ogc_id, XBZH FROM CQLN
UNION
SELECT OBJECTID, ogc_id, XBZH FROM CQPY)
WHERE XBZH NOT NULL AND XBZH <> '')
WHERE OBJECTID = ID);
七、地面构筑物赋值
1、拍照点关联地面构筑物
UPDATE GZWDATTR AS g SET GLPZD_OID =(SELECT OBJECTID 点ID FROM SMJDATTR s WHERE s.PZDLB IN(2,3,4) AND s.GZWGLBH = g.BH) WHERE 1=1;
UPDATE GZWXATTR AS g SET GLPZD_OID =(SELECT OBJECTID 点ID FROM SMJDATTR s WHERE s.PZDLB = 4 AND s.GZWGLBH = g.BH) WHERE 1=1;
UPDATE GZWATTR AS g SET GLPZD_OID = (SELECT OBJECTID 点ID FROM SMJDATTR s WHERE s.PZDLB = 4 AND s.GZWGLBH = g.BH) WHERE 1=1;
2、拍照点更新关联地面构筑物字段
UPDATE GZWDATTR SET
/*MC = (SELECT YSMC FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
CD = (SELECT CD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
KD = (SELECT KD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
GS = (SELECT GS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
HJ = (SELECT HJ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
/*BH = (SELECT BH FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
/*BZ = (SELECT BZ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
CS = (SELECT CS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID);
UPDATE GZWXATTR SET
/*MC = (SELECT YSMC FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
CD = (SELECT CD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
KD = (SELECT KD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
GS = (SELECT GS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
HJ = (SELECT HJ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
/*BH = (SELECT BH FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
/*BZ = (SELECT BZ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
CS = (SELECT CS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID);
UPDATE GZWATTR SET
/*GZWMC = (SELECT YSMC FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
CD = (SELECT CD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
KD = (SELECT KD FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
GS = (SELECT GS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
HJ = (SELECT HJ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
/*BH = (SELECT BH FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),*/
BZ = (SELECT BZ FROM SMJDATTR WHERE GLPZD_OID= OBJECTID),
CS = (SELECT CS FROM SMJDATTR WHERE GLPZD_OID= OBJECTID);
3、计算地面构筑物字段
UPDATE
GZWXATTR AS C SET
CD =(
SELECT
ROUND(ST_Length(GEOMETRY), ZLDICNAME('CDWS', '小组配置'))
FROM GEOLN G
WHERE G.OBJECTID = C.OBJECTID)
, BMJ = ROUND((
CASE
WHEN gs > 0 THEN CD * gs
WHEN HJ > 0 THEN CD * HJ
ELSE BMJ END)
, ZLDICNAME('MJWS', '小组配置'))
, MJ = ROUND(BMJ * (
CASE
WHEN CS > 0 THEN CS
ELSE 1 END)
, ZLDICNAME('MJWS', '小组配置'))
, TJ = ROUND((
CASE
WHEN GS > 0
AND HJ > 0 THEN MJ * HJ
ELSE NULL END)
, ZLDICNAME('TJWS', '小组配置'));
UPDATE
GZWATTR AS C SET
BMJ =(
SELECT
ROUND(ST_Area(GEOMETRY), ZLDICNAME('MJWS', '小组配置'))
FROM GEOPY G
WHERE G.OBJECTID = C.OBJECTID)
, MJ = ROUND(
BMJ * (
CASE
WHEN CS > 0 THEN CS
ELSE 1 END)
, ZLDICNAME('MJWS', '小组配置'))
, TJ = ROUND((
CASE
WHEN GS > 0 THEN MJ * GS
ELSE NULL END)
, ZLDICNAME('TJWS', '小组配置'));
八、无图拍照点计算
1、无图形构筑物计算
UPDATE
SMJDATTR
SET
BMJ = CASE
WHEN GS > 0 THEN CD * GS
WHEN HJ > 0 THEN CD * HJ
ELSE BMJ
END
, TJ = CASE
WHEN GS > 0
AND HJ > 0 THEN BMJ * HJ
ELSE TJ END
WHERE PZDLB IN('2', '3', '4') AND (GZWGLBH = '' OR GZWGLBH IS NULL);
九、更新拍照点信息
1、反更新拍照点计算字段【房屋】
UPDATE
smjdattr
SET
BMJ =(SELECT ZYDMJ FROM zrzattr WHERE zrzattr.glpzd_OID = smjdattr.objectid)
, ZMJ =(SELECT scjzmj FROM zrzattr WHERE zrzattr.glpzd_OID = smjdattr.objectid)
WHERE pzdlb = 1 AND objectid IN (SELECT glpzd_oid FROM zrzattr);
2、反更新拍照点计算字段【层属构筑物】
UPDATE
smjdattr
SET
CD =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQLN
) THEN (
SELECT SUM(CD) FROM CQLN WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE CD END)
, TJ =(
CASE WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQLN
) THEN (
SELECT SUM(TJ) FROM CQLN WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE TJ END)
, BMJ =(
CASE
WHEN OBJECTID IN( SELECT GLPZD_OID FROM CQLN
) THEN (
SELECT SUM(BMJ) FROM CQLN WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE BMJ END)
, ZMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQPY
) THEN (
SELECT SUM(SL) FROM CQPY WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE ZMJ END);
UPDATE
smjdattr
SET
CD =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQPT
) THEN (
SELECT SUM(CD) FROM CQPT
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE CD END
)
, TJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQPT
) THEN (
SELECT SUM(TJ) FROM CQPT
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE TJ END
)
, BMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQPT
) THEN (
SELECT SUM(BMJ) FROM CQPT
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE BMJ END
)
, ZMJ =(
CASE
WHEN OBJECTID IN(
SELECT GLPZD_OID FROM CQPY
) THEN (
SELECT SUM(SL) FROM CQPY
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE ZMJ END);
UPDATE
smjdattr
SET
CD =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQPY
) THEN (
SELECT SUM(CD) FROM CQPY WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE CD END)
, TJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQPY
) THEN (
SELECT SUM(TJ) FROM CQPY
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE TJ END)
, BMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM CQPY
) THEN (
SELECT SUM(BMJ) FROM CQPY
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE BMJ END)
, ZMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID
FROM CQPY
) THEN (
SELECT SUM(SL) FROM CQPY
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE ZMJ END);
3、反更新拍照点计算字段【地面构筑物】
UPDATE
smjdattr
SET
CD =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWXATTR
) THEN (
SELECT SUM(CD) FROM GZWXATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE CD END)
, TJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWXATTR
) THEN (
SELECT SUM(TJ) FROM GZWXATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE TJ END)
, BMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWXATTR
) THEN (
SELECT SUM(BMJ) FROM GZWXATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE BMJ END)
, ZMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWXATTR
) THEN (
SELECT SUM(MJ) FROM GZWXATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE ZMJ END)
WHERE PZDLB = 4;
UPDATE
smjdattr
SET
CD =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWATTR
) THEN (
SELECT SUM(CD) FROM GZWATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE CD END)
, TJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWATTR
) THEN (
SELECT SUM(TJ) FROM GZWATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE TJ END)
, BMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWATTR
) THEN (
SELECT SUM(BMJ) FROM GZWATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE BMJ END)
, ZMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWATTR
) THEN (
SELECT SUM(MJ) FROM GZWATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE ZMJ END)
WHERE PZDLB = 4;
UPDATE
smjdattr
SET
CD =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWDATTR
) THEN (
SELECT SUM(CD) FROM GZWDATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE CD END)
, TJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWDATTR
) THEN (
SELECT SUM(TJ) FROM GZWDATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE TJ END)
, BMJ =(
CASE
WHEN OBJECTID IN(SELECT GLPZD_OID FROM GZWDATTR
) THEN (
SELECT SUM(BMJ) FROM GZWDATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE BMJ END)
, ZMJ =(
CASE
WHEN OBJECTID IN( SELECT GLPZD_OID FROM GZWDATTR) THEN (
SELECT
SUM(MJ) FROM GZWDATTR
WHERE smjdattr.OBJECTID = GLPZD_OID)
ELSE ZMJ END)
WHERE PZDLB = 4;
4、拍照点【数量】字段赋值
UPDATE SMJDATTR SET SL = CASE JLFF WHEN 1 THEN ZMJ WHEN 2 THEN ZMJ WHEN 3 THEN TJ WHEN 4 THEN CD ELSE SL END WHERE 1=1;
5、赋值拍照点【自动计算规格】
此语句有要素代码定义表格通过函数生成
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '10101';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '10102';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '20101';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '20102';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '20103';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '30101';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '30102';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '30103';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '30104';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '40101';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '40102';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米' where ysdm = '40201';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米' where ysdm = '40202';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米' where ysdm = '40203';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米' where ysdm = '40204';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '40301';
update smjdattr set zdjs_gg = '口径' || hj || '米、深度' || gs || '米' where ysdm = '40401';
update smjdattr set zdjs_gg = '口径' || hj || '米、深度' || gs || '米' where ysdm = '40402';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米' where ysdm = '40501';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米' where ysdm = '40502';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米' where ysdm = '40503';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米、厚度' || hj || '米' where ysdm = '40601';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米、厚度' || hj || '米' where ysdm = '40602';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米、厚度' || hj || '米' where ysdm = '40603';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米、厚度' || hj || '米' where ysdm = '40701';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米、厚度' || hj || '米' where ysdm = '40702';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米、厚度' || hj || '米' where ysdm = '40801';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '40802';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '40803';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '40804';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '40901';
update smjdattr set zdjs_gg = '宽' || hj || '米' where ysdm = '41002';
update smjdattr set zdjs_gg = '宽' || hj || '米' where ysdm = '41003';
update smjdattr set zdjs_gg = '伸长度' || cd || '米' where ysdm = '41004';
update smjdattr set zdjs_gg = '宽' || hj || '米*高' || gs || '米' where ysdm = '41005';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '41101';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '41102';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '41103';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '41201';
update smjdattr set zdjs_gg = '高' || gs || '米' where ysdm = '41301';
update smjdattr set zdjs_gg = '高' || gs || '米' where ysdm = '41302';
update smjdattr set zdjs_gg = '高' || gs || '米' where ysdm = '41303';
update smjdattr set zdjs_gg = '高' || gs || '米' where ysdm = '41304';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '41401';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41701';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41702';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41703';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41704';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41705';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41706';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41707';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41708';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41709';
update smjdattr set zdjs_gg = '直径' || hj || '米、高' || gs || '米' where ysdm = '41710';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '41801';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '41802';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米*直径' || hj || '米' where ysdm = '41901';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '42001';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '42002';
update smjdattr set zdjs_gg = '长' || cd || '米、直径' || hj || '米' where ysdm = '42101';
update smjdattr set zdjs_gg = '长' || cd || '米、直径' || hj || '米' where ysdm = '42102';
update smjdattr set zdjs_gg = '长' || cd || '米、直径' || hj || '米' where ysdm = '42103';
update smjdattr set zdjs_gg = '长' || cd || '米、直径' || hj || '米' where ysdm = '42201';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '42601';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '42701';
update smjdattr set zdjs_gg = '内架棚高' || gs || '米' where ysdm = '43301';
update smjdattr set zdjs_gg = '内架棚高' || gs || '米' where ysdm = '43302';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '43401';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '43501';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '43502';
update smjdattr set zdjs_gg = '长' || cd || '米*宽' || hj || '米*高' || gs || '米' where ysdm = '43601';
update smjdattr set zdjs_gg = '长' || cd || '米*高' || gs || '米' where ysdm = '43701';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
update smjdattr set zdjs_gg = '据构筑物示意图尺寸' where ysdm = '';
成果输出相关
一、封面
无相关 SQL 语句，直接从【使用权宗地】读取。
二、地上附着物清点汇总表
1. 与宗地关联关系
SYQZDATTR.ZDYBH = 白云区地上附着物测绘清点汇总表.ZDYBH
2. 数据提取语句
表名： 白云区地上附着物测绘清点汇总表
【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用
SELECT * FROM
(
/*汇总拍照点类别为 1 的房屋信息，单位为 ㎡ ，数量从 SL 字段合计，类型设置为 1 */
SELECT
'房屋' AS DL, ZDYBH, YSDM, YSMC, YSMC AS YSMC_XS, JLDW AS DW
, SUBSTR(sum(SL)+ ZLDICNAME('WSCS', '小组配置'), 1
, charindex('.', sum(SL)+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('MJWS', '小组配置')) SL
, '1' AS LX
, SUBSTR(SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'), 1
, charindex('.', SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('MJWS', '小组配置')) 合计
, BZ AS BZ
FROM smjdattr WHERE PZDLB = 1 GROUP BY ZDYBH, YSMC
UNION
SELECT
'按面积统计附着物' AS DL, ZDYBH, YSDM, YSMC, YSMC AS YSMC_XS, JLDW AS DW
, SUBSTR(sum(SL)+ ZLDICNAME('WSCS', '小组配置'), 1
, charindex('.', sum(SL)+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('MJWS', '小组配置')) SL
, '2' AS LX
, SUBSTR(SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'), 1
, charindex('.', SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('MJWS', '小组配置')) 合计
, BZ AS BZ
FROM smjdattr WHERE PZDLB = 2 OR ( PZDLB IN(3, 4) AND JLFF IN(1, 2)) GROUP BY ZDYBH, YSMC, GG
UNION
SELECT
'按体积统计附着物' AS DL, ZDYBH, YSDM, YSMC, JLDW AS DW, YSMC AS YSMC_XS
, SUBSTR(sum(SL)+ ZLDICNAME('WSCS', '小组配置'), 1
, charindex('.', sum(SL)+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('TJWS', '小组配置')) SL
, '4' AS LX
, SUBSTR(SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'), 1
, charindex('.', SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('TJWS', '小组配置')) 合计
, BZ AS BZ
FROM SMJDATTR WHERE PZDLB IN(3, 4) AND JLFF = 3 GROUP BY ZDYBH, YSMC, GG
UNION
SELECT
'按长度统计附着物' AS DL, ZDYBH, YSDM, YSMC, YSMC AS YSMC_XS, JLDW AS DW
, SUBSTR(sum(SL)+ ZLDICNAME('WSCS', '小组配置'), 1
, charindex('.', sum(SL)+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('CDWS', '小组配置')) SL
, '5' AS LX
, SUBSTR(SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'), 1
, charindex('.', SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('CDWS', '小组配置')) 合计
, BZ AS BZ
FROM SMJDATTR WHERE PZDLB IN(3, 4) AND JLFF = 4 GROUP BY ZDYBH, YSMC, GG
UNION
SELECT
'按个数统计附着物' AS DL, ZDYBH, YSDM, YSMC, YSMC AS YSMC_XS, JLDW AS DW
, sum(SL) SL
, '6' AS LX
, SUM(sl) OVER() 合计
, BZ AS BZ
FROM SMJDATTR WHERE PZDLB IN(3, 4) AND JLFF = 5 GROUP BY ZDYBH, YSMC, GG
) t ORDER BY t.LX
三、房屋测绘调查明细表
1. 与宗地关联关系
SYQZDATTR.OBJECTID = 自然幢排序.DBZH
自然幢排序.OBJECTID = ZRZATTR.OBJECTID ORDER BY ZRZATTR.排序号
2. 数据提取语句
表名：自然幢排序
/*根据编号排序*/
select bh as 排序号,* from zrzattr order by bh
四、构筑物、附属物及青苗清点明细表
1.与宗地关联关系
SYQZDATTR.ZDYBH = 清点表1.ZDYBH
2.数据提取语句
表名：清点表1
【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用
SELECT
ZDYBH
, ZLDICNAME(substr(YSDM, 1, 1), '建构筑物名称') 一级类
, ZLDICNAME(substr(YSDM, 1, 3), '建构筑物名称') 二级类
, YSMC 三级类
, YSMC AS YSMC_XS
, JLDW
, sum(SL) SL
,(CASE WHEN GG = '' OR GG IS NULL THEN ZDJS_GG ELSE GG END) GG1
, BZ, BH, pzdlb
FROM
smjdattr
GROUP BY
ZDYBH, YSMC, GG1, BH
HAVING
pzdlb IN(2, 3, 4) AND YSMC IS NOT NULL
ORDER BY
zdybh
, substr(ZLDICVALUE(YSMC, '建构筑物名称'), 1, 3)
, ZLPADLEFT(BH, '0', 3)
五、位置示意图
无相关 SQL 语句，直接从【使用权宗地】读取。
六、房屋测绘平面图
1.与宗地关联关系
SYQZDATTR.OBJECTID = ZRZATTR.DBZH
ZRZATTR.GROUPID = GEOPY.GROUPID
GEOPY.OBJECTID = 房屋分层分类统计.OBJECTID
2.数据提取语句
表名：房屋分层分类统计
SELECT
*
, CASE
WHEN JSXS = 0 THEN '高度不够' || X'0D' || X'0A' || '不计算面积'
ELSE CAST(ROUND(JZMJ, 4) AS text) END JZMJ_STR
FROM
(SELECT
RP.DBZH, RP.XBZH, t.OBJECTID, t.DCODE
, ROW_NUMBER() OVER(PARTITION BY RP.XBZH ORDER BY MC) BH
, GEOPY.FWCS
, CASE
WHEN MC LIKE 'M%' THEN 'M' || ifnull( GEOPY.FWCS, '')
WHEN MC LIKE 'N%' THEN 'N' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'B%' THEN 'B' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'A%' THEN 'A' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'C%' THEN 'C' || ifnull(GEOPY.FWCS, '')
ELSE REPLACE(MC, '1-1', '')
END MC
, JSXS, SXH, CS, SZC, Round(GEOPY.SCMJ, 4)
* (CASE WHEN MC LIKE 'M%' OR MC LIKE 'N%' OR MC LIKE 'A%' OR MC LIKE 'B%' OR MC LIKE 'C%'
THEN ifnull(GEOPY.FWCS, 1) ELSE CS END) * JSXS JZMJ
FROM
(SELECT
*
FROM
(
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'C', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'C', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'C', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'C', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'C', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'B', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'B', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'B', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'B', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'B', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'A', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'A', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'A', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'A', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'A', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 1, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 1, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 2, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 2, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 5) SZC
FROM
respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 1) MC
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 4) CS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '夹层', 1, 1) MC
, ZLFHJGXX(FHJGXX, '夹层', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '夹层', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '夹层', 1, 4) CS
, ZLFHJGXX(FHJGXX, '夹层', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '檐廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '檐廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '檐廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '门廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '门廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '门廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '门廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '门廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '挑廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '挑廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '挑廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 5) SZC
FROM respyattr
)
WHERE
instr(SZC, '.') = 0) t
JOIN (SELECT OBJECTID, DBZH, xbzh FROM respyattr) RP
ON t.objectid = RP.objectid
JOIN GEOPY ON t.objectid = GEOPY.objectid
WHERE MC IS NOT NULL AND xbzh IS NOT NULL
) WHERE JSXS > 0
七、构筑物测绘平面图（依附于建筑物）
1.与宗地关联关系
SYQZDATTR.OBJECTID = ZRZATTR.DBZH
ZRZATTR.GROUPID = FSGZWBZB.FZID
FSGZWBZB.XBZH = GROUPFEATUREPRINTINFO.GROUPVALUE
GROUPFEATUREPRINTINFO.PRINTEXTENTOBJECTID = FEATUREPRINTINFO.OBJECTID
/*模板内关系表*/
FEATUREPRINTINFO.OBJECTID = 层属构筑物明细.PRINTEXTENTOBJECTID
2.数据提取语句
表名：层属构筑物明细
【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用
SELECT
PrintExtentObjectID
, dbzh, 名称, YSMC_XS, 长度, 宽度, 高度
, '<' || 编号 || '>' 编号
, 厚度, 水平投影面积, 数量, count(*) 坐标数量
, h.sl 角点数量, 类型, 备注, 关联拍照点ID
, 总数量, 计量单位, 计量方法
FROM
(SELECT
p.PrintExtentObjectID, YSMC_XS
, t.DBZH, t.LX AS 类型, t.CD 长度, t.KD 宽度
, t.GS 高度, t.BH 编号, t.HJ 厚度, t.SPTYMJ 水平投影面积
, 数量, t.YSMC 名称, t.BZ 备注, t.XBZH
, t.表名, f.GLPZD_OID 关联拍照点ID
, s.SL 总数量, s.JLDW 计量单位, s.JLFF 计量方法
FROM
(SELECT
OBJECTID, GLPZD_OID, '点' AS LX
, CD, KD, GS, BH, HJ, SPTYMJ, YSMC, YSMC AS YSMC_XS
, BZ, DBZH, XBZH, 'CQPT' AS 表名, SL 数量
FROM CQPT
UNION
SELECT
OBJECTID, GLPZD_OID, '线' AS LX
, CD, KD, GS, BH, HJ, SPTYMJ, YSMC, YSMC AS YSMC_XS
, BZ, DBZH, XBZH, 'CQLN' AS 表名, SL 数量
FROM CQLN
UNION
SELECT
OBJECTID, GLPZD_OID, '面' AS LX
, CD, KD, GS, BH, HJ, SPTYMJ, YSMC, YSMC AS YSMC_XS
, BZ, DBZH, XBZH, 'CQPY' AS 表名, SL 数量
FROM CQPY
) t
JOIN (SELECT
DISTINCT GroupValue, PrintExtentObjectID
FROM GroupFeaturePrintInfo
WHERE GroupValue IS NOT NULL AND GroupValue <> ''
) p
ON t.xbzh = p.GroupValue
JOIN (SELECT
XBZH, GLPZD_OID
FROM FSGZWBZB
) f
ON t.XBZH = f.XBZH
JOIN (SELECT
OBJECTID, SL, JLDW, JLFF
FROM SMJDATTR
) s
ON f.GLPZD_OID = s.OBJECTID
) t1
LEFT JOIN (SELECT
dyid, count(*) sl
FROM MBTYANNLN
WHERE dcode = '910100821' /*坐标注记代码*/
GROUP BY dyid
) h
ON h.dyid = t1.PrintExtentObjectID
GROUP BY
t1.PrintExtentObjectID
, t1.dbzh, t1.名称, t1.长度, t1.宽度
, t1.高度, t1.编号, t1.厚度, t1.水平投影面积
, t1.类型, t1.备注
ORDER BY 编号
八、构筑物测绘平面图（地面）
1.与宗地关联关系
SYQZDATTR.OBJECTID = ZRZATTR.DBZH
SYQZDATTR.ZDYBH = 地面构筑物拍照点.ZDYBH
2.数据提取语句
表名：地面构筑物拍照点
【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用
SELECT
ZDYBH, bh, gzwbh, '(' || bh || ')' xbh, ysmc,ysmc AS YSMC_XS, sl
|| CASE jlff
WHEN 1 THEN '㎡'
WHEN 2 THEN '㎡'
WHEN 3 THEN '?'
WHEN 4 THEN 'm'
ELSE jldw END value
FROM smjdattr
WHERE pzdlb = 4 AND gzwglbh <> '' AND gzwglbh NOT NULL
九、房屋照片
1.与宗地关联关系
SYQZDATTR.OBJECTID = ZRZATTR.DBZH
SMJDATTR.OBJECTID = 白云区房屋照片.YS_OBJECTID
2.数据提取语句
表名：白云区房屋照片
SELECT
sw.*
, SD.YSMC, SD.PZDLB, SD.ZDYBH, SD.FWBH, SD.BH
, CASE
WHEN SW.WJMC LIKE '%门牌照片%' THEN 1
WHEN SW.WJMC LIKE '%正面照片%' THEN 2
WHEN SW.WJMC LIKE '%侧面照片%' THEN 3
WHEN SW.WJMC LIKE '%内部照片%' THEN 4
ELSE 5 END ZPLX
, CASE
WHEN SW.WJMC LIKE '%门牌照片%' THEN '房屋门牌'
WHEN SW.WJMC LIKE '%正面照片%' THEN '房屋正面'
WHEN SW.WJMC LIKE '%侧面照片%' THEN '房屋侧面'
WHEN SW.WJMC LIKE '%内部照片%' THEN '房屋内部'
ELSE '其他位置' END ZPLXMC
FROM smjwjxxb sw
JOIN smjdattr sd
ON sw.ys_objectid = sd.objectid
WHERE PZDLB = 1
ORDER BY fwbh COLLATE zlCompareCharNum
十、其它附着物照片
1.与宗地关联关系
SYQZDATTR.ZDYBH = 白云区构筑物照片1.ZDYBH
2.数据提取语句
表名：白云区构筑物照片1
【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用
SELECT
sw.*
, SD.YSMC, SD.YSMC_XS, SD.PZDLB, SD.ZDYBH, SD.BH
, CASE
WHEN sw.sl > 1 THEN YSMC_XS || '-' || SXH || '(编号' || BH || ')'
WHEN sw.sl = 1 THEN YSMC_XS || '(编号' || BH || ')' END YSM
FROM
(SELECT
ROW_NUMBER() OVER(PARTITION BY t.YS_OBJECTID) SXH
, smjwjxxb .*, t.sl
FROM smjwjxxb
JOIN
(SELECT
YS_objectid, count(*) sl
FROM smjwjxxb
GROUP BY YS_objectid
) t ON smjwjxxb.YS_objectid = t.YS_objectid
) sw
JOIN
(SELECT YSMC AS YSMC_XS,* FROM smjdattr) sd
ON sw.ys_objectid = sd.objectid
WHERE PZDLB IN(3, 4)
十一、简易房屋照片
1.与宗地关联关系
SYQZDATTR.ZDYBH = 白云区简易房屋照片.ZDYBH
2.数据提取语句
表名：白云区简易房屋照片
【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用
SELECT
sw.*
, SD.YSMC, SD.YSMC AS YSMC_XS, SD.PZDLB, SD.ZDYBH, SD.BH
, '简易结构' || '(编号' || BH || ')' YSM
FROM
smjwjxxb sw
JOIN smjdattr sd ON
sw.ys_objectid = sd.objectid
WHERE
PZDLB = 2
本文来自博客园，作者：AiZhenVIP，转载请注明原文链接：https://www.cnblogs.com/aizhen/p/16595933.html
浙公网安备 33010602011771号