How the MultiPointFromText() function works in Mariadb?

The MultiPointFromText() function is a spatial function that creates a multipoint geometry from a well-known text (WKT) representation of the multipoint.

Posted on

The MultiPointFromText() function is a spatial function that creates a multipoint geometry from a well-known text (WKT) representation of the multipoint. A multipoint is a collection of points that can be treated as a single geometry. A well-known text is a standard format for representing spatial objects as text.

Syntax

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

MultiPointFromText(wkt[, srid])

The function takes two arguments, which are:

  • wkt: A string that contains the well-known text representation of the multipoint. The string must start with the keyword MULTIPOINT followed by a list of point coordinates or point geometries enclosed by parentheses. Each point coordinate consists of two or three numbers separated by spaces, representing the X, Y, and optionally Z values of the point. Each point geometry is a valid point expression enclosed by parentheses. For example:
'MULTIPOINT(1 1, 2 2, 3 3, 4 4)'
'MULTIPOINT((1 1), (2 2), (3 3), (4 4))'
'MULTIPOINT(1 1 10, 2 2 20, 3 3 30, 4 4 40)'
  • srid: An optional integer that specifies the spatial reference system identifier (SRID) of the multipoint. The SRID defines the coordinate system and the units of measurement of the spatial object. If the SRID is not specified, the function assumes the default SRID of 0, which means the coordinate system is undefined.

The function returns a multipoint geometry that contains all the points specified by the well-known text. If the well-known text is not valid or the SRID is not supported, the function returns NULL.

Examples

Example 1: Creating a multipoint from a well-known text with two-dimensional coordinates

In this example, we use the MultiPointFromText() function to create a multipoint geometry from a well-known text that contains four points with two-dimensional coordinates:

SELECT MultiPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)') AS result;

The output is:

+--------------------------------------------+
| result                                     |
+--------------------------------------------+
| 0x000000000104040000000000000000F03F000000 |
| 000000F03F00000000000000400000000000000040 |
| 000000000000840000000000000084000000000000 |
| 000094000000000000009400                   |
+--------------------------------------------+

The output is a binary representation of the multipoint geometry, which can be converted to a human-readable format using the ST_AsText() function:

SELECT ST_AsText(MultiPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)')) AS result;

The output is:

+-----------------------------+
| result                      |
+-----------------------------+
| MULTIPOINT(1 1,2 2,3 3,4 4) |
+-----------------------------+

Example 2: Creating a multipoint from a well-known text with three-dimensional coordinates

In this example, we use the MultiPointFromText() function to create a multipoint geometry from a well-known text that contains four points with three-dimensional coordinates:

SELECT MultiPointFromText('MULTIPOINT(1 1 10, 2 2 20, 3 3 30, 4 4 40)') AS result;

The output is:

+------------------------------------------------+
| result                                         |
+------------------------------------------------+
| 0x000000000105040000000000000000F03F0000000000 |
| 00F03F0000000000002440000000000000000040000000 |
| 0000004000000000000034400000000000000840000000 |
| 000008400000000000004E400000000000000940000000 |
| 000009400000000000005840                       |
+------------------------------------------------+

The output is a binary representation of the multipoint geometry with Z values, which can be converted to a human-readable format using the ST_AsText() function:

SELECT ST_AsText(MultiPointFromText('MULTIPOINT(1 1 10, 2 2 20, 3 3 30, 4 4 40)')) AS result;

The output is:

+--------------------------------------------+
| result                                     |
+--------------------------------------------+
| MULTIPOINT Z (1 1 10,2 2 20,3 3 30,4 4 40) |
+--------------------------------------------+

Example 3: Creating a multipoint from a well-known text with a specified SRID

In this example, we use the MultiPointFromText() function to create a multipoint geometry from a well-known text with a specified SRID of 4326, which corresponds to the World Geodetic System 1984 (WGS 84), a widely used coordinate system for geographic data:

SELECT MultiPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)', 4326) AS result;

The output is:

+---------------------------------------------+
| result                                      |
+---------------------------------------------+
| 0x000000000A040400000000000000000F03F000000 |
| 000000F03F00000000000000400000000000000040  |
| 000000000000840000000000000084000000000000  |
| 000094000000000000009400                    |
+---------------------------------------------+

The output is a binary representation of the multipoint geometry with the SRID of 4326, which can be converted to a human-readable format using the ST_AsText() function:

SELECT ST_AsText(MultiPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)', 4326)) AS result;

The output is:

+-----------------------------+
| result                      |
+-----------------------------+
| MULTIPOINT(1 1,2 2,3 3,4 4) |
+-----------------------------+

Example 4: Creating a multipoint from an invalid well-known text

In this example, we use the MultiPointFromText() function to create a multipoint geometry from an invalid well-known text that contains a syntax error:

SELECT MultiPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4') AS result;

The output is:

+--------+
| result |
+--------+
| NULL   |
+--------+

The output is NULL, since the well-known text is not valid and the function cannot parse it.

There are some other functions that are related to the MultiPointFromText() function, such as:

  • MultiPoint(): This function creates a multipoint geometry from a set of coordinates or point geometries. For example:
SELECT MultiPoint(1 1, 2 2, 3 3, 4 4) AS result;

The output is the same as the first example of the MultiPointFromText() function.

  • MultiPointFromWKB(): This function creates a multipoint geometry from a well-known binary (WKB) representation of the multipoint. For example:
SELECT MultiPointFromWKB(0x000000000104040000000000000000F03F000000000000F03F0000000000000040000000000000004000000000000008400000000000000840000000000000094000000000000009400) AS result;

The output is the same as the first example of the MultiPointFromText() function.

  • ST_NumPoints(): This function returns the number of points in a multipoint geometry. For example:
SELECT ST_NumPoints(MultiPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)')) AS result;

The output is:

+--------+
| result |
+--------+
| 4      |
+--------+
  • ST_PointN(): This function returns the N-th point in a multipoint geometry. For example:
SELECT ST_AsText(ST_PointN(MultiPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)'), 3)) AS result;

The output is:

+------------+
| result     |
+------------+
| POINT(3 3) |
+------------+

Conclusion

The MultiPointFromText() function is a useful function to create a multipoint geometry from a well-known text (WKT) representation of the multipoint.