MaxCompute:GEOGRAPHY data type

Supported geometry types

MaxCompute supports the following geometry types:

-- A polygon with only an outer boundary and no holes
ST_GEOGFROMTEXT('POLYGON ((0 0, 1 1, 1 0, 0 0))')

-- A polygon with one hole
ST_GEOGFROMTEXT('POLYGON ((-10 -10, 10 -10, 10 10, -10 10, -10 -10), (-5 -5, 5 -5, 5 5, -5 5, -5 -5))')

-- Nested holes are not currently supported. The following construction is invalid:
ST_GEOGFROMTEXT('POLYGON ((-10 -10, 10 -10, 10 10, -10 10, -10 -10), (-5 -5, 5 -5, 5 5, -5 5, -5 -5), (-2 -2, 2 -2, 2 2, -2 2, -2 -2))')

• Coordinates are specified as (longitude, latitude) in degrees, following the WGS84 coordinate system. Longitude must be within the range [-180, 180], and latitude must be within [-90, 90].

• The MaxCompute GEOGRAPHY data type uses the S2 Geometry library. Unlike traditional Geographic Information System (GIS) libraries, S2 Geometry does not use planar map projections like the Mercator projection. Instead, it treats the Earth as a perfect sphere. It first converts latitude and longitude coordinates to three-dimensional Cartesian coordinates (x, y, z) on the sphere. This approach avoids the increased computational errors that planar projections can cause, especially near the poles.

• The S2 Geometry library uses the DOUBLE type to describe the coordinates of geographic objects. When converting input data to S2 objects, a minor loss of precision may occur, resulting in a very small discrepancy between the input and output.

Limitations

• Client version restrictions: To query GEOGRAPHY data, you must use odpscmd version 0.56.0 or later. The setting use_instance_tunnel=true is not supported.

• Data type restrictions: The GEOGRAPHY data type is not supported in the 1.0 data type system. You must enable the 2.0 data type system at the project or session level by setting odps.sql.type.system.odps2=true;.

• The GEOGRAPHY data type does not support comparison operations. Therefore, you cannot use it in SELECT DISTINCT, GROUP BY, or ORDER BY clauses, or as a condition for an equi-join in a JOIN clause.

• The GEOGRAPHY data type does not support the Z (elevation) or M (measure) dimensions for geographic objects.

• The GEOGRAPHY data type does not support constructing a geographic object that is larger than a hemisphere. For example, the following SQL statement attempts to create a geographic object that covers almost the entire globe, from 179 degrees west longitude to 179 degrees east longitude. However, the actual result is an object smaller than a hemisphere whose longitude extends eastward from 179°E, crosses the 180° meridian, and ends at 179°W.

  SELECT ST_GEOGFROMTEXT(
    'POLYGON ((-179 -89, 179 -89, 179 89, -179 89, -179 -89))'
  );
  
  -- Result:
  +------+
  | _c0  |
  +------+
  | POLYGON ((-179 89, 179 89, 179 -89, -179 -89, -179 89)) |
  +------+

Create and use GEOGRAPHY data

You can use the GEOGRAPHY data type as a field type when you create a table:

SET odps.sql.type.system.odps2=true;

CREATE TABLE spatial_points (
    id BIGINT,
    name STRING,
    location GEOGRAPHY,
    region GEOGRAPHY
);

You can use built-in functions, such as ST_GEOGPOINT, ST_GEOGFROMTEXT, ST_GEOGFROMWKB, ST_MAKELINE, and ST_MAKEPOLYGON, to construct geographic objects. For more information, see Geography function overview.

-- Insert data: Construct GEOGRAPHY objects using geography functions.
INSERT INTO spatial_points VALUES
  (1, 'Beijing Center', ST_GEOGPOINT(116.4074, 39.9042),
                ST_GEOGFROMTEXT('POLYGON((116.3 39.8, 116.5 39.8, 116.5 40.0, 116.3 40.0, 116.3 39.8))')),
  (2, 'The Bund', ST_GEOGPOINT(121.4894, 31.2403),
                ST_MAKEPOLYGON(ST_MAKELINE(ARRAY(
                  ST_GEOGPOINT(121.4, 31.2),
                  ST_GEOGPOINT(121.5, 31.2),
                  ST_GEOGPOINT(121.5, 31.3),
                  ST_GEOGPOINT(121.4, 31.3),
                  ST_GEOGPOINT(121.4, 31.2)
                ))));

-- Query the inserted data.
SELECT * FROM spatial_points;
-- Result:
+------------+--------------+--------------------------+--------+
| id         | name         | location                 | region |
+------------+--------------+--------------------------+--------+
| 1          | Beijing Center | POINT (116.4074 39.9042) | POLYGON ((116.3 39.8, 116.5 39.8, 116.5 40, 116.3 40, 116.3 39.8)) |
| 2          | The Bund       | POINT (121.4894 31.2403) | POLYGON ((121.4 31.2, 121.5 31.2, 121.5 31.3, 121.4 31.3, 121.4 31.2)) |
+------------+--------------+--------------------------+--------+

Geography functions

MaxCompute provides a series of geography functions prefixed with ST_ to construct, convert, and analyze GEOGRAPHY data. You can use these functions to create geographic objects such as points, lines, and polygons from longitude and latitude coordinates or from WKT/WKB text. You can also extract properties like coordinates and bounding boxes, determine spatial relationships such as containment, intersection, and coverage, and calculate the distance between geographic objects.

For more information, see Geography function overview.

The following geography functions are supported:

Spatial join

A spatial join is a non-equi join operation between two tables containing GEOGRAPHY data. The join condition uses a spatial predicate function (such as ST_CONTAINS or ST_INTERSECTS) that evaluates the spatial relationship between the geographic objects.

• A common use case is using a geography function as the JOIN condition to evaluate the spatial relationship between GEOGRAPHY columns.

• Typical use cases include determining whether a point of interest (POI) is within a specified area or analyzing the intersection of a trajectory with a geofence.

MaxCompute implements spatial joins based on a broadcast table. You must use a MAPJOIN HINT to specify which table to broadcast.

For more information, see Spatial JOIN.