Spatial constructor functions - AnalyticDB - Alibaba Cloud Documentation Center

Spatial constructor functions can be used to obtain binary representations of the POINT, LINE, and POLYGON GEOMETRY data types. You can also use these functions to convert binary data to text and obtain binary geometry data that is expressed as Well-Known Text (WKT).

ST_Point: returns a point geometry from the specified x and y coordinate values.
ST_AsText: returns the WKT representation of a specified geometry.
ST_GeometryFromText or ST_GeomFromText: constructs a geometry object from a WKT representation of a specified geometry.
ST_IsValidWKT: returns whether the text is well-formed in the WKT format.
ST_LineFromText: returns a LineString object by using its WKT representation.
ST_PointFromText: returns a PointString object by using its WKT representation.
ST_PolygonFromText: returns a PolygonString object by using its WKT representation.
ST_LineString: returns a LineString geometry object formed from an array of points.
ST_MultiPoint: returns a MultiPoint geometry object formed from an array of points.
ST_GeomFromBinary: returns a geometry object from a WKB type object.

Geometry_from_hadoop_shape: returns a geometry object from a Spatial Framework for Hadoop type object.
To_spherical_geography: returns a spherical geography object from a geometry object. This converts an identifier defined in 2D space to that defined in 3D space.
To_geometry: returns a geometry object from a spherical geography object. In fact, each geography object is a reasonable geometry object.
ST_AsBinary: returns a VARBINARY data type that contains the WKB representation of g1.
ST_Buffer: returns all the points whose distance from g1 is less than or equal to d.

ST_Point

ST_Point(x, y)

Description: This function returns a point geometry from the specified x and y coordinate values.
Data type of the return value: GEOMETRY of subtype POINT. If you execute the SELECT statement to query results of this function, the returned results are corrupted.

Example:

SELECT ST_Point(1,1);

Returned results:

+-----------------------+
| ST_Point(1,1)         |
+-----------------------+
|        �?      �?   |

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

SELECT ST_AsText(ST_Point(1,1));

Returned results:

+-----------------------+
| ST_Point(1,1)         |
+-----------------------+
|        POINT (1 1)    |

ST_AsText

ST_AsText(geometry)

Description: This function returns the WKT representation of a specified geometry.
Data type of the return value: WKT.

Example:

SELECT ST_AsText(ST_Point(1,1));

Returned results:

+--------------------------+
| ST_AsText(ST_Point(1,1)) |
+--------------------------+
| POINT (1 1)              |

ST_GeometryFromText or ST_GeomFromText

ST_GeometryFromText(wkt)

Description: This function constructs a geometry object from a WKT representation of a 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:

SELECT ST_AsText(ST_GeometryFromText('Point(1 1)'));

Returned results:

+----------------------------------------------+
| ST_AsText(ST_GeometryFromText('Point(1 1)')) |
+----------------------------------------------+
| POINT (1 1)                                  |

ST_IsValidWKT

ST_IsValidWKT(wkt)

Description: This function returns whether the text is well-formed in the WKT format. A value of 1 indicates true, and a value of 0 indicates false.
Data type of the return value: INT.

Example:

SELECT ST_IsValidWKT('MULTIPOINT (1 2, 2 4, 3 6, 4 8)');

Returned results:

+-------------------------------------------------------------+
|        ST_IsValidWKT('MULTIPOINT (1 2, 2 4, 3 6, 4 8)')     |
+-------------------------------------------------------------+
|                        1                                    |

ST_LineFromText

ST_LineFromText(wkt)

Description: This function returns a LineString object by using its WKT representation.
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_LineFromText('LINESTRING (1 1, 2 2, 1 3)'));

Returned results:

+-----------------------------------------------------------------------+
|        ST_AsText(ST_LineFromText('LINESTRING (1 1, 2 2, 1 3)'))       |
+-----------------------------------------------------------------------+
|                        LINESTRING (1 1, 2 2, 1 3)                     |

ST_PointFromText

ST_PointFromText(wkt)

Description: This function returns a PointString object by using its WKT representation.
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_PointFromText('POINT (1 2)'));

Returned results:

+-------------------------------------------------------------+
|        ST_AsText(ST_PointFromText('POINT (1 2)'))           |
+-------------------------------------------------------------+
|                       POINT (1 2)                           |

ST_PolygonFromText

ST_PolygonFromText(wkt)

Description: This function returns a PolygonString object by using its WKT representation.
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_PolygonFromText('POLYGON ((1 1, 1 4, 4 4, 4 1))'));

Returned results:

