设备所在楼层
1. 设备有ModelId信息点
2. 楼层有ModelId信息点
3. 楼层的ModelId信息点等于设备的ModelId信息点
4. 设备没有所在楼层关系
5. 设备有Location(非必要)
6. 楼层有Outline(非必要)
1. 如果楼层有Outline, 设备有Location, 做点(Location)在多边形(Outline)内的判断, 在多边形内则认为有关系
2. 如果不满足条件1, 则判断ModelId信息点的值, 相等则认为有关系
-- 设备所在楼层
create or replace function public.rel_eq2fl(project_id character varying) returns boolean
as
$$
from matplotlib.path import Path
import json
def is_in_meta_polygon(x, y, json_poly):
try:
poly = []
polygon = json.loads(json_poly)
l = len(polygon)
points = (float(x), float(y))
for i in range(l):
pair = polygon[i]
poly.append((pair["X"], pair["Y"]))
p = Path(poly)
except Exception as e:
plpy.info(e)
return False
else:
return p.contains_points([points], None, -0.0001)
try:
# 将下面对数据库的操作作为一个事务, 出异常则自动rollback
with plpy.subtransaction():
floor_plan = plpy.prepare("select id, outline, model_id from floor where project_id = $1 and model_id is not null", ["text"])
floors = floor_plan.execute([project_id])
equip_plan = plpy.prepare("select id, bim_location, model_id from equipment where project_id = $1 and model_id is not null", ["text"])
equips = equip_plan.execute([project_id])
for floor in floors:
for equip in equips:
outline = floor['outline']
location = equip['bim_location']
if outline == None or len(outline) == 0 or location == None or len(location) == 0:
if floor['model_id'] == floor['model_id']:
equip['floor_id'] = floor['id']
continue
location = location.split(',')
try:
if is_in_meta_polygon(location[0], location[1], outline):
equip['floor_id'] = floor['id']
except Exception as ex:
continue
for equip in equips:
if equip.__contains__('floor_id'):
try:
#plpy.info("equip id {0}, floor id {1}".format(equip['id'], equip['floor_id']))
plan = plpy.prepare("update equipment set floor_id = $1 where id = $2", ["text", "text"])
plan.execute([equip['floor_id'], equip['id']])
except Exception as ex:
continue
except Exception as e:
plpy.warning(e)
return False
else:
return True
$$
LANGUAGE 'plpython3u' VOLATILE;
select public.rel_eq2fl('Pj1101010015');
1. 项目id
true 成功
false 失败