How the AsWKT() function works in Mariadb?

The AsWKT() function is a spatial function in MariaDB that returns the Well-Known Text (WKT) representation of a geometry.

Posted on

The AsWKT() function is a spatial function in MariaDB that returns the Well-Known Text (WKT) representation of a geometry. This textual format is widely used for exchanging geometry data as it is readable both by humans and machines.

Syntax

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

AsWKT(geometry)

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

Examples

Example 1: Basic Usage of AsWKT()

This example demonstrates how to get the WKT representation of a point.

SELECT AsWKT(Point(1, 2));

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

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

Example 2: WKT of a LineString

Here we retrieve the WKT format of a LineString.

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

+---------------------------------------------+
| AsWKT(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: WKT of a Polygon

This example converts a polygon into WKT format.

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

+---------------------------------------------------------------------------------------------+
| AsWKT(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: WKT of a MultiPoint

Converting a MultiPoint geometry to its WKT equivalent.

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

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

The output is the WKT representation of multiple points.

Example 5: Using AsWKT() 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 AsWKT(g) FROM geometries;

+---------------------+
| AsWKT(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 AsWKT() function:

  • MariaDB ST_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 AsWKT() 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.