Spatial JOIN - MaxCompute - Alibaba Cloud Documentation Center

A spatial join is a non-equijoin operation that uses a spatial predicate function to evaluate the relationship between geographic objects in two tables. This relationship then serves as the join condition. Common use cases include determining if a point of interest (POI) is within a specified area or analyzing intersections between a trajectory and a geofence.

Limitations

MaxCompute implements spatial joins using a broadcast join, which requires you to use a MAPJOIN HINT to specify the smaller table to be broadcast.

Supported spatial predicate functions include ST_CONTAINS, ST_COVERS, ST_WITHIN, and ST_INTERSECTS.
Supported join types are INNER JOIN, LEFT JOIN (which broadcasts the right table), and RIGHT JOIN (which broadcasts the left table).

Examples

Example 1

Create two non-overlapping areas and several points within them. The geometric data is stored as 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)'),   -- Falls within area_1
(2, 'point_2', 'POINT(108 38)'),   -- Falls within area_1
(3, 'point_3', 'POINT(102 32)'),   -- Falls within area_1
(4, 'point_4', 'POINT(125 35)'),   -- Falls within area_2
(5, 'point_5', 'POINT(128 39)');   -- Falls within area_2

Query the points in each area. A MAPJOIN HINT is required to specify the smaller table for a spatial join.

Note

Because geospatial computations are generally complex, you can use SET odps.stage.mapper.split.size=10; to reduce the split size, which increases the concurrency of the Map stage.

-- To support the GEOGRAPHY data type, enable the 2.0 type system.
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

Create two non-overlapping areas and several points. The area polygons are stored as the GEOGRAPHY data type, and the point locations are stored as longitude and latitude values of type 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),   -- In area_1
(2, 'restaurant_2', 108, 38),   -- In area_1
(3, 'restaurant_3', 125, 35),   -- In area_2
(4, 'restaurant_4', 150, 50),   -- Not in any area
(5, 'restaurant_5', 102, 32);   -- In area_1

Query the area containing each point, and use a LEFT JOIN to also include points that are not in any area.

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