1. 楼层有外轮廓
2. 业务空间有外轮廓
3. 有2个或2个以上的楼层的ModelId相同(意思是一个模型划分成了多个楼层)
4. 这些ModelId相同的楼层下有所属的业务空间关系
5. 业务空间所属楼层表内维护的关系是正确的
1. 查出项目内所有ModelId相同的楼层(如果没有则结束)
2. 按照ModelId信息点将楼层分组, 相同ModelId的楼层为一组, 每组内按照以下规则计算
3. 获取组内所有楼层下的业务空间, 将查出的业务空间的轮廓与组内楼层的轮廓比对是否有重叠部分(所有业务空间 X 组内楼层个数次对比), 如果有重叠则认为该业务空间和该楼层有关系
4. 在业务空间所属楼层表内删除该组楼层下的所有业务空间关系, 并插入新计算出的业务空间所属楼层的关系
源码
CREATE OR REPLACE FUNCTION "public"."rel_sp2fl"("project_id" varchar)
RETURNS "pg_catalog"."bool" AS $BODY$
from matplotlib.path import Path
import json
from shapely.geometry import Polygon
# 获取Polygon对象
def get_polygon(single_poly):
poly_len = len(single_poly)
poly = []
for i in range(poly_len):
pair = single_poly[i]
poly.append((pair["X"], pair["Y"]))
return Polygon(poly)
# 判断业务空间轮廓是否跟楼层轮廓有重叠
def is_space_floor_overlap(space_outline, floor_outline):
try:
if space_outline is None or floor_outline is None:
return False
space_json = json.loads(space_outline)
floor_json = json.loads(floor_outline)
if len(floor_json) < 3 or len(space_json) == 0:
return False
floor_poly = get_polygon(floor_json)
for i in range(len(space_json)):
sub_space = space_json[i]
sub_space_poly = get_polygon(sub_space[0])
if floor_poly.contains(sub_space_poly) or floor_poly.equals(sub_space_poly) or floor_poly.overlaps(sub_space_poly) or sub_space_poly.contains(floor_poly):
return True
return False
except Exception as e:
plpy.info('计算轮廓异常')
plpy.info(e)
return False
column_model_id = 'model_id'
column_space_id = 'space_id'
column_floor_id1 = 'id1'
column_floor_outline1 = 'outline1'
column_floor_building1 = 'bd1'
column_floor_id2 = 'id2'
column_floor_outline2 = 'outline2'
column_floor_building2 = 'bd2'
column_space_zone = 'object_type'
# 构建一个 model_id --> set{floor_id} 的dict
def compose_model_id_floor_dict(involved_floors):
model_id_floor_dict = dict()
floor_outline_dict = dict()
floor_building_dict = dict()
for row in involved_floors:
model_id = row.get(column_model_id)
if model_id not in model_id_floor_dict:
model_id_floor_dict[model_id] = set()
floor_set = model_id_floor_dict[model_id]
id1 = row.get(column_floor_id1)
outline1 = row.get(column_floor_outline1)
building_id1 = row.get(column_floor_building1)
id2 = row.get(column_floor_id2)
outline2 = row.get(column_floor_outline2)
building_id2 = row.get(column_floor_building2)
floor_set.add(id1)
floor_set.add(id2)
floor_building_dict[id1] = building_id1
floor_building_dict[id2] = building_id2
if id1 not in floor_outline_dict:
floor_outline_dict[id1] = outline1
if id2 not in floor_outline_dict:
floor_outline_dict[id2] = outline2
return model_id_floor_dict, floor_outline_dict, floor_building_dict
# 计算一个楼层组内的业务空间与楼层的轮廓关系
def calc_floor_group(floor_set, floor_outline_dict):
sql_str = ""
for floor_id in floor_set:
sql_str += '\'' + floor_id + '\','
if sql_str.endswith(','):
sql_str = sql_str[0:-1]
plpy.info('sql : {0}'.format(sql_str))
involved_space_plan = plpy.prepare("SELECT distinct rel.space_id space_id, sp.outline outline1, sp.object_type object_type " +
"FROM r_sp_in_fl rel inner join zone_space_base sp on sp.id = rel.space_id where rel.floor_id in ({0}) and sp.outline is not null".format(sql_str), ["text"])
involved_space = involved_space_plan.execute([project_id])
plpy.info("involved space:{}".format(len(involved_space)))
result_dict = dict() # space_id --> set{floor_id}
space_zone_dict = dict() # space_id --> object_type
for space_row in involved_space:
space_outline = space_row.get(column_floor_outline1)
space_id = space_row.get(column_space_id)
space_zone = space_row.get(column_space_zone)
space_zone_dict[space_id] = space_zone
result_dict[space_id] = set()
rel_floor_set = result_dict[space_id]
for floor_id in floor_set:
#for floor_id, floor_outline in floor_outline_dict.items():
floor_outline = floor_outline_dict[floor_id]
if is_space_floor_overlap(space_outline, floor_outline):
plpy.info('{0} : {1}'.format(space_id, floor_id))
rel_floor_set.add(floor_id)
return result_dict, space_zone_dict
# 删除以前的关系
def delete_prev_rel(floor_set):
sql_str = ''
for floor_id in floor_set:
sql_str += '\'{0}\','.format(floor_id)
if sql_str.endswith(','):
sql_str = sql_str[0:-1]
delete_rel_plan = plpy.prepare("delete from public.r_sp_in_fl where floor_id in ({0})".format(sql_str), [])
delete_rel_plan.execute([])
# 添加关系到数据库
def add_rel(result_dict, space_zone_dict, floor_building_dict):
for space_id, floor_set in result_dict.items():
space_zone = space_zone_dict[space_id]
for floor_id in floor_set:
floor_building = floor_building_dict[floor_id]
plpy.info("space_id : {0}, floor_id : {1}".format(space_id, floor_id))
insert_rel_plan = plpy.prepare("insert into public.r_sp_in_fl(floor_id, space_id, object_type, project_id, sign, building_id) " +
"values($1, $2, $3, $4, 2, $5)", ["text", "text", "text", "text", "text"])
try:
insert_rel_plan.execute([floor_id, space_id, space_zone, project_id, floor_building])
except Exception as e:
plpy.info(e)
try:
# 将下面对数据库的操作作为一个事务, 出异常则自动rollback
involved_floors_plan = plpy.prepare("select f1.id id1, f1.building_id bd1, f2.id id2, f2.building_id bd2, f1.model_id model_id, f1.outline outline1, f2.outline outline2 " +
"from floor f1 inner join floor f2 on f1.model_id = f2.model_id " +
"where f1.id != f2.id and f1.outline is not null and f2.outline is not null and f1.project_id = $1 and f2.project_id = $1", ["text"])
involved_floors = involved_floors_plan.execute([project_id])
if len(involved_floors) == 0 :
plpy.info('没有需要计算的改变')
return True
plpy.info(len(involved_floors))
model_id_floor_dict, floor_outline_dict, floor_building_dict = compose_model_id_floor_dict(involved_floors)
plpy.info(len(floor_outline_dict))
plpy.info(len(floor_building_dict))
plpy.info(len(model_id_floor_dict))
for model_id, floor_set in model_id_floor_dict.items():
with plpy.subtransaction():
plpy.info(model_id)
plpy.info(floor_set)
result_dict, space_zone_dict = calc_floor_group(floor_set, floor_outline_dict) # space_id -->所属 set{floor_id}, space_id --> object_type
delete_prev_rel(floor_set)
add_rel(result_dict, space_zone_dict, floor_building_dict)
return True
except Exception as e:
plpy.warning(e)
return False
$BODY$
LANGUAGE plpython3u VOLATILE
COST 100;
select public.rel_sp2fl('Pj1101050001')
1. 项目id
select public.rel_sp2fl('Pj1102290002');