How the MPointFromText() function works in Mariadb?

Posted on

The MPointFromText() function is a spatial function that returns a multipoint geometry value from a well-known text (WKT) representation. The function can also accept an optional second argument that specifies the spatial reference system identifier (SRID) for the geometry value.

Syntax

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

MPointFromText(wkt, [srid])

The wkt argument is a string that represents the WKT representation of the multipoint geometry value. The WKT representation follows the format:

MULTIPOINT(x1 y1, x2 y2, ..., xn yn)

where x and y are the coordinates of the points that make up the multipoint geometry value.

The srid argument is an optional integer that specifies the SRID for the geometry value. The SRID is a unique identifier that defines the spatial reference system (SRS) of the geometry value. If the srid argument is omitted, the function uses the default SRID of 0.

Examples

Example 1: Basic usage

The following example shows how to use the MPointFromText() function to return a multipoint geometry value from a WKT representation:

SELECT MPointFromText('MULTIPOINT(1 1, 2 2, 3 3)');

The output is:

MPointFromText('MULTIPOINT(1 1, 2 2, 3 3)')
-----------------------------------------
MULTIPOINT(1 1, 2 2, 3 3)

The function returns a multipoint geometry value that consists of three points with the coordinates (1, 1), (2, 2), and (3, 3).

Example 2: Using a different SRID

The following example shows how to use the MPointFromText() function with a different SRID to return a multipoint geometry value with a specific SRS:

SELECT MPointFromText('MULTIPOINT(1 1, 2 2, 3 3)', 4326);

The output is:

MPointFromText('MULTIPOINT(1 1, 2 2, 3 3)', 4326)
------------------------------------------------
MULTIPOINT(1 1, 2 2, 3 3)

The function returns a multipoint geometry value that has the SRID of 4326, which corresponds to the WGS 84 SRS. The WGS 84 SRS is a global SRS that uses latitude and longitude as the coordinates.

Example 3: Using a string as the wkt argument

The following example shows how to use the MPointFromText() function with a string as the wkt argument:

SELECT MPointFromText(CONCAT('MULTIPOINT(', '1 1, 2 2, 3 3', ')'));

The output is:

MPointFromText(CONCAT('MULTIPOINT(', '1 1, 2 2, 3 3', ')'))
----------------------------------------------------------
MULTIPOINT(1 1, 2 2, 3 3)

The function returns a multipoint geometry value, as it concatenates the string literals to form a valid WKT representation.

Example 4: Handling invalid arguments

The following example shows how the MPointFromText() function handles invalid arguments:

SELECT MPointFromText('MULTIPOINT(1 1, 2 2, 3)');

The output is:

MPointFromText('MULTIPOINT(1 1, 2 2, 3)')
---------------------------------------
NULL

The function returns NULL, as it cannot parse the string as a valid WKT representation. The third point is missing the y coordinate.

Example 5: Using the MPointFromText() function in a WHERE clause

The following example shows how to use the MPointFromText() function in a WHERE clause to filter the records based on the multipoint geometry value:

SELECT * FROM locations
WHERE location = MPointFromText('MULTIPOINT(1 1, 2 2, 3 3)');

The output is:

id | name    | location
---|---------|---------
1  | Point A | MULTIPOINT(1 1, 2 2, 3 3)

The query returns the record that has the same multipoint geometry value as the one specified by the MPointFromText() function.

Some of the functions that are related to the MPointFromText() function are:

  • MPoint():Returns a multipoint geometry value from a set of point geometry values.
  • MPointFromWKB():Returns a multipoint geometry value from a well-known binary (WKB) representation.
  • AsText():Returns the WKT representation of a geometry value.
  • AsBinary():Returns the WKB representation of a geometry value.

For example, the following query uses the MPoint() and AsText() functions to return the WKT representation of a multipoint geometry value that is constructed from three point geometry values:

SELECT AsText(MPoint(Point(1, 1), Point(2, 2), Point(3, 3)));

The output is:

AsText(MPoint(Point(1, 1), Point(2, 2), Point(3, 3)))
----------------------------------------------------
MULTIPOINT(1 1, 2 2, 3 3)

Conclusion

The MPointFromText() function is a useful function to get a multipoint geometry value from a WKT representation. The function can handle various types of arguments, such as string or concatenated string values. The function can also accept an optional SRID argument to specify the SRS of the geometry value. The function is related to other spatial functions, such as MPoint(), MPointFromWKB(), AsText(), and AsBinary() functions.