Spatial JOIN（空间连接） - 云原生大数据计算服务 MaxCompute

空间连接（Spatial Join）是指在两个包含地理空间数据的表之间，基于空间谓词函数对地理对象之间的空间关系进行判断，并以此作为连接条件的一种非等值连接操作。典型场景包括判断兴趣点（POI）是否落在指定区域内、分析轨迹与地理围栏的交叉关系等。

使用限制

MaxCompute 基于广播小表连接实现空间连接。在查询中存在空间连接时，必须使用 MAPJOIN HINT 指定需广播的小表。

支持的空间谓词函数包括 ST_CONTAINS、ST_COVERS、ST_WITHIN、ST_INTERSECTS等。
支持的连接类型包括 INNER JOIN、LEFT JOIN（广播右侧表）、RIGHT JOIN（广播左侧表）。

使用示例

示例一

构造两个不相交的区域以及一些属于这些区域的点信息，使用 WKT 字符串存储。

CREATE OR REPLACE TABLE areas (
  area_id         BIGINT,
  area_name       STRING,
  geometry_detail STRING
);

INSERT OVERWRITE TABLE areas VALUES
(1, 'area_1', 'POLYGON((100 30, 110 30, 110 40, 100 40, 100 30))'),
(2, 'area_2', 'POLYGON((120 30, 130 30, 130 40, 120 40, 120 30))');

CREATE OR REPLACE TABLE points (
  point_id        BIGINT,
  point_name      STRING,
  location        STRING
);

INSERT OVERWRITE TABLE points VALUES
(1, 'point_1', 'POINT(105 35)'),   -- 落在 area_1 内
(2, 'point_2', 'POINT(108 38)'),   -- 落在 area_1 内
(3, 'point_3', 'POINT(102 32)'),   -- 落在 area_1 内
(4, 'point_4', 'POINT(125 35)'),   -- 落在 area_2 内
(5, 'point_5', 'POINT(128 39)');   -- 落在 area_2 内

查询两个区域各有哪些点。在空间连接时必须使用 MAPJOIN 指定小表。

说明

由于地理空间计算一般较为复杂，可通过 SET odps.stage.mapper.split.size=10; 适当调小 split size 以提高 Map 阶段并发度。

-- 需要支持 GEOGRAPHY 数据类型，开启 2.0 数据类型
SET odps.sql.type.system.odps2=true; 

SELECT /*+MAPJOIN(a)*/
    a.area_name,
    a.area_id,
    r.point_name,
    r.point_id
FROM areas a, points r  
WHERE ST_CONTAINS(ST_GEOGFROMTEXT(a.geometry_detail), ST_GEOGFROMTEXT(r.location))
ORDER BY a.area_name, r.point_id LIMIT 10;

-- 返回结果：
+-----------+------------+------------+------------+
| area_name | area_id    | point_name | point_id   |
+-----------+------------+------------+------------+
| area_1    | 1          | point_1    | 1          |
| area_1    | 1          | point_2    | 2          |
| area_1    | 1          | point_3    | 3          |
| area_2    | 2          | point_4    | 4          |
| area_2    | 2          | point_5    | 5          |
+-----------+------------+------------+------------+

示例二

构造两个不相交的区域及一些属于这些区域的点信息，使用 GEOGRAPHY 类型存储多边形，DOUBLE 类型存储点的经纬度。

CREATE OR REPLACE TABLE residential_areas (
    area_id   BIGINT,
    area_name STRING,
    geog      GEOGRAPHY
);

INSERT OVERWRITE TABLE residential_areas VALUES
(1, 'area_1', ST_GEOGFROMTEXT('POLYGON((100 30, 110 30, 110 40, 100 40, 100 30))')),
(2, 'area_2', ST_GEOGFROMTEXT('POLYGON((120 30, 130 30, 130 40, 120 40, 120 30))'));

CREATE OR REPLACE TABLE restaurants (
    restaurant_id   BIGINT,
    restaurant_name STRING,
    lon             DOUBLE,
    lat             DOUBLE
);

INSERT OVERWRITE TABLE restaurants VALUES
(1, 'restaurant_1', 105, 35),   -- 在 area_1 内
(2, 'restaurant_2', 108, 38),   -- 在 area_1 内
(3, 'restaurant_3', 125, 35),   -- 在 area_2 内
(4, 'restaurant_4', 150, 50),   -- 不在任一区域内
(5, 'restaurant_5', 102, 32);   -- 在 area_1 内

查询一个区域包含的点，并使用 LEFT JOIN 输出不在任一区域内的点。

SELECT /*+MAPJOIN(a)*/
    a.area_name,
    r.restaurant_id
FROM restaurants r LEFT JOIN residential_areas a 
ON ST_CONTAINS(a.geog, ST_GEOGPOINT(r.lon, r.lat))
ORDER BY a.area_name, r.restaurant_id LIMIT 10;

-- 返回结果：
+-----------+---------------+
| area_name | restaurant_id |
+-----------+---------------+
| NULL      | 4             |
| area_1    | 1             |
| area_1    | 2             |
| area_1    | 5             |
| area_2    | 3             |
+-----------+---------------+