+------------------------------------------------------------------------+
|        ST_AsText(ST_PolygonFromText('POLYGON ((1 1, 1 4, 4 4, 4 1))')) |
+------------------------------------------------------------------------+
|                        POLYGON ((1 1, 1 4, 4 4, 4 1))                  |

ST_LineString

ST_LineString(array(g1,g2...))

Description: This function returns a LineString geometry object formed from an array of points.
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_LineString(array[ST_Point(1,2), ST_Point(3,4)]));

Returned results:

+------------------------------------------------------------------------+
| ST_AsText(ST_LineString(array[ST_Point(1,2), ST_Point(3,4)]))          |
+------------------------------------------------------------------------+
|           LINESTRING (1 2, 3 4)                                        |

ST_MultiPoint

ST_MultiPoint(array(g1,g2...))

Description: This function returns a MultiPoint geometry object formed from an array of points.
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_MultiPoint(array[ST_GeometryFromText('POINT(1 2)'), ST_GeometryFromText('POINT (3 4)')]));

Returned results:

+----------------------------------------------------------------------------------------------------------+
|  ST_AsText(ST_MultiPoint(array[ST_GeometryFromText('POINT(1 2)'), ST_GeometryFromText('POINT (3 4)')]))  |
+----------------------------------------------------------------------------------------------------------+
|          MULTIPOINT ((1 2), (3 4))                                                                       |

ST_GeomFromBinary

ST_GeomFromBinary(wkb)

Description: This function returns a geometry object from a WKB type object.
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_GeomFromBinary(ST_AsBinary(ST_GeometryFromText('MULTIPOINT ((1 2), (3 4))'))));

+------------------------------------------------------------------------------------------------+
|  ST_AsText(ST_GeomFromBinary(ST_AsBinary(ST_GeometryFromText('MULTIPOINT ((1 2), (3 4))'))))   |
+------------------------------------------------------------------------------------------------+
|          MULTIPOINT ((1 2), (3 4))                                                             |

Geometry_from_hadoop_shape

Geometry_from_hadoop_shape(wkb)

Description: This function returns a geometry object from a Spatial Framework for Hadoop type object.
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_from_hadoop_shape(from_hex('000000000101000000000000000000F03F0000000000000040')));

Returned results:

+-------------------------------------------------------------------------------------------------------+
| ST_AsText(Geometry_from_hadoop_shape(from_hex('000000000101000000000000000000F03F0000000000000040'))) |
+-------------------------------------------------------------------------------------------------------+
|                         POINT (1 2)                                                                   |

To_spherical_geography

To_spherical_geography(g1)

Description: This function returns a spherical geography object from a geometry object. This converts an identifier defined in 2D space to that defined in 3D space.
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 To_spherical_geography(ST_Point(-71.0882, 42.3607));

Returned results:

+-----------------------------------------------------------+
| To_spherical_geography(ST_Point(-71.0882, 42.3607))       |
+-----------------------------------------------------------+
|          �?      �?                                      |

To_geometry

To_geometry(g1)

Description: This function returns a geometry object from a spherical geography object. In fact, each geography object is a reasonable geometry object.
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(To_geometry(To_spherical_geography(ST_Point(61.56, -58.54))));

Returned results:

+----------------------------------------------------------------------------+
| ST_ASText(To_geometry(To_spherical_geography(ST_Point(61.56, -58.54))))    |
+----------------------------------------------------------------------------+
|           POINT (61.56 -58.54)                                             |

ST_AsBinary

ST_AsBinary(g1)

Description: This function returns a VARBINARY data type that contains the WKB representation of g1.
Data type of the return value: VARBINARY.

Example:

SELECT ST_AsText(ST_GeomFromBinary(ST_AsBinary(ST_GeometryFromText('MULTIPOINT ((1 2), (3 4))'))));

Returned results:

+--------------------------------------------------------------------------------------------------+
|  ST_AsText(ST_GeomFromBinary(ST_AsBinary(ST_GeometryFromText('MULTIPOINT ((1 2), (3 4))'))))     |
+--------------------------------------------------------------------------------------------------+
|          MULTIPOINT ((1 2), (3 4))                                                               |

ST_Buffer

ST_Buffer(g1, d)

Description: This function returns all the points whose distance from g1 is less than or equal to d.
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_Buffer(ST_Point(0, 0), 0.5));

Returned results:

