How the ST_LineFromWKB() function works in Mariadb?

The ST_LineFromWKB() function in MariaDB is used to create a linestring geometry from a well-known binary (WKB) representation of the geometry. This function is particularly useful when you need to convert binary geometry data into a spatial data type that can be used in various spatial operations and analyses.

Syntax

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

ST_LineFromWKB(wkb, srid)

• wkb: The well-known binary (WKB) representation of the linestring geometry.
• srid (optional): The spatial reference system identifier (SRID) for the geometry. If not provided, the geometry will have an SRID of 0, which represents an unknown spatial reference system.

The function returns a linestring geometry object based on the provided WKB representation and SRID.

Examples

Example 1: Creating a linestring from WKB

This example demonstrates how to create a linestring geometry from a WKB representation.

SELECT ST_LineFromWKB(ST_AsWKB(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)')));

The following is the output:

LINESTRING(0 0, 1 1, 2 0)

The function creates a linestring geometry from the provided WKB representation, which is obtained by converting the WKT representation 'LINESTRING(0 0, 1 1, 2 0)' to WKB using ST_AsWKB() and ST_GeomFromText().

Example 2: Creating a linestring with an SRID

This example shows how to create a linestring geometry with a specific spatial reference system identifier (SRID).

SELECT ST_LineFromWKB(ST_AsWKB(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)', 4326)), 4326);

The following is the output:

LINESTRING(0 0, 1 1, 2 0)

The function creates a linestring geometry from the provided WKB representation with an SRID of 4326, which represents the WGS 84 spatial reference system.

Example 3: Creating a linestring from a table

This example demonstrates how to create a linestring geometry from a table that stores WKB representations.

DROP TABLE IF EXISTS lines;
CREATE TABLE lines (id INT PRIMARY KEY, wkb GEOMETRY);
INSERT INTO lines (id, wkb) VALUES (1, ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)')), (2, ST_GeomFromText('LINESTRING(3 3, 4 4, 5 3)'));

SELECT id, ST_LineFromWKB(ST_AsWKB(wkb)) FROM lines;

The following is the output:

1, LINESTRING(0 0, 1 1, 2 0)
2, LINESTRING(3 3, 4 4, 5 3)

The function creates linestring geometries from the WKB representations stored in the wkb column of the lines table.

Example 4: Creating a linestring with an invalid WKB

This example shows how the function handles an invalid WKB representation.

SELECT ST_LineFromWKB(0x0000000000000000);

The following is the output:

NULL

When an invalid WKB representation is provided, the function returns NULL instead of a linestring geometry.

Example 5: Creating a linestring from a geometry collection

This example demonstrates how to create a linestring geometry from a geometry collection containing a linestring.

SELECT ST_LineFromWKB(ST_AsWKB(ST_GeometryN(ST_GeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1, 2 0))'), 1)));

The following is the output:

LINESTRING(0 0, 1 1, 2 0)

The function extracts the linestring geometry from the geometry collection using ST_GeometryN(), converts it to WKB using ST_AsWKB(), and then creates a linestring geometry from the resulting WKB representation.

Related Functions

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

• MariaDB ST_PointFromWKB() function is used to create a point geometry from a WKB representation.
• MariaDB ST_PolygonFromWKB() function is used to create a polygon geometry from a WKB representation.
• MariaDB ST_GeomFromWKB() function is used to create a geometry of any type from a WKB representation.
• MariaDB ST_AsWKB() function is used to convert a geometry to its WKB representation.
• MariaDB ST_GeometryType() function is used to get the geometry type of a given geometry.

Conclusion

The ST_LineFromWKB() function in MariaDB is a valuable tool for working with spatial data, particularly when dealing with linestring geometries in their binary representation. By allowing you to convert WKB representations of geometries into spatial data types, this function facilitates the integration of spatial data from various sources and enables further processing and analysis. The examples provided in this article demonstrate how to use the ST_LineFromWKB() function effectively in different scenarios, helping you manage and work with linestring geometries in your spatial applications and databases.