How the AsWKB() function works in Mariadb?

The AsWKB() function is a spatial function that returns the Well-Known Binary (WKB) representation of a geometry value.

Posted on

The AsWKB() function in MariaDB is a spatial function that returns the Well-Known Binary (WKB) representation of a geometry value. This binary format is useful for efficient storage and retrieval of geometric data.

Syntax

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

AsWKB(geometry)

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

Examples

Example 1: Basic Usage of AsWKB()

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

SELECT AsWKB(Point(1, 2));

+----------------------------------------------+
| AsWKB(Point(1, 2))                           |
+----------------------------------------------+
| 0x0101000000000000000000F03F0000000000000040 |
+----------------------------------------------+

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

Example 2: WKB of a LineString

Here we retrieve the WKB format of a LineString.

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

+------------------------------------------------------------------------------------------+
| AsWKB(LineString(Point(1, 2), Point(3, 4)))                                              |
+------------------------------------------------------------------------------------------+
| 0x010200000002000000000000000000F03F000000000000004000000000000008400000000000001040     |
+------------------------------------------------------------------------------------------+

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

Example 3: WKB of a Polygon

This example converts a polygon into WKB format.

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

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| AsWKB(Polygon(LineString(Point(0, 0), Point(4, 0), Point(4, 4), Point(0, 4), Point(0, 0))))
                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x010300000001000000050000000000000000000000000000000000000000000000000010400000000000000000000000000000104000000000000010400000000000000000000000000000104000000000000000000000000000000000 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The output is the WKB representation of a square polygon.

Example 4: WKB of a MultiPoint

Converting a MultiPoint geometry to its WKB equivalent.

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

+----------------------------------------------------------------------------------------------------------+
| AsWKB(MultiPoint(Point(1, 1), Point(2, 2)))                                                              |
+----------------------------------------------------------------------------------------------------------+
| 0x0104000000020000000101000000000000000000F03F000000000000F03F010100000000000000000000400000000000000040 |
+----------------------------------------------------------------------------------------------------------+

The output is the WKB representation of multiple points.

Example 5: Using AsWKB() 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 WKB representation of each geometry:

SELECT AsWKB(g) FROM geometries;

+--------------------------------------------------------------------------------------+
| AsWKB(g)                                                                             |
+--------------------------------------------------------------------------------------+
| 0x0101000000000000000000F03F000000000000F03F                                         |
| 0x01020000000200000000000000000000000000000000000000000000000000F03F000000000000F03F |
+--------------------------------------------------------------------------------------+

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

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

  • MariaDB ST_AsText() function converts a geometry to its Well-Known Text (WKT) representation.
  • MariaDB ST_GeomFromWKB() function creates a geometry instance from a WKB representation.
  • MariaDB ST_AsGeoJSON() function converts a geometry into its GeoJSON format.

Conclusion

The AsWKB() function is a vital tool for handling spatial data within MariaDB. It provides a standardized binary format for representing geometric data, which can be particularly beneficial for applications that require efficient spatial operations and storage. Understanding how to use this function, along with its related functions, can greatly enhance the management of spatial data in your database applications.