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             |
+-----------+---------------+