设备所在业务空间
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 失败