How the AREA() function works in Mariadb?

MariaDB is a robust, scalable, and versatile database system that supports a wide range of functions, including geospatial functions. One such function is the AREA() function, which is used to calculate the area of a polygon. This article will delve into the syntax, usage, examples, and related functions of the AREA() function in MariaDB.

Introduction

The AREA() function in MariaDB is a geospatial function that calculates the area of a polygon. It is particularly useful in applications where spatial data is crucial, such as geographic information systems (GIS), urban planning, and environmental science.

Syntax

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

SELECT AREA(geo_shape) AS area_result;

Where geo_shape is a geometry type column or an expression that returns a geometry type. The AREA() function returns a numeric value representing the area of the polygon in square units.

Examples

Example 1: Simple Polygon

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

SELECT AREA(ST_PolyFromText('POLYGON((15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40))')) AS area;

+------+
| area |
+------+
|  275 |
+------+

The output shows that the area of the polygon is 275 square units.

Example 2: Complex Polygon

This example calculates the area of a more complex polygon with an inner ring.

SET @polygon = 'Polygon((0 0,0 5,5 0,0 0),(2 2,2 5,5 2,2 2))';
SELECT AREA(ST_GeomFromText(@polygon)) AS area;

+------+
| area |
+------+
|    8 |
+------+

The area of the complex polygon is 8 square units, excluding the area of the inner ring.

Example 3: Using Variables

This example shows how to use variables to store polygon definitions and calculate their area.

SET @poly = ST_PolygonFromText('POLYGON((0 40, 0 20, 6 30, 12 20, 12 40, 0 40))');
SELECT AREA(@poly) AS area;

+------+
| area |
+------+
|  180 |
+------+

The area calculated for the polygon stored in the variable @poly is 180 square units.

Example 4: Empty Polygon

Attempting to calculate the area of an Empty polygon will result 0.

SELECT AREA(ST_PolyFromText('POLYGON((0 0, 0 0, 0 0, 0 0))')) AS area;

+------+
| area |
+------+
|    0 |
+------+

The error indicates that the polygon definition is invalid.

Example 5: Polygon with Table Data

This example demonstrates calculating the area of polygons stored in a table.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (shape GEOMETRY);
INSERT INTO polygons (shape) VALUES (ST_PolyFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'));
SELECT AREA(shape) AS area FROM polygons;

+------+
| area |
+------+
|  100 |
+------+

The output shows that the area of the polygon from the table is 100 square units.

Below are a few functions related to the MariaDB AREA() function:

MariaDB ST_AREA() function is used to calculate the area of a polygon and is synonymous with AREA().
MariaDB ST_GeomFromText() function is used to create a geometry instance from a string.
MariaDB ST_PolyFromText() function is used to create a polygon from a Well-Known Text (WKT) representation.

Conclusion

The AREA() function in MariaDB is a powerful tool for calculating the area of polygons in spatial data analysis. By understanding its syntax and usage, as demonstrated through various examples, users can effectively incorporate this function into their geospatial queries and applications. Remember to handle spatial data with care and ensure that polygon definitions are valid to avoid errors during calculations.