Spatial join adalah operasi non-equijoin yang menggunakan fungsi predikat spasial untuk mengevaluasi hubungan antara objek geografis dalam dua tabel, dengan hubungan tersebut berfungsi sebagai kondisi join. Kasus penggunaan umum mencakup menentukan apakah suatu point of interest (POI) berada di dalam area tertentu atau menganalisis perpotongan antara lintasan (trajectory) dan geofence.
Limitations
MaxCompute mengimplementasikan spatial join menggunakan broadcast join, sehingga Anda harus menggunakan MAPJOIN HINT untuk menentukan tabel yang lebih kecil agar di-broadcast.
Fungsi predikat spasial yang didukung meliputi ST_CONTAINS, ST_COVERS, ST_WITHIN, dan ST_INTERSECTS.
Jenis join yang didukung adalah INNER JOIN, LEFT JOIN (yang melakukan broadcast pada tabel kanan), dan RIGHT JOIN (yang melakukan broadcast pada tabel kiri).
Examples
Example 1
Buat dua area yang tidak tumpang tindih dan beberapa titik di dalamnya. Data geometris disimpan dalam format 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)'), -- Berada di dalam area_1
(2, 'point_2', 'POINT(108 38)'), -- Berada di dalam area_1
(3, 'point_3', 'POINT(102 32)'), -- Berada di dalam area_1
(4, 'point_4', 'POINT(125 35)'), -- Berada di dalam area_2
(5, 'point_5', 'POINT(128 39)'); -- Berada di dalam area_2Kueri titik-titik dalam setiap area. Diperlukan MAPJOIN HINT untuk menentukan tabel yang lebih kecil dalam spatial join.
Karena komputasi geospasial umumnya kompleks, Anda dapat menggunakan SET odps.stage.mapper.split.size=10; untuk mengurangi ukuran split, yang meningkatkan konkurensi pada tahap Map.
-- Untuk mendukung tipe data GEOGRAPHY, aktifkan sistem tipe versi 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;
-- Result:
+-----------+------------+------------+------------+
| 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 |
+-----------+------------+------------+------------+Example 2
Buat dua area yang tidak tumpang tindih dan beberapa titik. Poligon area disimpan dalam tipe data GEOGRAPHY, sedangkan lokasi titik disimpan sebagai nilai longitude dan latitude bertipe 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), -- Di area_1
(2, 'restaurant_2', 108, 38), -- Di area_1
(3, 'restaurant_3', 125, 35), -- Di area_2
(4, 'restaurant_4', 150, 50), -- Tidak berada di area mana pun
(5, 'restaurant_5', 102, 32); -- Di area_1Kueri area yang memuat setiap titik, dan gunakan LEFT JOIN untuk menyertakan juga titik-titik yang tidak berada di area mana pun.
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;
-- Result:
+-----------+---------------+
| area_name | restaurant_id |
+-----------+---------------+
| NULL | 4 |
| area_1 | 1 |
| area_1 | 2 |
| area_1 | 5 |
| area_2 | 3 |
+-----------+---------------+