How the MLineFromWKB() function works in Mariadb?

The MLineFromWKB() function is a useful tool for creating a multilinestring geometry from a well-known binary (WKB) representation.

Posted on

The MLineFromWKB() function is a useful tool for creating a multilinestring geometry from a well-known binary (WKB) representation. It can be used for various purposes, such as storing, querying, and manipulating spatial data.

Syntax

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

MLineFromWKB(wkb, [srid])

The function takes one or two arguments:

  • wkb: A binary value that represents the well-known binary representation of the multilinestring geometry. It can be any valid expression that returns a binary value, such as a column name, a literal, or a function. The well-known binary representation must follow the format specified by the Open Geospatial Consortium (OGC).
  • srid: An optional integer value that represents the spatial reference system identifier (SRID) of the multilinestring geometry. It can be any valid expression that returns an integer, such as a column name, a literal, or a function. The SRID must be a valid value in the spatial_ref_sys table, or 0 if the geometry has no SRID.

The function returns a multilinestring geometry value that represents the spatial object created from the well-known binary representation, with the specified SRID. If any of the arguments are NULL or invalid, the function returns NULL.

Examples

In this section, we will show some examples of how to use the MLineFromWKB() function in different scenarios.

Example 1: Creating a multilinestring geometry from a literal well-known binary representation

Suppose you want to create a multilinestring geometry from a literal well-known binary representation. You can use the MLineFromWKB() function to do so. For example, you can execute the following statement:

SELECT MLineFromWKB('0x000000000A00000002000000010000000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002440000000000002440000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003440000000000003440000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004440000000000004440000000000000000000000000000000000000000000000000000000000000000000000000000000000000004440000000000002440');

This will return a multilinestring geometry value that represents the spatial object created from the well-known binary representation, with no SRID.

Note that the well-known binary representation must follow the format specified by the OGC, which is:

  • A byte order indicator, which is 0 for big-endian or 1 for little-endian.
  • A geometry type indicator, which is 10 for multilinestring.
  • A number of linestring geometries, which is an unsigned 32-bit integer.
  • For each linestring geometry:
    • A geometry type indicator, which is 2 for linestring.
    • A number of points, which is an unsigned 32-bit integer.
    • For each point:
      • An x-coordinate, which is a double-precision floating point number.
      • A y-coordinate, which is a double-precision floating point number.

Example 2: Creating a multilinestring geometry from a column value

Suppose you have a table called paths that stores the information of various paths, such as their path_id, name, and wkb. The wkb column is a binary value that represents the well-known binary representation of the multilinestring geometry of the path. You want to create a multilinestring geometry from the wkb column value of each path, so that you can store, query, and manipulate the spatial data. You can use the MLineFromWKB() function to do so. For example, you can execute the following statement:

SELECT path_id, name, MLineFromWKB(wkb) AS geom FROM paths;

This will return the path_id, name, and the multilinestring geometry value of each path, or an empty result set if the table is empty.

Note that the multilinestring geometry value is a binary value that represents the spatial object in the internal format used by Mariadb. You can use some other functions to convert the binary value to other formats, such as ST_AsText() or ST_AsGeoJSON().

Example 3: Creating a multilinestring geometry from a well-known binary representation with a specified SRID

Suppose you want to create a multilinestring geometry from a well-known binary representation, with a specified SRID, such as 3857. The SRID is a numeric value that identifies the spatial reference system (SRS) of the geometry, which defines how the coordinates are projected on the earth’s surface. You can use the MLineFromWKB() function with the second argument to do so. For example, you can execute the following statement:

SELECT MLineFromWKB('0x000000000A00000002000000010000000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002440000000000002440000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003440000000000003440000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004440000000000004440000000000000000000000000000000000000000000000000000000000000000000000000000000000000004440000000000002440', 3857);

This will return a multilinestring geometry value that represents the spatial object created from the well-known binary representation, with the SRID 3857.

Note that the SRID is a numeric value that identifies the spatial reference system (SRS) of the geometry, which defines how the coordinates are projected on the earth’s surface. The SRID 3857 corresponds to the Web Mercator projection, which is a widely used standard for web mapping. You can use the ST_SRID() function to get the SRID of a geometry, or the ST_SetSRID() function to set the SRID of a geometry.

There are some other functions that are related to the MLineFromWKB() function and can be used to perform other operations on multilinestring geometries in Mariadb. Here are some of them:

  • MLineFromText(): This function creates a multilinestring geometry from a well-known text (WKT) representation.
  • ST_NumGeometries(): This function returns the number of linestring geometries in a multilinestring geometry.
  • ST_GeometryN(): This function returns the nth linestring geometry in a multilinestring geometry.
  • ST_Length(): This function returns the length of a multilinestring geometry.

Conclusion

The MLineFromWKB() function is a powerful and flexible function that can help you create a multilinestring geometry from a well-known binary representation. It can be used for various purposes, such as storing, querying, and manipulating spatial data. You can also use some other related functions to create, convert, or access multilinestring geometries, such as MLineFromText, MLineToText, MLineToWKB, ST_NumGeometries, ST_GeometryN, or ST_Length. By using these functions, you can achieve a better analysis and understanding of your spatial data.