How the ST_LineFromText() function works in Mariadb?

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

Syntax

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

ST_LineFromText(wkt, srid)

• wkt: The well-known text (WKT) 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 WKT representation and SRID.

Examples

Example 1: Creating a linestring from WKT

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

SELECT ST_LineFromText('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 WKT representation 'LINESTRING(0 0, 1 1, 2 0)'.

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_LineFromText('LINESTRING(0 0, 1 1, 2 0)', 4326);

The following is the output:

LINESTRING(0 0, 1 1, 2 0)

The function creates a linestring geometry from the provided WKT representation 'LINESTRING(0 0, 1 1, 2 0)' 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 WKT representations.

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

SELECT id, ST_LineFromText(wkt) 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 WKT representations stored in the wkt column of the lines table.

Example 4: Creating a linestring with an invalid WKT

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

SELECT ST_LineFromText('INVALID WKT');

The following is the output:

NULL

When an invalid WKT 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_LineFromText(ST_AsText(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() and ST_AsText(), and then creates a linestring geometry from the resulting WKT representation.

Related Functions

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

• MariaDB ST_PointFromText() function is used to create a point geometry from a WKT representation.
• MariaDB ST_PolygonFromText() function is used to create a polygon geometry from a WKT representation.
• MariaDB ST_GeomFromText() function is used to create a geometry of any type from a WKT representation.
• MariaDB ST_AsText() function is used to convert a geometry to its WKT representation.
• MariaDB ST_GeometryType() function is used to get the geometry type of a given geometry.

Conclusion

The ST_LineFromText() function in MariaDB is a valuable tool for working with spatial data, particularly when dealing with linestring geometries. By allowing you to convert textual 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_LineFromText() function effectively in different scenarios, helping you manage and work with linestring geometries in your spatial applications and databases.