How the IsClosed() function works in Mariadb?

The MariaDB IsClosed() function is used to determine if a given LINESTRING’s start and end points are the same. This function is essential in geographic information systems (GIS) when working with linear features to ensure they form a closed loop.

Syntax

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

IsClosed(g)

• g is the LINESTRING geometry to be checked.

The function returns 1 if the LINESTRING’s start and end points are the same, indicating a closed shape. If they are not the same, it returns 0, indicating the shape is not closed. Before MariaDB 10.1.5, it returns NULL if not given a LINESTRING. After MariaDB 10.1.5, it returns -1 for non-LINESTRING geometries.

Examples

Example 1: Closed LINESTRING

To check if a LINESTRING is closed:

SELECT IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 0 0)'));

The output for this statement is:

+--------------------------------------------------------+
| IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 0 0)')) |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+

This indicates that the LINESTRING is closed.

Example 2: Open LINESTRING

To check if a LINESTRING is not closed:

SELECT IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 1 0)'));

The output for this statement is:

+--------------------------------------------------------+
| IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 1 0)')) |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+

This indicates that the LINESTRING is not closed.

Example 3: Non-LINESTRING Geometry

To demonstrate the return value for a non-LINESTRING geometry:

SELECT IsClosed(ST_GeomFromText('POINT(0 0)'));

The output for this statement is:

+-----------------------------------------+
| IsClosed(ST_GeomFromText('POINT(0 0)')) |
+-----------------------------------------+
|                                      -1 |
+-----------------------------------------+

This result is consistent with the behavior after MariaDB 10.1.5.

Example 4: Invalid Geometry

To show the result for an invalid geometry input:

SELECT IsClosed(ST_GeomFromText('INVALID'));

The output for this statement is:

+--------------------------------------+
| IsClosed(ST_GeomFromText('INVALID')) |
+--------------------------------------+
|                                   -1 |
+--------------------------------------+

The function returns -1 due to the invalid geometry input.

Example 5: Multiple Geometries

To check multiple geometries in a single query:

SELECT IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 0 0)')),
       IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 1 0)'));

The output for this statement is:

+--------------------------------------------------------+--------------------------------------------------------+
| IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 0 0)')) | IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 1 0)')) |
+--------------------------------------------------------+--------------------------------------------------------+
|                                                      1 |                                                      0 |
+--------------------------------------------------------+--------------------------------------------------------+

This result shows the closed status for two different LINESTRING geometries.

Related Functions

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

• MariaDB ST_IsClosed() function is used to check if a given spatial value is closed according to the SQL/MM standard.
• MariaDB ST_GeomFromText() function is used to create a geometry instance from a string.

Conclusion

The IsClosed() function in MariaDB is a valuable tool for GIS applications, allowing developers to verify the closure of linear geometries. Understanding its use is crucial for ensuring data integrity and correctness in spatial databases. Mastery of this function, along with its related functions, can greatly enhance the management and analysis of geometric data within MariaDB.