How the MultiPolygonFromWKB() function works in Mariadb?

The MultiPolygonFromWKB() function is a spatial function that converts a well-known binary (WKB) representation of a multipolygon geometry into a multipolygon value. A multipolygon is a geometry that consists of one or more polygons, each of which may have one or more holes. This function is useful for importing or exporting spatial data from or to other applications that use the WKB format.

Syntax

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

MultiPolygonFromWKB(wkb [, srid])

The function takes two arguments:

• wkb: A binary string that represents a multipolygon geometry in WKB format. This argument is mandatory.
• srid: An integer that represents the spatial reference system identifier (SRID) of the multipolygon value. This argument is optional. If omitted, the default SRID of 0 is used.

The function returns a multipolygon value that corresponds to the WKB representation. If the input is not a valid WKB representation of a multipolygon geometry, the function returns NULL.

Examples

Example 1: Creating a multipolygon value from a WKB representation

In this example, we use the MultiPolygonFromWKB() function to create a multipolygon value from a WKB representation. We use the ST_AsText() function to display the result in a human-readable format.

SELECT ST_AsText(MultiPolygonFromWKB(0x0106000000020000000103000000010000000500000000000000000000000000000000000000000000000000000000000000000000000008400000000000000840000000000000084000000000000008400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000103000000010000000500000000000000000010400000000000001040000000000000104000000000000010400000000000001040000000000000104000000000000010400000000000001040000000000000104000000000000010400000000000001040));

The output is:

MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0)),((4 4,4 7,7 7,7 4,4 4)))

The WKB representation consists of a header that indicates the endianness, the geometry type, and the number of polygons, followed by the WKB representations of each polygon. The endianness is 0x01 for little endian and 0x00 for big endian. The geometry type is 0x06 for multipolygon. The number of polygons is 0x02 for two polygons. Each polygon consists of a header that indicates the number of linear rings, followed by the WKB representations of each linear ring. The number of linear rings is 0x01 for one linear ring. Each linear ring consists of a header that indicates the number of points, followed by the coordinates of each point. The number of points is 0x05 for five points. The coordinates of each point are written as two double-precision floating-point numbers, which are eight bytes each.

Example 2: Creating a multipolygon value from a WKB representation with a specified SRID

In this example, we use the MultiPolygonFromWKB() function to create a multipolygon value from a WKB representation with a specified SRID. We use the ST_SRID() function to display the SRID of the result.

SELECT ST_SRID(MultiPolygonFromWKB(0x0106000020E6100000020000000103000000010000000500000000000000000000000000000000000000000000000000000000000000000008400000000000000840000000000000084000000000000008400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000103000000010000000500000000000000000010400000000000001040000000000000104000000000000010400000000000001040000000000000104000000000000010400000000000001040000000000000104000000000000010400000000000001040, 4326));

The output is:

4326

The WKB representation is similar to the previous example, except that it has a SRID of 0x20E61000 for 4326. The SRID is placed after the endianness and before the geometry type. The SRID indicates the coordinate system of the multipolygon value, which in this case is the World Geodetic System 1984 (WGS 84).

Example 3: Creating a multipolygon value from an invalid WKB representation

In this example, we use the MultiPolygonFromWKB() function to create a multipolygon value from an invalid WKB representation. We use the ISNULL() function to check if the result is NULL.

SELECT ISNULL(MultiPolygonFromWKB(0x0106000000030000000103000000010000000500000000000000000000000000000000000000000000000000000000000000000000000008400000000000000840000000000000084000000000000008400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000103000000010000000500000000000000000010400000000000001040000000000000104000000000000010400000000000001040000000000000104000000000000010400000000000001040000000000000104000000000000010400000000000001040001010000000000000000000000840000000000000840));

The output is:

1

The WKB representation is invalid because it has three polygons, but the number of polygons is 0x02 for two polygons. The function returns NULL, which is indicated by 1 in the output.

Related Functions

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

• MULTIPOLYGON(): This function creates a multipolygon value from a set of polygon values. For example:

  SELECT ST_AsText(MULTIPOLYGON(POLYGON((0 0, 0 3, 3 3, 3 0, 0 0)), POLYGON((4 4, 4 7, 7 7, 7 4, 4 4))));
  

  The output is:

  MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0)),((4 4,4 7,7 7,7 4,4 4)))

• ST_AsWKB(): This function converts a geometry value into a WKB representation. It is the inverse of the MultiPolygonFromWKB() function. For example:

  SELECT ST_AsWKB(MULTIPOLYGON(POLYGON((0 0, 0 3, 3 3, 3 0, 0 0)), POLYGON((4 4, 4 7, 7 7, 7 4, 4 4))));
  

  The output is:

  0x0106000000020000000103000000010000000500000000000000000000000000000000000000000000000000000000000000000000000008400000000000000840000000000000084000000000000008400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000103000000010000000500000000000000000010400000000000001040000000000000104000000000000010400000000000001040000000000000104000000000000010400000000000001040000000000000104000000000000010400000000000001040

Conclusion

The MultiPolygonFromWKB() function is a useful function for working with multipolygon geometries in Mariadb. It allows us to convert a WKB representation of a multipolygon geometry into a multipolygon value. We can also specify the SRID of the multipolygon value, or use the default SRID of 0. The function returns NULL if the input is not a valid WKB representation of a multipolygon geometry. There are some other functions that are related to the MultiPolygonFromWKB() function, such as MULTIPOLYGON(), ST_MultiPolygonFromText(), and ST_AsWKB(). These functions can help us to import, export, or manipulate spatial data in Mariadb.