What are the spatial constructors - AnalyticDB - Alibaba Cloud Documentation Center

Spatial constructor functions build and convert geometry objects in AnalyticDB for MySQL. Use them to construct POINT, LINESTRING, and POLYGON geometries from coordinates or text, convert between Well-Known Text (WKT) and Well-Known Binary (WKB) representations, and convert between planar and spherical coordinate systems.

Most spatial functions return a binary GEOMETRY value. Querying these values directly with SELECT produces unreadable output. Wrap the result with ST_AsText() to get a readable WKT string.

Function summary

Category	Function	Description
Construct from coordinates	`ST_Point`	Build a point from x and y coordinates
Construct from WKT	`ST_GeometryFromText` / `ST_GeomFromText`	Build any geometry type from a WKT string
	`ST_LineFromText`	Build a LineString from a WKT string
	`ST_PointFromText`	Build a point from a WKT string
	`ST_PolygonFromText`	Build a polygon from a WKT string
Construct from point arrays	`ST_LineString`	Build a LineString from an array of points
	`ST_MultiPoint`	Build a MultiPoint from an array of points
Convert to text	`ST_AsText`	Convert a geometry to its WKT representation
Convert to/from binary	`ST_AsBinary`	Convert a geometry to its WKB representation
	`ST_GeomFromBinary`	Build a geometry from a WKB value
Validate	`ST_IsValidWKT`	Check whether a string is valid WKT
Coordinate system	`To_spherical_geography`	Convert a planar geometry to a spherical geography object
	`To_geometry`	Convert a spherical geography object back to a planar geometry
Hadoop interop	`Geometry_from_hadoop_shape`	Build a geometry from a Spatial Framework for Hadoop binary value
Buffer	`ST_Buffer`	Return all points within distance `d` of a geometry

ST_Point

ST_Point(x double, y double) → GEOMETRY (subtype POINT)

Returns a POINT geometry from the specified x and y coordinate values. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(ST_Point(1, 1));

Result:

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

ST_AsText

ST_AsText(geometry) → WKT

Returns the Well-Known Text (WKT) representation of a geometry. Use this function to convert the binary output of other spatial functions into readable text.

Example

SELECT ST_AsText(ST_Point(1, 1));

Result:

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

ST_GeometryFromText / ST_GeomFromText

ST_GeometryFromText(wkt varchar) → GEOMETRY
ST_GeomFromText(wkt varchar) → GEOMETRY

Constructs a geometry object from a WKT string. ST_GeometryFromText and ST_GeomFromText are equivalent. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

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

Result:

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

ST_IsValidWKT

ST_IsValidWKT(wkt varchar) → INT

Returns 1 if the input string is valid WKT, or 0 if it is not.

Example

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

Result:

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

ST_LineFromText

ST_LineFromText(wkt varchar) → GEOMETRY

Returns a LineString geometry from a WKT string. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(ST_LineFromText('LINESTRING (1 1, 2 2, 1 3)'));

Result:

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

ST_PointFromText

ST_PointFromText(wkt varchar) → GEOMETRY

Returns a PointString object from a WKT string. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(ST_PointFromText('POINT (1 2)'));

Result:

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

ST_PolygonFromText

ST_PolygonFromText(wkt varchar) → GEOMETRY

Returns a PolygonString object from a WKT string. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(ST_PolygonFromText('POLYGON ((1 1, 1 4, 4 4, 4 1))'));

Result:

+----------------------------------------------------------------+
| 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(Point)) → GEOMETRY

Returns a LineString geometry built from an array of point geometries. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(ST_LineString(array[ST_Point(1, 2), ST_Point(3, 4)]));

Result:

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

ST_MultiPoint

ST_MultiPoint(array(Point)) → GEOMETRY

Returns a MultiPoint geometry built from an array of point geometries. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(
  ST_MultiPoint(array[
    ST_GeometryFromText('POINT(1 2)'),
    ST_GeometryFromText('POINT (3 4)')
  ])
);

Result:

+---------------------------+
| ST_AsText(...)            |
+---------------------------+
| MULTIPOINT ((1 2), (3 4)) |
+---------------------------+

ST_GeomFromBinary

ST_GeomFromBinary(wkb varbinary) → GEOMETRY

Returns a geometry from a Well-Known Binary (WKB) value. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

The following example converts a geometry to WKB with ST_AsBinary(), then reconstructs and reads it with ST_GeomFromBinary() and ST_AsText().

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

Result:

+---------------------------+
| ST_AsText(...)            |
+---------------------------+
| MULTIPOINT ((1 2), (3 4)) |
+---------------------------+

Geometry_from_hadoop_shape

Geometry_from_hadoop_shape(wkb varbinary) → GEOMETRY

Returns a geometry from a Spatial Framework for Hadoop binary value. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(
  Geometry_from_hadoop_shape(
    from_hex('000000000101000000000000000000F03F0000000000000040')
  )
);

Result:

+---------------------+
| ST_AsText(...)      |
+---------------------+
| POINT (1 2)         |
+---------------------+

To_spherical_geography

To_spherical_geography(geometry) → GEOMETRY

Converts a planar geometry to a spherical geography object. This converts an identifier defined in 2D space to that defined in 3D space. The return value is binary.

Example

SELECT To_spherical_geography(ST_Point(-71.0882, 42.3607));

To_geometry

To_geometry(geography) → GEOMETRY

Converts a spherical geography object back to a planar geometry. Each geography object is a valid geometry object. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(
  To_geometry(To_spherical_geography(ST_Point(61.56, -58.54)))
);

Result:

+----------------------+
| ST_AsText(...)       |
+----------------------+
| POINT (61.56 -58.54) |
+----------------------+

ST_AsBinary

ST_AsBinary(geometry) → VARBINARY

Returns the WKB representation of a geometry as a VARBINARY value. Use this function to store or transmit geometry data in binary format, then reconstruct it with ST_GeomFromBinary().

Example

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

Result:

+---------------------------+
| ST_AsText(...)            |
+---------------------------+
| MULTIPOINT ((1 2), (3 4)) |
+---------------------------+

ST_Buffer

ST_Buffer(geometry, d double) → GEOMETRY

Returns a geometry containing all points whose distance from the input geometry is less than or equal to d. For a point input, the result is a polygon that approximates a circle of radius d centered on that point. The return value is binary — wrap with ST_AsText() to get a readable result.

Example

SELECT ST_AsText(ST_Buffer(ST_Point(0, 0), 0.5));

Result (abbreviated):

+--------------------------------------------------------------------+
| ST_AsText(ST_Buffer(ST_Point(0, 0), 0.5))                         |
+--------------------------------------------------------------------+
| POLYGON ((0.5 0, 0.4989294616193014 0.03270156461507146, ...))     |
+--------------------------------------------------------------------+

The full result is a polygon that approximates a circle of radius 0.5 centered at the origin. The polygon is defined by 96 vertices.