How the MLineFromText() function works in Mariadb?

The MLineFromText() function is a useful tool for creating a multilinestring geometry from a well-known text (WKT) representation.

Posted on

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

Syntax

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

MLineFromText(wkt, [srid])

The function takes one or two arguments:

  • wkt: A string value that represents the well-known text representation of the multilinestring geometry. It can be any valid expression that returns a string, such as a column name, a literal, or a function. The well-known text 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 text 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 MLineFromText() function in different scenarios.

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

Suppose you want to create a multilinestring geometry from a literal well-known text representation, such as ‘MULTILINESTRING((0 0, 10 10), (20 20, 30 30))’. You can use the MLineFromText() function to do so. For example, you can execute the following statement:

SELECT MLineFromText('MULTILINESTRING((0 0, 10 10), (20 20, 30 30))');

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

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

  • The keyword MULTILINESTRING followed by a left parenthesis (.
  • One or more linestring geometries, separated by commas ,.
  • Each linestring geometry is enclosed by a pair of parentheses ( and ).
  • Each linestring geometry consists of two or more points, separated by commas ,.
  • Each point consists of two or more coordinates, separated by spaces.
  • The first and the last point of each linestring geometry may or may not be the same, depending on whether the linestring is closed or not.
  • A right parenthesis ) followed by the end of the input.

Example 2: Creating a multilinestring geometry from a column value

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

SELECT route_id, name, MLineFromText(wkt) AS geom FROM routes;

This will return the route_id, name, and the multilinestring geometry value of each route, 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 text representation with a specified SRID

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

SELECT MLineFromText('MULTILINESTRING((0 0, 10 10), (20 20, 30 30))', 4326);

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

Note that the SRID is a numeric value that identifies the spatial reference system (SRS) of the geometry, which defines how the coordinates are interpreted on the earth’s surface. The SRID 4326 corresponds to the World Geodetic System 1984 (WGS 84), which is a widely used standard for geographic data. 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 MLineFromText() function and can be used to perform other operations on multilinestring geometries in Mariadb. Here are some of them:

  • MLineFromWKB(): This function creates a multilinestring geometry from a well-known binary (WKB) representation.
  • MLineToText(): This function returns the well-known text representation of a multilinestring geometry.
  • MLineToWKB(): This function returns the well-known binary representation of a multilinestring geometry.
  • 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 MLineFromText() function is a powerful and flexible function that can help you create a multilinestring geometry from a well-known text 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 MLineFromWKB, 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.