How the MULTIPOINT() function works in Mariadb?

The MULTIPOINT() function is a spatial function that creates a geometry collection of points from a set of coordinates or point geometries. A point is a zero-dimensional object that represents a specific location in a two-dimensional or three-dimensional space. A multipoint is a collection of points that can be treated as a single geometry.

Syntax

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

MULTIPOINT(point[, point]...)

The function takes one or more arguments, which can be either coordinates or point geometries. Each coordinate argument consists of two or three numbers separated by spaces, representing the X, Y, and optionally Z values of the point. Each point geometry argument is a valid point expression enclosed by parentheses.

The function returns a multipoint geometry that contains all the points specified by the arguments. If any of the arguments is NULL, the function returns NULL.

Examples

Example 1: Creating a multipoint from coordinates

In this example, we use the MULTIPOINT() function to create a multipoint geometry from four coordinates:

SELECT 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(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 point geometries

In this example, we use the MULTIPOINT() function to create a multipoint geometry from four point geometries:

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

The output is the same as the previous example, since the function accepts both coordinates and point geometries as arguments.

Example 3: Creating a multipoint with Z values

In this example, we use the MULTIPOINT() function to create a multipoint geometry with Z values, which represent the height or depth of the points:

SELECT 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(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 4: Creating a multipoint with NULL values

In this example, we use the MULTIPOINT() function to create a multipoint geometry with some NULL values:

SELECT MULTIPOINT(1 1, NULL, 3 3, (4 4)) AS result;

The output is:

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

The output is NULL, since the function returns NULL if any of the arguments is NULL.

Conclusion

The MULTIPOINT() function is a useful function to create a geometry collection of points from a set of coordinates or point geometries. It can also handle Z values and NULL values. There are some other functions that can manipulate or access the multipoint geometries, such as ST_MultiPointFromText(), ST_MultiPointFromWKB(), ST_NumPoints(), and ST_PointN().