Operation functions - AnalyticDB - Alibaba Cloud Documentation Center

Operation functions can be used to manage values of the GEOMETRY data type.

ST_Intersection: returns a geometry that represents the intersection of g1 and g2.
ST_Envelope: takes data of the LINE, POLYGON, MULTILINE, and MULTIPOLYGON GEOMETRY types as an input. This function does not support the POINT GEOMETRY data type. This function returns a binary representation of an envelope, in which an envelope refers to the rectangle that encloses the specified geometry.
ST_Union: returns a geometry that represents the point set union of the specified geometries.
geometry_union: returns a geometry that represents the point set union of the specified geometries.
ST_Boundary: takes a geometry as an input parameter and returns its boundary as a new geometry.
ST_EnvelopeAsPts: returns an array of two points that represent the lower-left and upper-right corners of the bounding rectangular polygon of a geometry. If the specified geometry is empty, null is returned.
ST_Difference: returns a geometry that represents the difference between the left geometry and the right geometry.
ST_ExteriorRing: returns the exterior ring of a polygon as a linestring.
ST_SymDifference: returns a geometry that represents the symmetric difference between the left geometry and the right geometry.

ST_Intersection

ST_Intersection(g1, g2)

Description: This function returns a geometry that represents the intersection of g1 and g2.
Data type of the return value: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.

Example:

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

Returned results:

+------------------------------------------------------------------------------------------------------------+
| ST_Intersection(ST_GeometryFromText('MULTIPOINT (50 100, 50 200)'), ST_GeometryFromText('Point (50 100)')) |
+------------------------------------------------------------------------------------------------------------+
|                         �?      �?                                                                        |

You can call the ST_AsText function to convert the results to readable text. Example:

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

Returned results:

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

ST_Envelope

ST_Envelope(g)

Description: This function takes data of the LINE, POLYGON, MULTILINE, and MULTIPOLYGON GEOMETRY types as an input. This function does not support the POINT GEOMETRY data type. This function returns a binary representation of an envelope, in which an envelope refers to the rectangle that encloses the specified geometry.
Data type of the return value: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted. You can call the ST_AsText function to convert the results to readable text.

Example 1:

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

Returned results:

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

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

Returned results:

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

ST_Union

ST_Union(g1, g2)

Description: This function returns a geometry that represents the point set union of the specified geometries.
Data type of the return value: INT.

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)))')));

Returned results:

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

geometry_union

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

Description: This function returns a geometry that represents the point set union of the specified geometries.
Data type of the return value: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted. You can call the ST_AsText function to convert the results to readable text.

Example:

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

Returned results:

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

ST_Boundary

ST_Boundary(g)

Description: This function takes a geometry as an input parameter and returns its boundary as a new geometry.
Data type of the return value: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted. You can call the ST_AsText function to convert the results to readable text.

Example:

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

Returned results:

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

ST_EnvelopeAsPts

ST_EnvelopeAsPts(g)

Description: This function returns an array of two points that represent the lower-left and upper-right corners of the bounding rectangular polygon of a geometry. If the specified geometry is empty, null is returned.
Data type of the return value: Array[Geometry].

Example:

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

Returned results:

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

ST_Difference

ST_EnvelopeAsPts(g1, g2)

Description: This function returns a geometry that represents the difference between the left geometry and the right geometry.
Data type of the return value: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted. You can call the ST_AsText function to convert the results to readable text.

Example:

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

Returned results:

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

ST_ExteriorRing

ST_ExteriorRing(g1)

Description: This function returns the exterior ring of a polygon as a linestring.
Data type of the return value: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted. You can call the ST_AsText function to convert the results to readable text.

Example:

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

Returned results:

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

ST_SymDifference

ST_SymDifference(g1, g2)

Description: This function returns a geometry that represents the symmetric difference between the left geometry and the right geometry.
Data type of the return value: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted. You can call the ST_AsText function to convert the results to readable text.

Example:

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

Returned results:

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