第一次自动生成架空导线

最新推荐文章于 2024-07-29 12:56:42 发布

gis_ling

最新推荐文章于 2024-07-29 12:56:42 发布

阅读量766

点赞数

分类专栏： G/Technology模形文章标签： triggers attributes table insert alignment null

本文链接：https://blog.csdn.net/gis_ling/article/details/764301

版权

G/Technology模形专栏收录该内容

3 篇文章 0 订阅

订阅专栏

//
//
//改两处'团东甲线'和三处'2AB040'
//
//
--停止触发器
ALTER TABLE B$COMMON_N DISABLE ALL TRIGGERS;
ALTER TABLE b$CONNECTIVITY_N DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_N DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LN DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LN_SDOGEOM DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LB DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LB_SDOGEOM DISABLE ALL TRIGGERS;

declare
i number;
flag number; --标记是否为开始杆塔
flag_label number; --控制线路标注(现在为空一个标一个)
v_lable_sdo_x2   B$SD_JKDX_LN_SDOGEOM.sdo_x2%type;
v_lable_sdo_y2   B$SD_JKDX_LN_SDOGEOM.sdo_y2%type;
v_symbol_sdo_x1 B$SD_JKDX_LN_SDOGEOM.sdo_x1%type;
v_symbol_sdo_y1 B$SD_JKDX_LN_SDOGEOM.sdo_y1%type;
v_symbol_sdo_x2 B$SD_JKDX_LN_SDOGEOM.sdo_x2%type;
v_symbol_sdo_y2 B$SD_JKDX_LN_SDOGEOM.sdo_y2%type;
v_prv_gt_sdo_x1 B$SD_GT_PT_SDOGEOM.sdo_x1%type;
v_prv_gt_sdo_y1 B$SD_GT_PT_SDOGEOM.sdo_y1%type;
v_gt_sdo_x1      B$SD_GT_PT_SDOGEOM.sdo_x1%type;
v_gt_sdo_y1      B$SD_GT_PT_SDOGEOM.sdo_y1%type;

v_prv_gth B$SD_GT_N.gth%type;
v_gth B$SD_GT_N.gth%type;

v_g3e_fid        b$common_N.g3e_fid%type;
v_gt_g3e_fid     b$common_N.g3e_fid%type;
new_g3e_fid      b$common_n.g3e_fid%type;
new_jkdxlabel_g3e_id    b$common_n.g3e_id%type;
new_jkdxsymbol_g3e_id   b$common_n.g3e_id%type;

--标注的角度，通过架空导线的两个坐标求得
v_sdo_orientation B$SD_JKDX_LN_SDOGEOM.SDO_ORIENTATION%type;
v_x B$SD_JKDX_LN_SDOGEOM.sdo_x1%type;
v_y B$SD_JKDX_LN_SDOGEOM.sdo_y1%type;

v_node1_id       B$connectivity_n.node1_id%type;
v_node2_id       B$connectivity_n.node2_id%type;
v_owner_id1      b$common_n.owner1_id%type;
v_owner_id2      b$common_n.owner2_id%type;
v_old_owner_id   b$common_n.owner1_id%type;
v_old_node_id    b$connectivity_n.node2_id%type;

--选出某线路的所有杆塔 ,杆塔的选出顺序从左到右，通过ORDER BY GTH DESC控制。左边大号用desc
CURSOR c1 IS SELECT G3E_FID,GTH FROM B$SD_GT_N WHERE CD_XLMC='22B213' ORDER BY GTH;

begin
flag:=0; --第一个杆塔时为0，否则为1
flag_label:=1; --为1时架空导线有标注，为0时架空导线没有标注
v_old_owner_id:=0;
v_old_node_id:=0;
v_g3e_fid:=23727920; --作为参考的架空导线，参考的架空导线的所有属性将被复制到新设备了
v_prv_gth:=0;
v_gth:=0;
FOR c IN c1 LOOP
v_gt_g3e_fid:=c.g3e_fid;

--赋所属关系的值
v_owner_id1:=v_old_owner_id;
SELECT g3e_id INTO v_owner_id2 FROM B$COMMON_N WHERE g3e_fid=v_gt_g3e_fid;
v_old_owner_id:=v_owner_id2;

IF flag!=0 THEN
--赋连接关系的值
v_node1_id:=v_old_node_id;
SELECT g3e_node_seq.nextval INTO v_node2_id FROM dual;
v_old_node_id:=v_node2_id;
--取新加设备的g3e_fid与其它值
SELECT g3e_fid_seq.nextval INTO new_g3e_fid FROM dual;
SELECT sd_jkdx_ln_seq.nextval INTO new_jkdxsymbol_g3e_id FROM dual;
SELECT sd_jkdx_Lb_seq.nextval INTO new_jkdxlabel_g3e_id FROM dual;

   --取出相邻的两个杆塔的坐标，作为生成架空导线的两个点的坐标
   ----取出当前杆塔的坐标
   SELECT sdo_x1,sdo_y1 INTO v_gt_sdo_x1,v_gt_sdo_y1 FROM B$SD_GT_PT_SDOGEOM WHERE G3E_FID=v_gt_g3e_fid;
   ----给架空导线的两个点赋值,并保留第二个点作为下一条直线的第一个点
   v_symbol_sdo_x1:=v_prv_gt_sdo_x1;
   v_symbol_sdo_y1:=v_prv_gt_sdo_y1;
   v_symbol_sdo_x2:=v_gt_sdo_x1;
   v_symbol_sdo_y2:=v_gt_sdo_y1;

   v_prv_gt_sdo_x1:=v_gt_sdo_x1;
   v_prv_gt_sdo_y1:=v_gt_sdo_y1;
