How the LINESTRING() function works in Mariadb?

The LINESTRING() function is a spatial function that creates a line geometry from a list of point coordinates. It can be used to construct a line geometry from two or more points, such as (0, 0), (1, 1), (2, 2). The LINESTRING() function is a synonym for the ST_LineString() function and can be used interchangeably.

Syntax

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

LINESTRING(point1, point2, ..., pointN)

The point1, point2, ..., pointN are the point coordinates that define the line geometry. They can be any valid point expressions in Mariadb, such as column values, literal values, or variables. Each point expression consists of two or more numeric values, representing the x, y, and optionally z and m dimensions. For example, (0, 0), (1, 1), (2, 2) are valid point expressions.

The LINESTRING() function returns a geometry value that represents the line geometry constructed from the point coordinates. If any of the point expressions is NULL, the function returns NULL.

Examples

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

Example 1: Creating a line geometry from literal point coordinates

The following example shows how to use the LINESTRING() function to create a line geometry from literal point coordinates.

SELECT LINESTRING(0, 0, 1, 1, 2, 2);

The output is:

LINESTRING(0, 0, 1, 1, 2, 2)
----------------------------
0x0000000001030000000300000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840

As you can see, the LINESTRING() function returns a geometry value that represents the line geometry constructed from the literal point coordinates. 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(LINESTRING(0, 0, 1, 1, 2, 2));

The output is:

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

Example 2: Creating a line geometry from column values

The following example shows how to use the LINESTRING() function to create a line geometry from column values. Suppose we have a table called points that stores the point coordinates, such as x, y, and z. The table has the following data:

We can use the LINESTRING() function to create a line geometry from the x, y, and z columns, as shown below:

SELECT id, LINESTRING(x, y, z) AS line FROM points;

The output is:

As you can see, the LINESTRING() function returns a geometry value that represents the line geometry constructed from the column values. 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 id, ST_AsText(LINESTRING(x, y, z)) AS line FROM points;

The output is:

Example 3: Creating a line geometry from variable values

The following example shows how to use the LINESTRING() function to create a line geometry from variable values. Suppose we have three variables that store the point coordinates, such as @p1, @p2, and @p3. We can use the LINESTRING() function to create a line geometry from the variable values, as shown below:

SET @p1 = (0, 0, 0);
SET @p2 = (1, 1, 1);
SET @p3 = (2, 2, 2);
SELECT LINESTRING(@p1, @p2, @p3);

The output is:

LINESTRING(@p1, @p2, @p3)
-------------------------
0x000000000103000000030000000000000000000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840

As you can see, the LINESTRING() function returns a geometry value that represents the line geometry constructed from the variable values. 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(LINESTRING(@p1, @p2, @p3));

The output is:

ST_AsText(LINESTRING(@p1, @p2, @p3))
------------------------------------
LINESTRING(0 0 0,1 1 1,2 2 2)

Related Functions

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

• The ST_AsText() function: This function converts a geometry value to a text representation in the well-known text (WKT) format. It is the inverse of the LINESTRING() function. For example, ST_AsText(LINESTRING(0, 0, 1, 1, 2, 2)) returns 'LINESTRING(0 0,1 1,2 2)'.
• The ST_AsWKB() function: This function converts a geometry value to a binary representation in the well-known binary (WKB) format. It is the inverse of the LineFromWKB() function. For example, ST_AsWKB(LINESTRING(0, 0, 1, 1, 2, 2)) returns 0x0102000000030000000000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840.

Conclusion

In this article, we have learned how the LINESTRING() function works in Mariadb. We have seen the syntax of the function, and some examples of how to use it with different types of point expressions. We have also learned about some related functions that can be used with the LINESTRING() function. The LINESTRING() function is a useful function that can help us create line geometries from point coordinates in Mariadb.