How the WITHIN() function works in Mariadb?

The WITHIN() function in MariaDB is a spatial function used to determine whether a geometry is within another geometry.

Posted on

The WITHIN() function in MariaDB is a spatial function used to determine whether a geometry is within another geometry. It returns 1 if the first geometry is completely inside the second one, otherwise, it returns 0.

Syntax

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

WITHIN(g1, g2)

Where g1 and g2 are geometry expressions.

Examples

Example 1: Basic Usage of WITHIN()

This example checks if a point is within a polygon.

SELECT WITHIN(Point(1, 1), Polygon(LineString(Point(0, 0), Point(3, 0), Point(3, 3), Point(0, 3), Point(0, 0)))) AS within_result;

Below is the output for the statement:

+---------------+
| within_result |
+---------------+
|             1 |
+---------------+

This indicates that the point is within the specified polygon.

Example 2: Using WITHIN() with a Table

First, let’s create a table with geometries and then use the WITHIN() function to find out if one geometry is within another.

DROP TABLE IF EXISTS example_geometries;
CREATE TABLE example_geometries (g1 GEOMETRY, g2 GEOMETRY);
INSERT INTO example_geometries VALUES (Point(1, 1), Polygon(LineString(Point(0, 0), Point(3, 0), Point(3, 3), Point(0, 3), Point(0, 0))));

Now, let’s query the table:

SELECT WITHIN(g1, g2) AS within_result FROM example_geometries;

Below is the output for the statement:

+---------------+
| within_result |
+---------------+
|             1 |
+---------------+

The output shows that the point is within the polygon for the geometry pair in the table.

Example 3: WITHIN() with Multiple Geometries

To check multiple points within a polygon:

SELECT WITHIN(Point(1, 1), g2) AS within_result1,
       WITHIN(Point(4, 4), g2) AS within_result2
FROM example_geometries;

The output will show which points are within the polygon.

+----------------+----------------+
| within_result1 | within_result2 |
+----------------+----------------+
|              1 |              0 |
+----------------+----------------+

Example 4: WITHIN() in a WHERE Clause

Using the WITHIN() function in a WHERE clause to filter results:

SELECT * FROM example_geometries WHERE WITHIN(g1, g2);

This will return rows where g1 is within g2.

Example 5: WITHIN() with Complex Geometries

Checking if a complex geometry is within another complex geometry:

SELECT WITHIN(LineString(Point(0, 0), Point(2, 2)), Polygon(LineString(Point(0, 0), Point(3, 0), Point(3, 3), Point(0, 3), Point(0, 0)))) AS within_result;

This will determine if the line string is within the polygon.

+---------------+
| within_result |
+---------------+
|             1 |
+---------------+

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

  • MariaDB CONTAINS() function is used to determine if a geometry contains another geometry.
  • MariaDB INTERSECTS() function returns 1 if two geometries intersect, otherwise 0.
  • MariaDB OVERLAPS() function checks if two geometries overlap each other.

Conclusion

The WITHIN() function is an essential tool for spatial data analysis in MariaDB, allowing for the determination of spatial relationships between geometries. It is particularly useful in geographic information systems (GIS) and other applications where spatial queries are fundamental. With the examples provided, you should have a clear understanding of how to use the WITHIN() function in your spatial data queries.