--取出当前杆塔号
SELECT gth INTO v_gth FROM B$SD_GT_N WHERE G3E_FID=v_gt_g3e_fid;

   ----给架空导线的标注坐标赋值
   v_lable_sdo_x2:=(v_symbol_sdo_x1+v_symbol_sdo_x2)*0.5;
   v_lable_sdo_y2:=(v_symbol_sdo_y1+v_symbol_sdo_y2)*0.5-10;
   v_x:=v_symbol_sdo_x2-v_symbol_sdo_x1;
   v_y:=v_symbol_sdo_y2-v_symbol_sdo_y1;
   --左高的用ATAN2(v_y,v_x)
   v_sdo_orientation:=ATAN2(v_y,v_x);

   --生成架空导线的图形
   ----往公共表里插入记录,建立架空导线与杆塔的所属关系
   INSERT INTO B$COMMON_N
    ( G3E_ID,G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,CD_SSDW,SBMC,CD_DYDJ,OWNER1_ID,OWNER2_ID,CD_EJDW,CD_ZT,CD_JKZT,SBDM)
   SELECT
     common_n_seq.nextval,G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,CD_SSDW,'新肥线',CD_DYDJ,v_owner_id1,v_owner_id2,CD_EJDW,CD_ZT,CD_JKZT,'22B213'
   FROM B$COMMON_N
   WHERE g3e_fid=v_g3e_fid AND ltt_status IS NULL;

   ----生成图形
   INSERT INTO B$SD_JKDX_N
    (G3E_ID,G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,CD_XLMC,CD_XLDM,QSGH,ZZGH)
   SELECT
     sd_jkdx_N_seq.nextval,G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,'新肥线','22B213',v_prv_gth,v_gth
   FROM B$SD_JKDX_N
   WHERE g3e_fid=v_g3e_fid AND ltt_status IS NULL;
v_prv_gth:=v_gth;
   INSERT INTO B$SD_JKDX_LN
    (GDO_GID,G3E_ID, G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,G3E_GEOMETRY,LTT_DATE)
   SELECT
    GDO_GID,new_jkdxsymbol_g3e_id, G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,G3E_GEOMETRY,sysdate
   FROM B$SD_JKDX_LN
   WHERE g3e_fid=v_g3e_fid AND ltt_status IS NULL;

   INSERT INTO B$SD_JKDX_LN_SDOGEOM
    (SDO_GID,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,
     GDO_RADIUS,SDO_ORIENTATION,SDO_X1, SDO_Y1, SDO_X2, SDO_Y2,
     G3E_ID, G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,LTT_DATE)
   SELECT
     new_jkdxsymbol_g3e_id,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,
     GDO_RADIUS,SDO_ORIENTATION,v_symbol_sdo_x1, v_symbol_sdo_y1, v_symbol_sdo_x2, v_symbol_sdo_y2,
     sd_jkdx_ln_sdogeom_seq.nextval, G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,sysdate
   FROM B$SD_JKDX_LN_SDOGEOM
   WHERE g3e_fid=v_g3e_fid AND ltt_status IS NULL;

   ----生成标注,并间隔一个标注一个
   IF flag_label=1 THEN
    INSERT INTO B$SD_JKDX_LB
     (GDO_GID,G3E_ALIGNMENT,G3E_ID,G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,G3E_GEOMETRY,LTT_DATE)
    SELECT
     GDO_GID,G3E_ALIGNMENT,new_jkdxlabel_g3e_id,G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,G3E_GEOMETRY,sysdate
    FROM B$SD_JKDX_LB
    WHERE g3e_fid=v_g3e_fid AND ltt_status IS NULL;

    INSERT INTO B$SD_JKDX_LB_SDOGEOM
     (SDO_GID,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,
      GDO_RADIUS,SDO_ORIENTATION,SDO_X1,SDO_Y1,
      G3E_ID,G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,LTT_DATE)
    SELECT
      new_jkdxlabel_g3e_id,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,
      GDO_RADIUS,v_sdo_orientation,v_lable_sdo_x2,v_lable_sdo_y2,
      sd_jkdx_lb_sdogeom_seq.nextval,G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,sysdate
    FROM B$SD_JKDX_lb_SDOGEOM
    WHERE g3e_fid=v_g3e_fid AND ltt_status IS NULL;
    flag_label:=0;
   ELSE
     flag_label:=1;
   END IF;

--建立架空导线与架空导线的电力连接关系
   INSERT INTO B$CONNECTIVITY_N
    (G3E_FNO,G3E_FID,G3E_CNO,G3E_CID,G3E_ID,NODE1_ID,NODE2_ID,
     PHASE,LTT_DATE)
   SELECT
     G3E_FNO,new_g3e_fid,G3E_CNO,G3E_CID,connectivity_n_seq.nextval,v_node1_id,v_node2_id,
     PHASE,sysdate
   FROM B$CONNECTIVITY_N
   WHERE g3e_fid=v_g3e_fid AND ltt_status IS NULL;
   i:=i+1;
ELSE
    flag:=1;
    --取出第一个杆塔的坐标作为第一条架空导线的第一个坐标
    SELECT sdo_x1,sdo_y1 INTO v_prv_gt_sdo_x1,v_prv_gt_sdo_y1 FROM B$SD_GT_PT_SDOGEOM WHERE G3E_FID=v_gt_g3e_fid;
END IF;
end loop;
dbms_output.put_line('添加了'||i||' 条架空导线。');
end;

--启用触发器
ALTER TABLE B$COMMON_N ENABLE ALL TRIGGERS;
ALTER TABLE b$CONNECTIVITY_N enable all triggers;
ALTER TABLE B$SD_JKDX_N enable all triggers;
ALTER TABLE B$SD_JKDX_LN ENABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LN_SDOGEOM ENABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LB ENABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LB_SDOGEOM ENABLE ALL TRIGGERS;