设备所在业务空间 ## 前置条件 1. 业务空间有所属楼层 2. 设备有所属楼层 3. 业务空间有轮廓数据(outline) 4. 设备有坐标信息(bimLocation) ## 依赖函数 Eq2Fl ## 处理方式 将在同一楼层内的设备和业务空间取出, 判断设备的bim_location是否在业务空间的outline内, 如果在, 则添加进对应的关系表内, 控制sign = 2 # 函数 ## 代码 ``` create or replace function public.rel_eq2sp(tables text, out_tables text, project_id character varying, sign1 integer, sign2 integer) returns boolean as $$ from matplotlib.path import Path import json def is_in_meta_polygon(point, single_poly, radius): poly_len = len(single_poly) poly = [] for i in range(poly_len): pair = single_poly[i] poly.append((pair["X"], pair["Y"])) p = Path(poly) return p.contains_points([point], None, radius) def is_in_polygon(point, polygons): polygons_length = len(polygons) if polygons_length == 0: return False for j in range(polygons_length): polygon = polygons[j] if j == 0: if not is_in_meta_polygon(point, polygon, -0.001): return False else: if is_in_meta_polygon(point, polygon, 0.001): return False return True def is_point_in_polygon(x, y, json_poly): try: polygon_list = json.loads(json_poly) total_len = len(polygon_list) point_pair = (float(x), float(y)) for index in range(total_len): if is_in_polygon(point_pair, polygon_list[index]): return True return False except Exception as e: plpy.info(e) return False # 将下面对数据库的操作作为一个事务, 出异常则自动rollback input_table_list = tables.split(',') output_table_list = out_tables.split(',') with plpy.subtransaction(): for i in range(0, len(input_table_list)): in_table_name = input_table_list[i] out_table_name = output_table_list[i] # 删除原来关系表中的数据 plan1 = plpy.prepare("delete from {0} where project_id = $1 and (sign = $2 or sign = $3)".format(out_table_name.strip()), ["text", "integer", "integer"]) plan1.execute([project_id, sign1, sign2]) # 计算关系 plan_floor = plpy.prepare("select id from floor where project_id = $1", ["text"]) floors = plan_floor.execute([project_id]) # 按楼层计算 for floor in floors: floor_id = floor['id'] # 获取楼层下的设备 plan_equip = plpy.prepare("select id, bim_location from equipment where project_id = $1 and bim_location is not null and floor_id = $2", ["text", "text"]) equips = plan_equip.execute([project_id, floor_id]) if len(equips) == 0: continue # 获取楼层下的业务空间 space_plan = plpy.prepare("select id, outline from {0} as sp where project_id = $1 and outline is not null and floor_id = $2".format(in_table_name), ["text", "text"]) spaces = space_plan.execute([project_id, floor_id]) if len(spaces) == 0: continue # 判断设备的bim_location是否在业务空间的outline内 for equip in equips: for space in spaces: try: location = equip['bim_location'].split(',') if is_point_in_polygon(location[0], location[1], space['outline']): # 设备在业务空间内, 添加关系 insert_plan = plpy.prepare("insert into {0}(equip_id, space_id, project_id, sign) values($1, $2, $3, 2) ".format(out_table_name.strip()), ["text", "text", 'text']) insert_plan.execute([equip['id'], space['id'], project_id]) except Exception as ex: continue return True $$ LANGUAGE 'plpython3u' VOLATILE; 例子: select public.rel_eq2sp('zone_general,zone_lighting', 'r_eq_in_sp_zone_general,r_eq_in_sp_zone_lighting', 'Pj1101010015', 2, 2); ``` ## 输入 1. 参与计算的业务空间表的全名称, 带schema名, 以英文逗号隔开 2. 关系计算结果存储的表, 跟第一个参数一一对应, 带schema名, 以英文逗号隔开 3. 项目id 4. 要被删除的sign, 只能是int型, 值为1或2 (1表示手动维护的关系, 2表示自动计算的关系) 5. 第二个要被删除的sign, 只能是int型, 值为1或2 (1表示手动维护的关系, 2表示自动计算的关系) ## 返回结果 true 成功 false 失败