persagy-service-doc/docs/dev/data-center/relations/belongs/Sp2Sp2.md

业务空间邻接关系

前置条件

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. 将上面找到的点, 依次与组内其他业务空间外轮廓做一次点是否在多边形内的判断, 如果在, 则认为该多边形与图中多边形相邻

函数

业务空间轮廓结构

[
    [
        [
            {点坐标},
            {点坐标}...
        ], // 子轮廓的外轮廓
        [
            {点坐标},
            {点坐标}...
        ], // 子轮廓的第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');