Eq2Sp.md 4.9 KB

设备所在业务空间

前置条件

1. 业务空间有所属楼层关系
2. 设备有所属楼层关系
3. 业务空间有轮廓数据(outline信息点)
4. 设备有坐标信息(bimLocation信息点)

依赖函数

Eq2Fl, Sp2Fl

处理逻辑

1. 取出项目内的所有业务空间和设备, 将在同一楼层内的设备和业务空间分成一组
2. 在一组内, 判断设备的bim_location是否在业务空间的outline内, 即做点在多边形内的判断, 点在多边形内则认为有关系

函数

代码

源码
CREATE OR REPLACE FUNCTION "public"."rel_eq2sp"("tables" text, "out_tables" text, "project_id" varchar, "sign1" int4, "sign2" int4)
  RETURNS "pg_catalog"."bool" AS $BODY$
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 sp.id, sp.outline, sp.object_type from public.r_sp_in_fl rel inner join {0} sp on rel.space_id = sp.id where outline is not null and rel.floor_id = $1".format(in_table_name), ["text"])
            spaces = space_plan.execute([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}(eq_id, sp_id, project_id, sign, type, zone_type) values($1, $2, $3, 2, 'eq2sp_in', $4)".format(out_table_name.strip()), ["text", "text", "text", "text"])
                            insert_plan.execute([equip['id'], space['id'], project_id, space['object_type']])
                    except Exception as ex:
                        plpy.warning(ex)
                        continue
return True
$BODY$
  LANGUAGE plpython3u VOLATILE
  COST 100


例子:
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   失败