Operation functions for the geometry data type - AnalyticDB

Operation functions compute spatial relationships and transformations on GEOMETRY values. All functions that return GEOMETRY produce binary output — wrap the result in ST_AsText() to get readable Well-Known Text (WKT).

Function	Description
`ST_Intersection(g1, g2)`	Returns the geometric intersection of g1 and g2
`ST_Envelope(g)`	Returns the minimum bounding rectangle of a geometry
`ST_Union(g1, g2)`	Returns the point set union of two geometries
`geometry_union(array[g1, g2, ...])`	Returns the point set union of an array of geometries
`ST_Boundary(g)`	Returns the boundary of a geometry as a new geometry
`ST_EnvelopeAsPts(g)`	Returns the lower-left and upper-right corners of the bounding rectangle
`ST_Difference(g1, g2)`	Returns the part of g1 that does not intersect g2
`ST_ExteriorRing(g1)`	Returns the exterior ring of a polygon as a linestring
`ST_SymDifference(g1, g2)`	Returns the portions of g1 and g2 that do not intersect each other

ST_Intersection

ST_Intersection(g1, g2)

Returns a geometry representing the intersection of g1 and g2.

Return type: GEOMETRY

Example:

Running SELECT ST_Intersection(...) directly returns a binary result. Use ST_AsText() to convert it to readable text:

SELECT ST_AsText(ST_Intersection(ST_GeometryFromText('MULTIPOINT (50 100, 50 200)'), ST_GeometryFromText('Point (50 100)')));

Result:

+-----------------------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_Intersection(ST_GeometryFromText('MULTIPOINT (50 100, 50 200)'), ST_GeometryFromText('Point (50 100)'))) |
+-----------------------------------------------------------------------------------------------------------------------+
| POINT (50 100)                                                                                                        |

Related functions: ST_Union, ST_Difference, ST_SymDifference, ST_AsText

ST_Envelope

ST_Envelope(g)

Returns a binary representation of the minimum bounding rectangle (envelope) that encloses the input geometry.

Supported input types: LINE, POLYGON, MULTILINE, MULTIPOLYGON

POINT input is not supported.

Return type: GEOMETRY

Example 1 — Envelope of a LINESTRING:

SELECT ST_AsText(ST_Envelope(ST_GeometryFromText('LINESTRING (1 1, 2 2, 1 3)')));

Result:

+---------------------------------------------------------------------------------+
| ST_AsText(ST_Envelope(ST_GeometryFromText('LINESTRING (1 1, 2 2, 1 3)')))      |
+---------------------------------------------------------------------------------+
| POLYGON ((1 1, 2 1, 2 3, 1 3, 1 1))                                            |

Example 2 — Envelope of a MULTIPOINT:

