How the ST_AREA() function works in Mariadb?

The ST_AREA() function in MariaDB is used to calculate the area of a given geometry value. It is part of the spatial extensions in MariaDB, which provide support for storing and manipulating spatial data.

Syntax

The syntax for the MariaDB ST_AREA() function is as follows:

ST_AREA(geom)

• geom: The geometry value for which the area needs to be calculated. This can be a point, linestring, polygon, multipolygon, or any other valid geometry type.

The function returns the area of the specified geometry as a double-precision value. If the input geometry is a polygon or multipolygon, the returned value represents the sum of the areas of all the polygons. For other geometry types, such as points or linestrings, the returned area is 0.

Examples

Example 1: Calculate the area of a polygon

This example demonstrates how to calculate the area of a simple polygon.

SET @poly = ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
SELECT ST_AREA(@poly);

The following is the output:

100.0

This example creates a polygon geometry with four vertices at (0, 0), (10, 0), (10, 10), and (0, 10), and then calculates its area using the ST_AREA() function. The result is 100.0, which is the area of a square with side lengths of 10 units.

Example 2: Calculate the area of a multipolygon

This example shows how to calculate the area of a multipolygon.

SET @multipoly = ST_GeomFromText('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0)), ((20 20, 30 20, 30 30, 20 30, 20 20)))');
SELECT ST_AREA(@multipoly);

The following is the output:

300.0

In this example, a multipolygon geometry is created with two polygons: one square with side lengths of 10 units, and another square with side lengths of 10 units, but shifted to (20, 20). The ST_AREA() function calculates the combined area of both polygons, which is 100.0 + 200.0 = 300.0.

Example 3: Calculate the area of a point

This example demonstrates that the ST_AREA() function returns 0 for non-polygon geometries like points.

SET @point = ST_GeomFromText('POINT(1 1)');
SELECT ST_AREA(@point);

The following is the output:

0.0

Since a point has no area, the ST_AREA() function returns 0.0 for the specified point geometry.

Example 4: Calculate the area of a linestring

This example shows that the ST_AREA() function also returns 0 for linestring geometries.

SET @line = ST_GeomFromText('LINESTRING(0 0, 10 10)');
SELECT ST_AREA(@line);

The following is the output:

0.0

Similar to points, linestrings have no area, so the ST_AREA() function returns 0.0 for the specified linestring geometry.

Example 5: Calculate the area of a geometry from a table

This example demonstrates how to calculate the area of a geometry stored in a table.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT PRIMARY KEY, poly GEOMETRY);
INSERT INTO polygons (id, poly) VALUES
    (1, ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));
SELECT id, ST_AREA(poly) AS area FROM polygons;

The following is the output:

+----+-------+
| id | area  |
+----+-------+
|  1 | 100.0 |
+----+-------+

In this example, a table named polygons is created with an id column and a poly column of type GEOMETRY. A polygon geometry is inserted into the table, and then the ST_AREA() function is used to calculate the area of the stored geometry for each row in the table.

Related Functions

The following are some functions related to the MariaDB ST_AREA() function:

• The MariaDB ST_DISTANCE() function is used to calculate the distance between two geometries.
• The MariaDB ST_BUFFER() function is used to create a buffer polygon around a geometry.
• The MariaDB ST_INTERSECTION() function is used to calculate the intersection between two geometries.
• The MariaDB ST_UNION() function is used to combine multiple geometries into a single geometry.
• The MariaDB ST_CONTAINS() function is used to check if a geometry contains another geometry.

Conclusion

The ST_AREA() function in MariaDB is a useful spatial function for calculating the area of polygon and multipolygon geometries. It provides a straightforward way to obtain the area of a given geometry value, which can be helpful in various applications that involve spatial data analysis and processing.