How the ST_AsWKB() function works in Mariadb?

The ST_AsWKB() function in MariaDB is used to convert a geometry value into its Well-Known Binary (WKB) representation, allowing for efficient storage and transfer of spatial data.

Posted on

The ST_AsWKB() function in MariaDB is used to convert a geometry value into its Well-Known Binary (WKB) representation. The WKB format is a compact binary representation of vector geometry data, and it is widely used for transferring and storing spatial data in databases and other systems.

Syntax

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

ST_AsWKB(geom)
  • geom: The geometry value that needs to be converted to its WKB representation.

The function returns a binary string that represents the WKB format of the input geometry.

Examples

Example 1: Convert a Point geometry to WKB

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

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

The following is the output:

0x0101000000000000000000f03f000000000000f03f

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

Example 2: Convert a LineString geometry to WKB

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

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

The following is the output:

0x0102000000030000000000000000000000000000000000000000000000f03f000000000000f03f0000000000000040000000000000004

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

Example 3: Convert a Polygon geometry to WKB

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

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

The following is the output:

0x0103000000010000000500000000000000000000000000000000000000000000000024400000000000000000000000000024400000000000002440000000000000244000000000000000000000000000000000

The output shows the WKB 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 WKB

This example shows how to convert a MultiPolygon geometry to its WKB 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_AsWKB(@multipoly);

The following is the output:

0x0103000000020000000500000000000000000000000000000000000000000000000024400000000000000000000000000024400000000000002440000000000000244000000000000000000000000000000005000000000000000000144000000000000014400000000000001440000000000000144000000000000014400000000000001440

The output displays the WKB 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 WKB

This example demonstrates how to convert a geometry stored in a table to its WKB 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_AsWKB(poly) AS wkb FROM polygons;

The following is the output:

+----+------------------------------------------------------------------+
| id | wkb                                                              |
+----+------------------------------------------------------------------+
|  1 | 0x0103000000010000000500000000000000000000000000000000000000000000000024400000000000000000000000000024400000000000002440000000000000244000000000000000000000000000000000 |
+----+------------------------------------------------------------------+

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_AsWKB() function is used to convert the stored geometry to its WKB representation for each row in the table.

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

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

Conclusion

The ST_AsWKB() function in MariaDB is a valuable tool for working with spatial data in a compact and efficient manner. By converting geometry values to their WKB representation, spatial data can be stored and transferred more efficiently, which is particularly useful when dealing with large datasets or in situations where bandwidth or storage space is limited. Additionally, the WKB format is widely supported by various spatial data processing tools and libraries, making it a versatile choice for interoperability.