时间戳

首页 > 解决方案 > 在mysql中计算空间距离并按距离排序

mysql - 在mysql中计算空间距离并按距离排序

问题描述

我有 3 个包含以下字段的表：

  1. org（org_id、org_name、位置）
  2. 办公室（office_id、office_name、位置、org_id）
  3. 项目（项目 ID、项目名称、位置）

我想找到离给定项目最近的办公室 

SELECT office.office_name, org.org_name, 
       st_distance_sphere(office.location, project.location)*0.001 as 
       distance 
FROM   office JOIN org JOIN project
WHERE  office.org_id=org.org_id AND 
       project.project_id=7 
ORDER BY distance asc;

上述查询有效，并为我提供了离项目最近的办公室，但我只需要为每个组织获取 1 个最近的办公室

因此我尝试了以下使用半连接

SELECT office.office_name, org.org_name, 
       st_distance_sphere(office.location, project.location)*0.001 as  
       distance 
FROM  office JOIN org JOIN project  
INNER JOIN 
  ( SELECT org.org_id as orgid1, 
       min(st_distance_sphere(office.location, project.location)*0.0001) as 
       distance1 
   FROM office JOIN org JOIN project 
   WHERE 
      office.org_id=org.org_id and project.project_id=7
   GROUP BY org.org_id
   ) AS t 
     ON t.orgid1=org.org_id and t.distance1=min(st_distance_sphere(office.location,project.location)*0.001) 
 WHERE  office.org_id=org.org_id AND project.project_id=7 ;

使用它，我得到以下错误，原因似乎是min(st_distance_sphere)函数。删除它会消除错误，但我没有得到想要的结果。

ERROR 1111 (HY000): 组函数使用无效

我究竟做错了什么 ？任何想法高度赞赏。谢谢

标签： mysqlgroup-bygeospatialgreatest-n-per-group

解决方案

我只需要为每个组织找到 1 个最近的办公室

SELECT 
  A.office_name, A.org_name, A.distance
FROM
  (SELECT 
    office.office_name,
    org.org_name, 
    st_distance_sphere(office.location, project.location)*0.001 as distance 
  FROM  office JOIN org JOIN project
  WHERE  office.org_id=org.org_id AND 
       project.project_id=7) A 
  JOIN 
  (SELECT 
     org.org_name, 
     MIN(st_distance_sphere(office.location, project.location)*0.001) as distance 
  FROM   office JOIN org JOIN project
  WHERE  office.org_id=org.org_id AND 
       project.project_id=7
  GROUP BY org.org_name) B
  ON A.org_name=B.org_name AND A.distance=B.distance
ORDER BY A.distance ASC;

推荐阅读