How the LineStringFromWKB() function works in Mariadb?

The LineStringFromWKB() function is a spatial function that converts a binary representation of a line to a geometry value.

Posted on

The LineStringFromWKB() function is a spatial function that converts a binary representation of a line to a geometry value. It can be used to create a line geometry from a well-known binary (WKB) format, such as 0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840. The LineStringFromWKB() function is a synonym for the ST_LineStringFromWKB() function and can be used interchangeably.

Syntax

The syntax of the LineStringFromWKB() function is as follows:

LineStringFromWKB(wkb, [srid])

The wkb is the binary representation of the line in the WKB format. It must start with a byte order marker, followed by a geometry type code, followed by a number of points, followed by a list of point coordinates. Each point coordinate consists of two or more numeric values, representing the x, y, and optionally z and m dimensions. For example, 0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840 is a valid WKB representation of a line.

The srid is an optional parameter that specifies the spatial reference system identifier (SRID) of the geometry. It can be any positive integer value that corresponds to a valid SRID in the spatial_ref_sys table. If the srid is not specified, the function assumes a default SRID of 0.

The LineStringFromWKB() function returns a geometry value that represents the line geometry constructed from the binary representation. If the input binary is not a valid WKB representation of a line, the function returns NULL.

Examples

In this section, we will show some examples of how to use the LineStringFromWKB() function in Mariadb.

Example 1: Creating a line geometry from a binary representation

The following example shows how to use the LineStringFromWKB() function to create a line geometry from a binary representation in the WKB format.

SELECT LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840);

The output is:

LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x0000000001030000000300000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840

As you can see, the LineStringFromWKB() function returns a geometry value that represents the line geometry constructed from the binary representation. The geometry value is displayed in a hexadecimal format, which can be converted to a human-readable format using the ST_AsText() function, as shown below:

SELECT ST_AsText(LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840));

The output is:

ST_AsText(LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840))
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LINESTRING(0 0,1 1,2 2)

Example 2: Creating a line geometry from a binary representation with a specified SRID

The following example shows how to use the LineStringFromWKB() function to create a line geometry from a binary representation in the WKB format with a specified SRID.

SELECT LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840, 4326);

The output is:

LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840, 4326)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x00000000010A0000000300000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840

As you can see, the LineStringFromWKB() function returns a geometry value that represents the line geometry constructed from the binary representation with a specified SRID of 4326, which is the SRID for the WGS 84 coordinate system. The geometry value is displayed in a hexadecimal format, which can be converted to a human-readable format using the ST_AsText() function, as shown below:

SELECT ST_AsText(LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840, 4326));

The output is:

ST_AsText(LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840, 4326))
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LINESTRING(0 0,1 1,2 2)

Example 3: Creating a line geometry from an invalid binary representation

The following example shows how to use the LineStringFromWKB() function to create a line geometry from an invalid binary representation in the WKB format.

SELECT LineStringFromWKB(0x0102000000020000000000000000000000000000000000000000000000000000000000F03F000000000000F03F);

The output is:

LineStringFromWKB(0x0102000000020000000000000000000000000000000000000000000000000000000000F03F000000000000F03F)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL

As you can see, the LineStringFromWKB() function returns NULL, because the input binary is not a valid WKB representation of a line. The input binary has an invalid number of points, which is 2 instead of 3 or more.

There are some other functions that are related to the LineStringFromWKB() function in Mariadb. They are:

  • The LineStringFromText() function: This function converts a text representation of a line to a geometry value. It is similar to the LineStringFromWKB() function, but it takes a well-known text (WKT) format as the input, such as 'LINESTRING(0 0, 1 1, 2 2)'.
  • The ST_AsText() function: This function converts a geometry value to a text representation in the WKT format. It is the inverse of the LineStringFromText() function. For example, ST_AsText(LineStringFromWKB(0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840)) returns 'LINESTRING(0 0, 1 1, 2 2)'.
  • The ST_AsWKB() function: This function converts a geometry value to a binary representation in the WKB format. It is the inverse of the LineStringFromWKB() function. For example, `ST_AsWKB(LineStringFromText(‘LINE