How the ST_AsText() function works in Mariadb?

The ST_AsText() function in MariaDB is used to convert a geometry value into its Well-Known Text (WKT) representation. The WKT format is a human-readable text representation of vector geometry data, and it is widely used for exchanging spatial data between different systems and applications.

Syntax

The syntax for the MariaDB ST_AsText() function is as follows:

ST_AsText(geom)

• geom: The geometry value that needs to be converted to its WKT representation.

The function returns a string containing the WKT representation of the input geometry.

Examples

Example 1: Convert a Point geometry to WKT

This example demonstrates how to convert a Point geometry to its WKT representation.

SET @point = ST_GeomFromText('POINT(1 1)');
SELECT ST_AsText(@point);

The following is the output:

POINT(1 1)

The output shows the WKT representation of the Point geometry with coordinates (1, 1).

Example 2: Convert a LineString geometry to WKT

This example shows how to convert a LineString geometry to its WKT representation.

SET @line = ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)');
SELECT ST_AsText(@line);

The following is the output:

LINESTRING(0 0, 1 1, 2 2)

The output displays the WKT representation of the LineString geometry with three vertices at (0, 0), (1, 1), and (2, 2).

Example 3: Convert a Polygon geometry to WKT

This example demonstrates how to convert a Polygon geometry to its WKT representation.

SET @poly = ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
SELECT ST_AsText(@poly);

The following is the output:

POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))

The output shows the WKT representation of the Polygon geometry with four vertices at (0, 0), (10, 0), (10, 10), and (0, 10).

Example 4: Convert a MultiPolygon geometry to WKT

This example shows how to convert a MultiPolygon geometry to its WKT representation.

SET @multipoly = ST_GeomFromText('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0)), ((20 20, 30 20, 30 30, 20 30, 20 20)))');
SELECT ST_AsText(@multipoly);

The following is the output:

MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0)), ((20 20, 30 20, 30 30, 20 30, 20 20)))

The output displays the WKT representation of the MultiPolygon geometry, which consists of two polygons: one with vertices at (0, 0), (10, 0), (10, 10), and (0, 10), and another with vertices at (20, 20), (30, 20), (30, 30), and (20, 30).

Example 5: Convert a geometry from a table to WKT

This example demonstrates how to convert a geometry stored in a table to its WKT representation.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT PRIMARY KEY, poly GEOMETRY);
INSERT INTO polygons (id, poly) VALUES
    (1, ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));
SELECT id, ST_AsText(poly) AS wkt FROM polygons;

The following is the output:

+----+------------------------------------------+
| id | wkt                                      |
+----+------------------------------------------+
|  1 | POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))  |
+----+------------------------------------------+

In this example, a table named polygons is created with an id column and a poly column of type GEOMETRY. A polygon geometry is inserted into the table, and then the ST_AsText() function is used to convert the stored geometry to its WKT representation for each row in the table.

Related Functions

The following are some functions related to the MariaDB ST_AsText() function:

• The MariaDB ST_GeomFromText() function is used to convert a WKT representation into a geometry value.
• The MariaDB ST_AsBinary() function is used to convert a geometry value into its Well-Known Binary (WKB) representation.
• The MariaDB ST_GeomFromWKB() function is used to convert a WKB representation back into a geometry value.
• The MariaDB ST_AsGeoJSON() function is used to convert a geometry value into its GeoJSON representation.

Conclusion

The ST_AsText() function in MariaDB is a valuable tool for working with spatial data in a human-readable format. By converting geometry values to their WKT representation, spatial data can be easily shared, visualized, and analyzed by humans or applications that support the WKT format. Additionally, the WKT format is widely supported by various spatial data processing tools and libraries, making it a versatile choice for interoperability and data exchange.