How the MPolyFromText() function works in Mariadb?

The MPolyFromText() function is a spatial function that returns a multipolygon 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 MPolyFromText() function is as follows:

MPolyFromText(wkt, [srid])

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

MULTIPOLYGON(((x1 y1, x2 y2, ..., xn yn)), ((x1 y1, x2 y2, ..., xm ym)), ...)

where x and y are the coordinates of the vertices that make up the multipolygon geometry value. Each pair of parentheses represents a polygon, and each polygon can have one or more rings. The first ring is the outer boundary of the polygon, and the subsequent rings are the inner boundaries or holes of the polygon. The rings must be closed, meaning that the first and last coordinates must be the same.

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 MPolyFromText() function to return a multipolygon geometry value from a WKT representation:

SELECT MPolyFromText('MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))');

The output is:

MPolyFromText('MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))')
---------------------------------------------------------------------------------------
MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))

The function returns a multipolygon geometry value that consists of two polygons, each with one ring. The first polygon has the vertices (1, 1), (1, 2), (2, 2), (2, 1), and (1, 1). The second polygon has the vertices (3, 3), (3, 4), (4, 4), (4, 3), and (3, 3).

Example 2: Using a different SRID

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

SELECT MPolyFromText('MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))', 4326);

The output is:

MPolyFromText('MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))', 4326)
-------------------------------------------------------------------------------------------------
MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))

The function returns a multipolygon 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 MPolyFromText() function with a string as the wkt argument:

SELECT MPolyFromText(CONCAT('MULTIPOLYGON(((', '1 1, 1 2, 2 2, 2 1, 1 1', ')), ((', '3 3, 3 4, 4 4, 4 3, 3 3', ')))'));

The output is:

MPolyFromText(CONCAT('MULTIPOLYGON(((', '1 1, 1 2, 2 2, 2 1, 1 1', ')), ((', '3 3, 3 4, 4 4, 4 3, 3 3', ')))'))
----------------------------------------------------------------------------------------------------------------
MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))

The function returns a multipolygon 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 MPolyFromText() function handles invalid arguments:

SELECT MPolyFromText('MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1)), ((3 3, 3 4, 4 4, 4 3)))');

The output is:

MPolyFromText('MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1)), ((3 3, 3 4, 4 4, 4 3)))')
-----------------------------------------------------------------------------
NULL

The function returns NULL, as it cannot parse the string as a valid WKT representation. The rings are not closed, meaning that the first and last coordinates are not the same.

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

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

SELECT * FROM regions
WHERE region = MPolyFromText('MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))');

The output is:

id | name    | region
---|---------|-------
1  | Region A | MULTIPOLYGON(((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))

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

Related Functions

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

• MPolyFromWKB() function: Returns a multipolygon geometry value from a well-known binary (WKB) representation.
• MULTIPOLYGON() function: Returns a multipolygon geometry value from a set of polygon geometry values.
• AsText() function: Returns the WKT representation of a geometry value.
• AsBinary() function: Returns the WKB representation of a geometry value.