How the AsText() function works in Mariadb?

The AsText() function, also known as ST_ASTEXT(), is a part of the spatial functions in MariaDB and is used to convert geometric data into Well-Known Text (WKT) representation.

Posted on

The AsText() function, also known as ST_ASTEXT(), is a part of the spatial functions in MariaDB and is used to convert geometric data into Well-Known Text (WKT) representation.

Syntax

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

AsText(geometry)

The geometry parameter is a geometric value of any type, such as POINT, LINESTRING, or POLYGON. The function returns the WKT representation of the geometry as a string.

Examples

Example 1: Converting a Point to Text

This example shows how to convert a geometric point into its WKT representation.

SELECT AsText(Point(1, 2));

+---------------------+
| AsText(Point(1, 2)) |
+---------------------+
| POINT(1 2)          |
+---------------------+

The output is the WKT representation of the point with coordinates (1,2).

Example 2: Converting a LineString to Text

Here we convert a LineString into its WKT format.

SELECT AsText(LineString(Point(1, 2), Point(3, 4)));

+----------------------------------------------+
| AsText(LineString(Point(1, 2), Point(3, 4))) |
+----------------------------------------------+
| LINESTRING(1 2,3 4)                          |
+----------------------------------------------+

The output is the WKT representation of a line segment from (1,2) to (3,4).

Example 3: Converting a Polygon to Text

This example demonstrates converting a polygon into WKT format.

SELECT AsText(Polygon(LineString(Point(0, 0), Point(4, 0), Point(4, 4), Point(0, 4), Point(0, 0))));

+----------------------------------------------------------------------------------------------+
| AsText(Polygon(LineString(Point(0, 0), Point(4, 0), Point(4, 4), Point(0, 4), Point(0, 0)))) |
+----------------------------------------------------------------------------------------------+
| POLYGON((0 0,4 0,4 4,0 4,0 0))                                                               |
+----------------------------------------------------------------------------------------------+

The output is the WKT representation of a square polygon.

Example 4: Converting a MultiPoint to Text

Converting a MultiPoint geometry to its WKT equivalent.

SELECT AsText(MultiPoint(Point(1, 1), Point(2, 2)));

+----------------------------------------------+
| AsText(MultiPoint(Point(1, 1), Point(2, 2))) |
+----------------------------------------------+
| MULTIPOINT(1 1,2 2)                          |
+----------------------------------------------+

The output is the WKT representation of multiple points.

Example 5: Using AsText() with a Table

First, create a table with spatial data:

DROP TABLE IF EXISTS geometries;
CREATE TABLE geometries (g GEOMETRY);
INSERT INTO geometries VALUES (Point(1, 1)), (LineString(Point(0, 0), Point(1, 1)));

Now, retrieve the WKT representation of each geometry:

SELECT AsText(g) FROM geometries;

+---------------------+
| AsText(g)           |
+---------------------+
| POINT(1 1)          |
| LINESTRING(0 0,1 1) |
+---------------------+

The output shows the WKT representation of the geometries stored in the table.

Below are a few functions related to the MariaDB AsText() function:

  • MariaDB GeomFromText() function is used to create a geometry instance from a WKT representation.
  • MariaDB ST_AsBinary() function returns the Well-Known Binary (WKB) representation of a geometry.
  • MariaDB ST_AsGeoJSON() function converts a geometry into its GeoJSON format.

Conclusion

The AsText() function is an essential tool for working with spatial data in MariaDB. It allows for the conversion of geometric data types into a readable string format, which is particularly useful for debugging, logging, or simply understanding the spatial data you’re working with. By mastering this function and its related counterparts, you can effectively manage and utilize spatial data within your databases.