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');