How the MBREqual() function works in Mariadb?

The MBREqual() function is a useful tool for performing spatial queries in Mariadb.

Posted on

The MBREqual() function is a useful tool for performing spatial queries in Mariadb. It allows you to check whether the minimum bounding rectangle (MBR) of one geometry is equal to the MBR of another geometry. This means that the two geometries have the same extent and orientation.

Syntax

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

MBREqual(g1, g2)

The function takes two arguments:

  • g1: A geometry value that represents the MBR of the first geometry. It can be a point, a line, a polygon, or a geometry collection.
  • g2: A geometry value that represents the MBR of the second geometry. It can be a point, a line, a polygon, or a geometry collection.

The function returns a boolean value that indicates whether the MBR of g1 is equal to the MBR of g2:

  • 1 (true): The MBR of g1 is equal to the MBR of g2.
  • 0 (false): The MBR of g1 is not equal to the MBR of g2.
  • NULL: Either g1 or g2 is NULL or invalid.

Examples

In this section, we will show some examples of how to use the MBREqual() function in different scenarios.

Example 1: Checking if a point is equal to another point

Suppose you have a table called places that stores the information of various places, such as their name, location, and type. The location column is a point geometry that represents the latitude and longitude of the place. You want to find the places that have the same location as a given point: (40, -120). You can use the MBREqual() function to check if the location of each place is equal to the MBR of the point. For example, you can execute the following statement:

SELECT name, location, type FROM places
WHERE MBREqual(location, ST_GeomFromText('POINT(40 -120)'));

This will return the name, location, and type of the places that have the same location as the point, or an empty result set if none of them do. For example, the result might look like this:

+----------+---------------------+--------+
| name     | location            | type   |
+----------+---------------------+--------+
| Reno     | POINT(40 -120)      | city   |
+----------+---------------------+--------+

Example 2: Checking if a polygon is equal to another polygon

Suppose you have a table called countries that stores the information of various countries, such as their name, area, and boundary. The boundary column is a polygon geometry that represents the outline of the country. You want to find the countries that have the same boundary as a given polygon: POLYGON((0 0, 0 10, 10 10, 10 0, 0 0)). You can use the MBREqual() function to check if the boundary of each country is equal to the MBR of the polygon. For example, you can execute the following statement:

SELECT name, area, boundary FROM countries
WHERE MBREqual(boundary, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));

This will return the name, area, and boundary of the countries that have the same boundary as the polygon, or an empty result set if none of them do. For example, the result might look like this:

+----------+----------+----------------------------------------------------------------+
| name     | area     | boundary                                                       |
+----------+----------+----------------------------------------------------------------+
| Nauru    | 21       | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))                         |
+----------+----------+----------------------------------------------------------------+

Example 3: Checking if a line is equal to another line

Suppose you have a table called roads that stores the information of various roads, such as their name, length, and path. The path column is a linestring geometry that represents the route of the road. You want to find the roads that have the same path as a given line: LINESTRING(0 0, 10 10). You can use the MBREqual() function to check if the path of each road is equal to the MBR of the line. For example, you can execute the following statement:

SELECT name, length, path FROM roads
WHERE MBREqual(path, ST_GeomFromText('LINESTRING(0 0, 10 10)'));

This will return the name, length, and path of the roads that have the same path as the line, or an empty result set if none of them do. For example, the result might look like this:

+----------+--------+---------------------------------------------------------------+
| name     | length | path                                                          |
+----------+--------+---------------------------------------------------------------+
| Road A   | 14.14  | LINESTRING(0 0, 10 10)                                        |
+----------+--------+---------------------------------------------------------------+

There are some other functions that are related to the MBREqual() function and can be used to perform other spatial queries in Mariadb. Here are some of them:

  • MBRIntersects(): This function returns whether the MBRs of two geometries intersect with each other.
  • MBRWithin(): This function returns whether the MBR of one geometry is within the MBR of another geometry.
  • MBRDisjoint(): This function returns whether the MBRs of two geometries are disjoint from each other.
  • MBRContains(): This function returns whether the MBR of one geometry contains the MBR of another geometry.

Conclusion

The MBREqual() function is a powerful and flexible function that can help you perform spatial queries in Mariadb. You can use it to check whether the MBR of one geometry is equal to the MBR of another geometry. This means that the two geometries have the same extent and orientation. You can also use some other related functions to perform other spatial operations, such as intersection, within, disjoint, or contain. By using these functions, you can achieve a better analysis and understanding of your spatial data.