SELECT ST_AsText(ST_Envelope(ST_GeometryFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)')));

Result:

+-------------------------------------------------------------------------------+
| ST_AsText(ST_Envelope(ST_GeometryFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)'))) |
+-------------------------------------------------------------------------------+
| POLYGON ((1 2, 4 2, 4 8, 1 8, 1 2))                                           |

Related functions: ST_EnvelopeAsPts, ST_Boundary

ST_Union

ST_Union(g1, g2)

Returns a geometry representing the point set union of g1 and g2.

Return type: INT

To compute the union of an array of geometries, use geometry_union.

Example:

SELECT ST_ASText(ST_Union(ST_GeometryFromText('MULTIPOLYGON (((1 1, 3 1, 3 3, 1 3, 1 1)))'), ST_GeometryFromText('MULTIPOLYGON (((2 2, 4 2, 4 4, 2 4, 2 2)))')));

Result:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_ASText(ST_Union(ST_GeometryFromText('MULTIPOLYGON (((1 1, 3 1, 3 3, 1 3, 1 1)))'), ST_GeometryFromText('MULTIPOLYGON (((2 2, 4 2, 4 4, 2 4, 2 2)))')))    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON ((1 1, 3 1, 3 2, 4 2, 4 4, 2 4, 2 3, 1 3, 1 1))                                                                                                      |

Related functions: geometry_union, ST_Intersection, ST_Difference, ST_SymDifference

geometry_union

geometry_union(array[g1, g2, ...])

Returns a geometry representing the point set union of an array of geometries.

Return type: GEOMETRY

To compute the union of exactly two geometries, use ST_Union.

Example:

SELECT ST_AsText(geometry_union(ARRAY[ST_Point(61.56, -158.54), ST_Point(61.56, -158.55)]));

Result:

+------------------------------------------------------------------------------------------------+
| ST_AsText(geometry_union(ARRAY[ST_Point(61.56, -158.54), ST_Point(61.56, -158.55)]))          |
+------------------------------------------------------------------------------------------------+
| MULTIPOINT ((61.56 -158.55), (61.56 -158.54))                                                 |

Related functions: ST_Union

ST_Boundary

ST_Boundary(g)

Returns the boundary of the input geometry as a new geometry.

Return type: GEOMETRY

Example:

SELECT ST_AsText(ST_Boundary(ST_GeometryFromText('LINESTRING (8 4, 5 7)')));

Result:

+----------------------------------------------------------------------------+
| ST_AsText(ST_Boundary(ST_GeometryFromText('LINESTRING (8 4, 5 7)')))      |
+----------------------------------------------------------------------------+
| MULTIPOINT ((8 4), (5 7))                                                  |

Related functions: ST_Envelope, ST_ExteriorRing

ST_EnvelopeAsPts

ST_EnvelopeAsPts(g)

Returns an array of two POINT values representing the lower-left and upper-right corners of the bounding rectangle. Returns null if the input geometry is empty.

Return type: Array[Geometry]

Example — empty geometry returns null:

SELECT ST_EnvelopeAsPts(ST_GeometryFromText('LINESTRING EMPTY'));

Result:

+-----------------------------------------------------------------+
| ST_EnvelopeAsPts(ST_GeometryFromText('LINESTRING EMPTY'))      |
+-----------------------------------------------------------------+
| null                                                            |

Related functions: ST_Envelope

ST_Difference

ST_Difference(g1, g2)

Returns the part of g1 that does not intersect g2.

Return type: GEOMETRY

Parameter order matters. ST_Difference(A, B) always returns a portion of A, not B. This is the only operation function where swapping the arguments produces a different result.

Example:

SELECT ST_AsText(ST_Difference(ST_GeometryFromText('POINT (50 100)'), ST_GeometryFromText('POINT (150 150)')));

Result:

+---------------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_Difference(ST_GeometryFromText('POINT (50 100)'), ST_GeometryFromText('POINT (150 150)')))      |
+---------------------------------------------------------------------------------------------------------------+
| POINT (50 100)                                                                                                |

The result is the full POINT (50 100) because the two points are disjoint — nothing is subtracted.

Related functions: ST_Intersection, ST_SymDifference, ST_Union

ST_ExteriorRing

ST_ExteriorRing(g1)

Returns the exterior ring of a polygon as a LINESTRING.

Return type: GEOMETRY

Example:

SELECT ST_AsText(ST_ExteriorRing(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 1))')));

Result:

+------------------------------------------------------------------------------------+
| ST_AsText(ST_ExteriorRing(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 1))')))     |
+------------------------------------------------------------------------------------+
| LINESTRING (1 1, 4 1, 1 4, 1 1)                                                   |

Related functions: ST_Boundary

ST_SymDifference

ST_SymDifference(g1, g2)

Returns the portions of g1 and g2 that do not intersect each other.

Return type: GEOMETRY

Example:

SELECT ST_AsText(ST_SymDifference(ST_GeometryFromText('POINT (50 100)'), ST_GeometryFromText('POINT (50 150)')));

Result:

+--------------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_SymDifference(ST_GeometryFromText('POINT (50 100)'), ST_GeometryFromText('POINT (50 150)')))   |
+--------------------------------------------------------------------------------------------------------------+
| MULTIPOINT ((50 100), (50 150))                                                                              |

The two points are disjoint, so the symmetric difference is both points combined.

Related functions: ST_Difference, ST_Union, ST_Intersection

Usage notes

Binary output: All functions returning GEOMETRY produce binary output when used in a bare SELECT. Always wrap the function call with ST_AsText() to get readable WKT output.
ST_Difference is asymmetric: ST_Difference(A, B) always returns a subset of A. Swapping the arguments produces a different result.
ST_Envelope does not support POINT input. Passing a POINT geometry to ST_Envelope is not supported.
MULTILINE: AnalyticDB for MySQL uses MULTILINE as the multi-segment line type. The equivalent OGC standard type is MULTILINESTRING.