+----------------------------------------------------------------------------------------------+
|  ST_AsText(ST_Buffer(ST_Point(0, 0), 0.5))                                                   |
+----------------------------------------------------------------------------------------------+
|          POLYGON ((0.5 0, 0.4989294616193014 0.03270156461507146, 
0.49572243068690486 0.0652630961100257, 0.4903926402016149 0.09754516100806403,
0.4829629131445338 0.12940952255126026, 0.47346506474755257 0.16071973265158065, 
0.46193976625564315 0.19134171618254472, 0.4484363707663439 0.22114434510950046, 
0.43301270189221913 0.2499999999999998, 0.41573480615127245 0.2777851165098009, 
0.39667667014561747 0.30438071450436016, 0.3759199037394886 0.32967290755003426, 
0.3535533905932737 0.3535533905932736, 0.32967290755003437 0.3759199037394886, 
0.3043807145043603 0.39667667014561747, 0.2777851165098011 0.4157348061512725, 
0.24999999999999997 0.43301270189221924, 0.22114434510950062 0.4484363707663441, 
0.19134171618254486 0.4619397662556433, 0.16071973265158077 0.4734650647475528, 
0.12940952255126037 0.48296291314453416, 0.09754516100806412 0.4903926402016152, 
0.06526309611002579 0.4957224306869052, 0.03270156461507153 0.49892946161930174, 
0 0.5, -0.03270156461507146 0.4989294616193014, -0.0652630961100257 
0.49572243068690486, -0.09754516100806403 0.4903926402016149, -0.12940952255126026 
0.4829629131445338, -0.16071973265158065 0.47346506474755257, -0.19134171618254472 
0.46193976625564315, -0.22114434510950046 0.4484363707663439, -0.2499999999999998 
0.43301270189221913, -0.2777851165098009 0.41573480615127245, -0.30438071450436016 
0.39667667014561747, -0.32967290755003426 0.3759199037394886, -0.3535533905932736 
0.3535533905932737, -0.3759199037394886 0.32967290755003437, -0.39667667014561747 
0.3043807145043603, -0.4157348061512725 0.2777851165098011, -0.43301270189221924 
0.24999999999999997, -0.4484363707663441 0.22114434510950062, -0.4619397662556433 
0.19134171618254486, -0.4734650647475528 0.16071973265158077, -0.48296291314453416 
0.12940952255126037, -0.4903926402016152 0.09754516100806412, -0.4957224306869052 
0.06526309611002579, -0.49892946161930174 0.03270156461507153, -0.5 0, 
-0.4989294616193014 -0.03270156461507146, -0.49572243068690486 -0.0652630961100257, 
-0.4903926402016149 -0.09754516100806403, -0.4829629131445338 -0.12940952255126026, 
-0.47346506474755257 -0.16071973265158065, -0.46193976625564315 
-0.19134171618254472, -0.4484363707663439 -0.22114434510950046, 
-0.43301270189221913 -0.2499999999999998, -0.41573480615127245 -0.2777851165098009, 
-0.39667667014561747 -0.30438071450436016, -0.3759199037394886 -0.32967290755003426, 
-0.3535533905932737 -0.3535533905932736, -0.32967290755003437 -0.3759199037394886, 
-0.3043807145043603 -0.39667667014561747, -0.2777851165098011 -0.4157348061512725, 
-0.24999999999999997 -0.43301270189221924, -0.22114434510950062 -0.4484363707663441, 
-0.19134171618254486 -0.4619397662556433, -0.16071973265158077 -0.4734650647475528, 
-0.12940952255126037 -0.48296291314453416, -0.09754516100806412 -0.4903926402016152, 
-0.06526309611002579 -0.4957224306869052, -0.03270156461507153 -0.49892946161930174, 
0 -0.5, 0.03270156461507146 -0.4989294616193014, 0.0652630961100257 
-0.49572243068690486, 0.09754516100806403 -0.4903926402016149, 0.12940952255126026 
-0.4829629131445338, 0.16071973265158065 -0.47346506474755257, 0.19134171618254472 
-0.46193976625564315, 0.22114434510950046 -0.4484363707663439, 0.2499999999999998 
-0.43301270189221913, 0.2777851165098009 -0.41573480615127245, 0.30438071450436016 
-0.39667667014561747, 0.32967290755003426 -0.3759199037394886, 0.3535533905932736 
-0.3535533905932737, 0.3759199037394886 -0.32967290755003437, 0.39667667014561747 
-0.3043807145043603, 0.4157348061512725 -0.2777851165098011, 0.43301270189221924 
-0.24999999999999997, 0.4484363707663441 -0.22114434510950062, 0.4619397662556433 
-0.19134171618254486, 0.4734650647475528 -0.16071973265158077, 0.48296291314453416 
-0.12940952255126037, 0.4903926402016152 -0.09754516100806412, 0.4957224306869052 
-0.06526309611002579, 0.49892946161930174 -0.03270156461507153, 0.5 0))                          |