How the MultiLineStringFromWKB() function works in Mariadb?

The MultiLineStringFromWKB() function is a spatial function that creates a MULTILINESTRING object from a Well-Known Binary (WKB) representation.

Posted on

The MultiLineStringFromWKB() function is a spatial function that creates a MULTILINESTRING object from a Well-Known Binary (WKB) representation. The WKB format is a standard way of encoding geometric objects as a sequence of bytes. The WKB format for a MULTILINESTRING object is as follows:

0x0105000000<number of line strings><line string 1><line string 2>...

Each line string is encoded as follows:

0x0102000000<number of points><point 1><point 2>...

Each point is encoded as follows:

<x coordinate><y coordinate>

The function takes one or two arguments:

  • wkb: A binary string that represents a MULTILINESTRING object in WKB format. This argument is mandatory.
  • srid: An optional argument that specifies the spatial reference system identifier (SRID) of the MULTILINESTRING object. If not specified, the default value is 0, which means the object has no SRID.

The function returns a MULTILINESTRING object if the input is valid, or NULL if the input is invalid or empty.

Syntax

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

MultiLineStringFromWKB(wkb [, srid])

Examples

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

Example 1: Creating a simple multilinestring object

In this example, we will create a simple multilinestring object that consists of two line strings. The first line string has four points: (0, 0), (0, 10), (10, 10), and (10, 0). The second line string has three points: (20, 20), (20, 30), and (30, 30). We will use the ST_AsText() function to display the result in Well-Known Text (WKT) format, which is a human-readable representation of geometric objects.

SELECT ST_AsText(MultiLineStringFromWKB(0x0105000000020000000102000000040000000000000000000000000000000000000000000000000000000000000000000000000000000244000000000000024400000000000000000000000000000024400000000000000000000000000000000000000000000000102000000030000000000000000000000344000000000000034400000000000003440000000000000344000000000000034400000000000003440000000000000344000000000000003440));

MULTILINESTRING((0 0,0 10,10 10,10 0),(20 20,20 30,30 30))

Example 2: Creating a multilinestring object with a specified SRID

In this example, we will create a multilinestring object with a specified SRID. The SRID is a numeric identifier that defines the coordinate system and projection of the spatial object. For example, the SRID 4326 represents the World Geodetic System 1984 (WGS 84), which is a common coordinate system for geographic data. The SRID can be specified as a prefix to the WKB representation, using the syntax 0xSRID<WKB>. For example, 0x0000110E0101000000000000000000F03F000000000000F03F indicates that the point is in the WGS 84 coordinate system with the SRID 4326.

We will create a multilinestring object that consists of two line strings. The first line string has four points: (-122.4194, 37.7749), (-118.2437, 34.0522), (-73.9352, 40.7306), and (-77.0369, 38.9072). The second line string has three points: (139.6917, 35.6895), (151.2093, -33.8688), and (174.7633, -36.8485). These coordinates are in degrees of longitude and latitude, and we will use the SRID 4326 to indicate that they are in the WGS 84 coordinate system.

SELECT ST_AsText(MultiLineStringFromWKB(0x0000110E010500000002000000010200000004000000C182D9CEF7C55EC08716D9CEF4E13740C3F5285C8F855EC0E17A14AE47E13740E890A1AD2B055EC0F6285C8F4E13740C3F5285C8F855EC08716D9CEF4E13740C182D9CEF7C55EC08716D9CEF4E13740010200000003000000C12A446B6F2D62E405DA4FDD5E2E3E40C12A446B6F2D62E40F6285C8F4E13740C3F5285C8F855EC0F6285C8F4E13740C12A446B6F2D62E405DA4FDD5E2E3E40));

MULTILINESTRING((-122.4194 37.7749,-118.2437 34.0522,-73.9352 40.7306,-77.0369 38.9072),(139.6917 35.6895,151.2093 -33.8688,174.7633 -36.8485))

There are some other functions that are related to the MultiLineStringFromWKB() function. Here are some of them:

  • ST_GeomFromWKB(): This function creates a geometry object from a WKB representation. It can handle any type of geometry, not just multilinestrings. For example:

    SELECT ST_AsText(ST_GeomFromWKB(0x0101000000000000000000F03F000000000000F03F));

    POINT(1 1)

  • ST_GeomFromText(): This function creates a geometry object from a text representation. It can also handle any type of geometry. For example:

    SELECT ST_AsText(ST_GeomFromText('LINESTRING(0 0, 10 10)'));

    LINESTRING(0 0,10 10)

  • ST_AsWKB(): This function returns the WKB representation of a geometry object. It is the inverse of the ST_GeomFromWKB() function. For example:

    SELECT ST_AsWKB(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));

    0x010300000001000000040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000024400000000000002440000000000000000000000000000002440000000000000000000000000000000000000000000000

  • MultiLineStringFromText(): This function creates a MULTILINESTRING object from a text representation. It is similar to the MultiLineStringFromWKB() function, but it takes a text string as input instead of a binary string. For example:

    SELECT ST_AsText(MultiLineStringFromText('MULTILINESTRING((0 0, 0 10, 10 10, 10 0), (20 20, 20 30, 30 30))'));

    MULTILINESTRING((0 0,0 10,10 10,10 0),(20 20,20 30,30 30))

Conclusion

In this article, we have learned how to use the MultiLineStringFromWKB() function in MariaDB to create a MULTILINESTRING object from a WKB representation. We have also seen some examples of how to use the function with different inputs and outputs. We have also learned about some related functions that can be used to create or manipulate spatial objects in MariaDB. Spatial functions are useful for working with geographic data and performing spatial analysis.