一、写在前面

①配套文件：根据剖面对三维坐标点（X,Y,Z）分类资源-CSDN文库

②脱敏处理：蚀变数据已采用随机数生成覆盖

③剖面坐标按顺序排列在“剖面坐标点.xlsx”文件中

二、3点确定空间中平面方程

原理：

设3点A，B，C
计算向量AB和AC
法向量n = AB × AC （叉乘）
得到n（ni，nj，nk）后，设方程为
ni * X + nj * Y + nk * Z = D
代入A,B,C任意一点的坐标得出D值后就可以得到平面方程

代码：

输入：p1、p2、p3为平面上的3个点

输出：Ax+By+Cz+D=0平面方程中的系数

def plane_from_points(p1, p2, p3):
    # 将点转换为numpy数组，如果已经是数组则不转换
    a = np.array(p1)
    b = np.array(p2)
    c = np.array(p3)
 
    # 计算法向量
    normal = np.cross(b - a, c - a)
    # 归一化法向量并计算d
    d = -np.dot(normal, a)
    
    print("平面方程: {}x + {}y + {}z + {} = 0".format(normal[0],normal[1],normal[2],d))
    return normal[0], normal[1], normal[2], d

三、确定空间中一点到平面的距离

原理：点到平面距离_百度百科 (baidu.com)

代码：

输入：①平面方程中的4个系数；②待求与平面方程距离的点

输出：点到平面距离

def point2area_distance(Ax, By, Cz, D,p):
    mod_d = Ax * p[0] + By * p[1] + Cz * p[2] + D
    mod_area = np.sqrt(np.sum(np.square([Ax, By, Cz])))
    d = abs(mod_d) / mod_area
    return d

四、完整代码

dataSample = pd.read_excel(r"C:\Users\zsllsz2022\Desktop\坐标分类.xlsx")

import numpy as np
 
def plane_from_points(p1, p2, p3):
    # 将点转换为numpy数组，如果已经是数组则不转换
    a = np.array(p1)
    b = np.array(p2)
    c = np.array(p3)
 
    # 计算法向量
    normal = np.cross(b - a, c - a)
    # 归一化法向量并计算d
    d = -np.dot(normal, a)
    
    print("平面方程: {}x + {}y + {}z + {} = 0".format(normal[0],normal[1],normal[2],d))
    return normal[0], normal[1], normal[2], d
 
def point2area_distance(Ax, By, Cz, D,p):
    mod_d = Ax * p[0] + By * p[1] + Cz * p[2] + D
    mod_area = np.sqrt(np.sum(np.square([Ax, By, Cz])))
    d = abs(mod_d) / mod_area
    return d

pointData = data = pd.read_excel(r"C:\Users\zsllsz2022\Desktop\剖面坐标点.xlsx")

point = []

for index in range(63):     
    x = pointData["X"].iloc[index]
    y = pointData["Y"].iloc[index]
    z = pointData["Z"].iloc[index]
    
    point.append([x,y,z])
    
    
fileIndex = 1
for x in [3*x for x in range(3,4)]:  #range21
    p1 = point[x]
    p2 = point[x+1]
    p3 = point[x+2]
    
    a,b,c,d = plane_from_points(p1, p2, p3)
    
    dataSample = pd.read_excel(r"C:\Users\zsllsz2022\Desktop\坐标分类.xlsx")
    classifyIndex = []
    for index in range(len(dataSample)):
        xTo = dataSample["Xto"].iloc[index]
        yTo = dataSample["Yto"].iloc[index]
        zTo = dataSample["Zto"].iloc[index]
        toPoint = [xTo,yTo,zTo]
        
        xFrom = dataSample["Xfrom"].iloc[index]
        yFrom = dataSample["Yfrom"].iloc[index]
        zFrom = dataSample["Zfrom"].iloc[index]
        fromPoint = [xFrom,yFrom,zFrom]
        
        if point2area_distance(a,b,c,d,toPoint) < 5:
            classifyIndex.append(index)
            
        if point2area_distance(a,b,c,d,fromPoint) < 5:
            classifyIndex.append(index)
    
    dataSample = dataSample.iloc[classifyIndex]
    list_to_append = [np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,
                      dataSample["Dep2200"].mean(),dataSample["IC"].mean(),dataSample["Pos2200"].mean(),
                      dataSample["Dep2250"].mean(),dataSample["Pos2250"].mean(),dataSample["Dep1900"].mean()]
     
    new_row = pd.DataFrame([list_to_append], columns=list(dataSample.columns))
    dataSample = dataSample.append(new_row,ignore_index=True)
    
    print("剖面完成:",fileIndex)
    dataSample.to_excel(r"C:\Users\zsllsz2022\Desktop\剖面{}.xlsx".format(fileIndex))
    fileIndex += 1