How the MPolyFromWKB() function works in Mariadb?

The MPolyFromWKB() function is a spatial function that creates a MULTIPOLYGON object from a Well-Known Binary (WKB) representation.

Posted on

The MPolyFromWKB() function is a spatial function that creates a MULTIPOLYGON object from a Well-Known Binary (WKB) representation. The WKB format is a standard way of encoding geometric objects as a sequence of bytes. It can be used to store and exchange spatial data between different systems.

Syntax

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

MPolyFromWKB(wkb [, srid])

The function takes two arguments:

  • wkb: A binary string that represents a MULTIPOLYGON object in WKB format. This argument is mandatory.
  • srid: An optional argument that specifies the spatial reference system identifier (SRID) of the MULTIPOLYGON object. If not specified, the default value is 0, which means the object has no SRID.

The function returns a MULTIPOLYGON object if the input is valid, or NULL if the input is invalid or empty.

Examples

In this section, we will show some examples of how to use the MPolyFromWKB() function in MariaDB.

Example 1: Creating a simple multipolygon object

In this example, we will create a simple multipolygon object that consists of two polygons. The first polygon has four vertices: (0, 0), (0, 10), (10, 10), and (10, 0). The second polygon has three vertices: (20, 20), (20, 30), and (30, 30). We will use the ST_AsText() function to display the result in Well-Known Text (WKT) format, which is a human-readable representation of geometric objects.

SELECT ST_AsText(MPolyFromWKB(0x00000000060000000200000000030000000100000004000000000000000000000000000000000000000000000000000000000000000000000000000244000000000000024400000000000000000000000000000024400000000000000000000000000000000000000000000030000000100000003000000000000000000000000344000000000000034400000000000003440000000000000344000000000000034400000000000003440000000000000034400000000000003440));

MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0)),((20 20,20 30,30 30,20 20)))

Example 2: Creating a multipolygon object with holes

In this example, we will create a multipolygon object that has holes. A hole is a polygon that is contained within another polygon and defines an area that is excluded from the outer polygon. The WKB format encodes the number of rings (outer and inner) for each polygon, followed by the coordinates of each ring. The first ring is always the outer ring, and the subsequent rings are the inner rings (holes). The orientation of the rings also matters: the outer ring must be oriented in a counter-clockwise direction, while the inner rings must be oriented in a clockwise direction.

We will create a multipolygon object that consists of two polygons. The first polygon has an outer ring with four vertices: (0, 0), (0, 10), (10, 10), and (10, 0), and an inner ring (hole) with three vertices: (2, 2), (2, 8), and (8, 8). The second polygon has an outer ring with three vertices: (20, 20), (20, 30), and (30, 30), and no inner rings.

SELECT ST_AsText(MPolyFromWKB(0x00000000060000000200000000040000000200000004000000000000000000000000000000000000000000000000000000000000000000000000000244000000000000024400000000000000000000000000000024400000000000000000000000000000000000000000000003000000000000000000000000084000000000000008400000000000000840000000000000084000000000000000840000000000000084000000000000008400000000000000000000000000000030000000100000003000000000000000000000000344000000000000034400000000000003440000000000000344000000000000034400000000000003440000000000000034400000000000003440));

MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(2 2,8 8,2 8,2 2)),((20 20,20 30,30 30,20 20)))

Example 3: Creating a multipolygon object with a specified SRID

In this example, we will create a multipolygon object with a specified SRID. The SRID is a numeric identifier that defines the coordinate system and projection of the spatial object. For example, the SRID 4326 represents the World Geodetic System 1984 (WGS 84), which is a common coordinate system for geographic data. The SRID can be specified as the second argument of the MPolyFromWKB() function.

We will create a multipolygon object that consists of two polygons. The first polygon has four vertices: (-122.4194, 37.7749), (-118.2437, 34.0522), (-73.9352, 40.7306), and (-77.0369, 38.9072). The second polygon has three vertices: (139.6917, 35.6895), (151.2093, -33.8688), and (174.7633, -36.8485). These coordinates are in degrees of longitude and latitude, and we will use the SRID 4326 to indicate that they are in the WGS 84 coordinate system.

SELECT ST_AsText(MPolyFromWKB(0x0106000020E61000000200000001030000000100000004000000C182D9CEF7C55EC08716D9CEF4E13740C3F5285C8F855EC0E17A14AE47E13740E890A1AD2B055EC0F6285C8F4E13740C3F5285C8F855EC08716D9CEF4E13740C182D9CEF7C55EC08716D9CEF4E1374001030000000100000003000000C12A446B6F2D62E405DA4FDD5E2E3E40C12A446B6F2D62E40F6285C8F4E13740C3F5285C8F855EC0F6285C8F4E13740C12A446B6F2D62E405DA4FDD5E2E3E40, 4326));

MULTIPOLYGON(((-122.4194 37.7749,-118.2437 34.0522,-73.9352 40.7306,-77.0369 38.9072,-122.4194 37.7749)),((139.6917 35.6895,151.2093 -33.8688,174.7633 -36.8485,139.6917 35.6895)))

There are some other functions that are related to the MPolyFromWKB() function. Here are some of them:

  • ST_GeomFromWKB(): This function creates a geometry object from a WKB representation. It can handle any type of geometry, not just multipolygons.

  • ST_GeomFromText(): This function creates a geometry object from a WKT representation. It can also handle any type of geometry.

  • ST_AsWKB(): This function returns the WKB representation of a geometry object. It is the inverse of the ST_GeomFromWKB() function.

  • MPolyFromText(): This function creates a MULTIPOLYGON object from a WKT representation. It is similar to the MPolyFromWKB() function, but it takes a text string as input instead of a binary string.

Conclusion

In this article, we have learned how to use the MPolyFromWKB() function in MariaDB to create a MULTIPOLYGON object from a WKB representation. We have also seen some examples of how to use the function with different inputs and outputs. We have also learned about some related functions that can be used to create or manipulate spatial objects in MariaDB. Spatial functions are useful for working with geographic data and performing spatial analysis.