mysql - 使用 MySQL 空间计算许多点之间的最短距离（通过循环中的行？）

Question

一）问题

假设有 4 个表：

表：assignment_has_place

| assignment_id | place_id |
|---------------|----------|
| 1             | 1        |
| 2             | 4        |
| 2             | 5        |

表：agent_has_place

| agent_id | place_id |
|----------|----------|
| 1        | 2        |
| 2        | 1        |
| 1        | 3        |
| 1        | 4        |
| 3        | 4        |
| 3        | 6        |

表：assignment_has_agent

| assignment_id | agent_id |
|---------------|----------|
| 1             | 1        |
| 1             | 2        |
| 2             | 3        |

表：地点（测试数据）

| Place_id | Name        | Geodata                 |
|----------|-------------|-------------------------|
| 1        | New-York    | POINT(-74.0072 40.7131) |
| 2        | Los Angeles | POINT(-118.244 34.0522) |
| 3        | Boston      | POINT(-71.0589 42.3601) |
| 4        | Chicago     | POINT(-87.6298 41.8781) |
| 5        | Dallas      | POINT(-96.797 32.7767)  |
| 6        | Austin      | POINT(-97.7431 30.2672) |

所以，一个任务可以有很多地方，一个代理可以有很多（个人）地方（例如家庭、工作、父母家等），一个任务可以有很多代理人。

我研究了代理人分配地点和代理人（个人）地点之间的关系。然后，我想知道每个代理分配地点和他所有（个人）地点之间的最短距离。（换句话说，我想为他的每个分配地点找到最近的代理人（个人）地点，并知道他们之间的距离）。

II) 当前查询

SELECT
    assignment_has_agent.agent_id AS agent,
    place.place_id AS place_of_assignment,

-- BEGIN. Calculate distance between agents places and agents places of assignment
    ST_Distance_Sphere(place.geodata, -- This is a geodata about a place of assignment

        -- BEGIN. Select geodata for all agents places
        (SELECT
            place.geodata -- This is a geodata about an agents place
        FROM
            place
        INNER JOIN agent_has_place -- Get geodata about agents place
        ON agent_has_place.place_id = place.place_id
        WHERE agent_has_place.agent_id = assignment_has_agent.agent_id
        LIMIT 1 -- Here the problem!!! Without LIMIT Mysql error "Subquery returns more than 1 row"
        )
        -- END. Select geodata for all agents places
    ) 
    AS shortest_distance
-- END. Calculate distance between agents places and agents places of assignment

    FROM
        assignment_has_place 
    LEFT JOIN assignment_has_agent -- Make a link between assignment and agent
        ON assignment_has_agent.assignment_id = assignment_has_place.assignment_id  
    LEFT JOIN place -- Get a geodata about the place of assignment
        ON place.place_id = assignment_has_place.place_id

    GROUP BY assignment_has_agent.agent_id, place.place_id

三）目前的结果

| agent | place_of_assignment | shortest_distance  |
|-------|---------------------|--------------------|
| 1     | 1                   | 3935659.762302256  | -- Note: New-York<>Los Angeles
| 2     | 1                   | 0                  | -- Note: New-York<>New-York
| 3     | 4                   | 0                  | -- Note: Chicago<>Chicago
| 3     | 5                   | 1294952.4320354236 | -- Note: Dallas<>Chicago

在我当前的查询中，我必须添加LIMIT 1，否则我有 mysql 错误“子查询返回超过 1 行”。（见上面的代码。）这是因为代理可以有很多（个人）的地方。所以这个结果是不正确的，因为它只占所有代理（个人）位置中的第一行。

IV) 预期结果

| agent | place_of_assignment | shortest_distance  |
|-------|---------------------|--------------------|
| 1     | 1                   | 306167.42792567355 | -- Note: New-York<>Boston
| 2     | 1                   | 0                  | -- Note: New-York<>New-York
| 3     | 4                   | 0                  | -- Note: Chicago<>Chicago
| 3     | 5                   | 293094.4286555507  | -- Note: Dallas<>Austin

我需要计算所有分配地点和所有代理（个人）地点之间的距离，然后选择最短（最小）距离。

五）问题（S）

如何获得预期的结果？我想用 MIN() 选择最短距离并不太难？但是我如何才能处理每一行代理（个人）位置？我是否必须使用循环（光标？？？我可以在这里使用它吗？）还是有任何其他（更简单）的方法可以做到这一点？

非常感谢您的任何建议！

在此处查看演示

标签： mysqlsqlloopsgeospatialgeocoding