# 业务空间邻接关系 ## 前置条件 1. 业务空间有所在楼层 2. 业务空间有外轮廓 ## 处理逻辑 1. 查出所有有所在楼层关系, 并且外轮廓不是null的业务空间 2. 根据所在楼层, 业务空间分区类型来将业务空间分为不同的组 (例如 : A 楼层下的默认业务空间是一组, A楼层下的空调分区是另外一组) 3. 计算每个分组内的业务空间的相邻关系 计算相邻算法 (如下图): 1. 在一个分组内, 遍历每个业务空间上组成外轮廓的每条线段, 根据线段的长度, 是否超过delta_distance来区分成两种情况判断 1). 如果线段长度大于delta_distance, 假如图中线段AB长度大于delta_distance, 则在距离A点delta_distance的距离找到点P5, 在P5点处作AB线段的垂线线段, 上下两边长度均为door_length长, 找到点P4和P6. 同理可以找到点P7和P9. 2). 如果线段长度小于或等于delta_distance, 假设线段AE长度小于delta_distance, 则在线段中间点P2处作垂直于AE的垂线线段, 上下两边长度均为door_length长, 找到点P1和P3. 2. 将上面找到的点, 依次与组内其他业务空间外轮廓做一次点是否在多边形内的判断, 如果在, 则认为该多边形与图中多边形相邻 ![image](./img/sp2sp2-1.png) ## 函数 ### 业务空间轮廓结构 ``` [ [ [ {点坐标}, {点坐标}... ], // 子轮廓的外轮廓 [ {点坐标}, {点坐标}... ], // 子轮廓的第n个需要被排除的轮廓 ], // 子轮廓 [ [ {点坐标}, {点坐标}... ] ], ] ```
源码 ``` CREATE OR REPLACE FUNCTION "public"."rel_sp2sp_v2"("project_id" varchar) RETURNS "pg_catalog"."bool" AS $BODY$ import math import json from shapely.geometry import Polygon from matplotlib.path import Path column_project_id = 'project_id' column_location_one = 'location_one' column_location_two = 'location_two' column_space_id_one = 'space_id_one' column_space_id_two = 'space_id_two' column_zone_type = 'zone_type' column_added_polygon = 'polygon' column_id = 'id' column_bim_location = 'bim_location' column_floor_id = 'floor_id' column_object_type = 'object_type' column_outline = 'outline' key_x = 'X' key_y = 'Y' delta_distance = 200 door_length = 1500 # 获取两点之间的距离 def get_segment_distance(x1, y1, x2, y2): x_diff = x1 - x2 y_diff = y1 - y2 return math.sqrt(x_diff ** 2 + y_diff ** 2) # 获取垂直方向的斜率 def get_vertical_k(k): if k is None: return 0 if k == 0: return None return 1 / k # 根据点 x, y 计算 y = kx + a 中的 a def get_a_by_point_k(x, y, k): if k is None: return None return y - k * x # 在直线 y = kx + a 上找到距离点 base_x, base_y 距离为distance 的坐标点(两个坐标点) def get_point_by_distance(base_x, base_y, k, a, distance): if k is None: return base_x, base_y + distance, base_x, base_y - distance vector_x1 = math.sqrt(distance ** 2 / (1 + k ** 2)) vector_x2 = -vector_x1 vector_y1 = k * vector_x1 vector_y2 = k * vector_x2 return base_x + vector_x1, base_y + vector_y1, base_x + vector_x2, base_y + vector_y2 # 在直线 y = kx + a 上找到一个点, 该点距离点(base_x, base_y) 长度为 distance, 并且距离vec_x, vec_y最近 def get_point_by_distance_on_segment(base_x, base_y, k, a, distance, vec_x, vec_y): x1, y1, x2, y2 = get_point_by_distance(base_x, base_y, k, a, distance) distance1 = get_segment_distance(x1, y1, vec_x, vec_y) distance2 = get_segment_distance(x2, y2, vec_x, vec_y) if distance1 > distance2: return x2, y2 return x1, y1 # 获取输入的两点之间开门之后门的坐标点 # 返回格式 # 如果线段距离小于等于door_length的情况 : x1, y1, x2, y2 # 如果线段距离大于door_length的情况 (两端各开一个门): x1, y1, x2, y2, x3, y3, x4, y4 def get_points(x1, y1, x2, y2): # 如果两个点是同一个点 if x1 == y1 and x2 == y2: return None, None, None, None # 计算线段的距离 distance = get_segment_distance(x1, y1, x2, y2) # 计算当前线段的 k if x1 == x2: k = None else: k = (y1 - y2) / (x1 - x2) # 计算垂直方向的k vertical_k = get_vertical_k(k) # 计算当前线段的 a a = get_a_by_point_k(x1, y1, k) # 距离大于delta_distance, 则足够开两个门 if distance > delta_distance: seg_x1, seg_y1 = get_point_by_distance_on_segment(x1, y1, k, a, delta_distance, x2, y2) seg_x2, seg_y2 = get_point_by_distance_on_segment(x2, y2, k, a, delta_distance, x1, y1) vertical_a1 = get_a_by_point_k(seg_x1, seg_y1, vertical_k) vertical_a2 = get_a_by_point_k(seg_x2, seg_y2, vertical_k) dest_x1, dest_y1, dest_x2, dest_y2 = get_point_by_distance(seg_x1, seg_y1, vertical_k, vertical_a1, door_length) dest_x3, dest_y3, dest_x4, dest_y4 = get_point_by_distance(seg_x2, seg_y2, vertical_k, vertical_a2, door_length) return [dest_x1, dest_x2, dest_x3, dest_x4], [dest_y1, dest_y2, dest_y3, dest_y4] else: # 距离太小, 在中间开门 seg_x1, seg_y1 = get_point_by_distance_on_segment(x1, y1, k, a, distance/2, x2, y2) vertical_a1 = get_a_by_point_k(seg_x1, seg_y1, vertical_k) dest_x1, dest_y1, dest_x2, dest_y2 = get_point_by_distance(seg_x1, seg_y1, vertical_k, vertical_a1, door_length) return [dest_x1, dest_x2], [dest_y1, dest_y2] # 获取Polygon对象 def get_polygon(single_poly): poly_len = len(single_poly) # plpy.info("poly_len : {0}, single_poly : {1} ".format(poly_len, single_poly)) poly = [] for i in range(poly_len): pair = single_poly[i] poly.append((pair[key_x], pair[key_y])) # plpy.info((pair[key_x], pair[key_y])) return Path(poly) # 获取业务空间每个块的最外层轮廓, 组成arr返回, 如果数据不合法发生异常则返回None def get_outer_polygon_arr(raw_outline): try: arr = [] outline_json = json.loads(raw_outline) for i in range(len(outline_json)): try: single_polygon = get_polygon(outline_json[i][0]) except Exception as ex: # plpy.info("error getting polygon : {0}".format(outline_json)) continue arr.append(single_polygon) return arr except Exception as e: return [] # 判断一个点是否在多边形数组中的某一个多边形内 def is_point_in_polygons(point, polygon_arr): try: for polygon in polygon_arr: try: if polygon.contains_points([point], None, -0.0001): return True except Exception as ee: plpy.warning("point in polygon : {0}".format(ee)) return False except Exception as e: plpy.warning(e) return False # 检查是否已经加过这个关系(单向检查) def check_is_in_rel(space_adjacent, probe_id, id): if probe_id in space_adjacent: rel_set = space_adjacent[probe_id] if id in rel_set: return True return False # 检查是否关系存在(双向检查) def check_is_in_rel_bidirection(space_adjacent, probe_id, id): is_in = check_is_in_rel(space_adjacent, probe_id, id) is_in = is_in or check_is_in_rel(space_adjacent, id, probe_id) return is_in # 将业务空间相邻关系插入map中 def insert_into_rel(space_adjacent, probe_id, id): if check_is_in_rel_bidirection(space_adjacent, probe_id, id): return if probe_id not in space_adjacent: space_adjacent[probe_id] = set() rel_set = space_adjacent[probe_id] rel_set.add(id) # 计算一个分组内的邻接关系, space_adjacent用来保存关系, 结构是 space_id --> set(space_id) def calc_adjacent_sub_arr(space_sub_arr, space_adjacent, space_info): # space_sub_arr 是一个楼层内的一类业务空间 for space_row in space_sub_arr: raw_outline = space_row.get(column_outline) id = space_row.get(column_id) space_info[id] = space_row outline_json = json.loads(raw_outline) #plpy.info("outline_json : {0}".format(len(outline_json))) for i in range(len(outline_json)): try: single_poly = outline_json[i][0] except Exception as ee : # plpy.warning("error outline : {0}, id : {1}".format(outline_json, id)) continue #single_poly = outline_json[i][0] poly_len = len(single_poly) for idx in range(1, poly_len): # 线段 segment_point1 = single_poly[idx - 1] segment_point2 = single_poly[idx] # segment_length = get_segment_distance(segment_point1[key_x], segment_point1[key_y], segment_point2[key_x], segment_point2[key_y]) x_arr, y_arr = get_points(segment_point1[key_x], segment_point1[key_y], segment_point2[key_x], segment_point2[key_y]) # plpy.info("x_arr : {0} , y_arr : {1}".format(x_arr, y_arr)) for probe_space_row in space_sub_arr: probe_polygon_arr = probe_space_row.get(column_added_polygon) probe_id = probe_space_row.get(column_id) # 如果自己跟自己比较或者已经存在该关系的话, 跳过 if probe_id == id or check_is_in_rel_bidirection(space_adjacent, probe_id, id): continue for arr_index in range(0, len(x_arr)): prob_x = x_arr[arr_index] prob_y = y_arr[arr_index] is_hit = is_point_in_polygons((prob_x, prob_y), probe_polygon_arr) # plpy.info("is hit : {0}".format(is_hit)) if is_hit: # plpy.info("hit") insert_into_rel(space_adjacent, probe_id, id) break # 将输入数据按照楼层id, 业务空间类型分类 def classify(space_list): current_floor_id = '' current_object_type = '' current_sub_arr = [] space_arr = [] for row in space_list: if row.get(column_floor_id) == current_floor_id and row.get(column_object_type) == current_object_type: current_sub_arr.append(row) else: current_floor_id = row.get(column_floor_id) current_object_type = row.get(column_object_type) current_sub_arr = [row] space_arr.append(current_sub_arr) row[column_added_polygon] = get_outer_polygon_arr(row.get(column_outline)) for sub_arr in space_arr: if len(sub_arr) == 1: space_arr.remove(sub_arr) return space_arr # 计算业务空间相邻 def calc_space_adjacent(space_list): # 给业务空间按照所属楼层和业务空间类型分组 space_arr = classify(space_list) space_adjacent = dict() space_info = dict() for space_sub_arr in space_arr: calc_adjacent_sub_arr(space_sub_arr, space_adjacent, space_info) return space_adjacent, space_info #try: # 将下面对数据库的操作作为一个事务, 出异常则自动rollback with plpy.subtransaction(): # 获取有所在楼层的所有业务空间, 并按照所在楼层和业务空间类型排序, 方便分组 sql_str = "SELECT sp.id, sp.project_id, rel.floor_id, sp.object_type, sp.bim_location, sp.outline FROM zone_space_base sp inner join r_sp_in_fl rel on rel.space_id = sp.id where sp.project_id = $1 and rel.project_id = $1 and outline is not null order by floor_id, object_type" space_data_plan = plpy.prepare(sql_str, ["text"]) space_data = space_data_plan.execute([project_id]) plpy.info("space data : {0}".format(len(space_data))) rel_data, space_info = calc_space_adjacent(space_data) # 删除以前的业务空间相邻关系 delete_plan = plpy.prepare("delete from r_spatial_connection where project_id = $1 and sign = 2 and graph_type = 'SpaceNeighborhood'", ["text"]) delete_plan.execute([project_id]) plpy.info("rel_data : {0}".format(len(rel_data))) for space_id1, to_space_set in rel_data.items(): space1 = space_info[space_id1] for space_id2 in to_space_set: space2 = space_info[space_id2] delete_duplicate_plan = plpy.prepare("delete from r_spatial_connection where space_id_one = $1 and space_id_two = $2 and type = 'SpaceNeighborhood'", ["text", "text"]) delete_duplicate_plan.execute([space_id1, space_id2]) insert_plan = plpy.prepare("insert into r_spatial_connection(project_id, location_one, location_two, space_id_one, space_id_two, sign, graph_type, floor_id, zone_type) values($1, $2, $3, $4, $5, 2, 'SpaceNeighborhood', $6, $7)", ["text", "text", "text", "text", "text", "text", "text"]) # plpy.info("{0}, {1}, {2}, {3}, {4}, {5}, {6}".format(project_id, space1.get(column_bim_location), space2.get(column_bim_location), space_id1, space2, space1.get(column_floor_id), space1.get(column_object_type))) insert_plan.execute([project_id, space1.get(column_bim_location), space2.get(column_bim_location), space_id1, space2.get(column_id), space1.get(column_floor_id), space1.get(column_object_type)]) return True #except Exception as e: # plpy.warning(e) # return False #else: # return True $BODY$ LANGUAGE plpython3u VOLATILE COST 100 select public.rel_sp2sp_v2('Pj1101050001') ```
## 入参 1. 项目id ## 例子 select public.rel_sp2sp_v2('Pj1102290002');