How the MBROverlaps() function works in Mariadb?

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

Posted on

The MBROverlaps() 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 overlaps with the MBR of another geometry. This means that the two geometries share some points in common, but not all of them.

Syntax

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

MBROverlaps(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 overlaps with the MBR of g2:

  • 1 (true): The MBR of g1 overlaps with the MBR of g2.
  • 0 (false): The MBR of g1 does not overlap with 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 MBROverlaps() function in different scenarios.

Example 1: Checking if a point overlaps with a rectangle

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 overlap with a rectangular area defined by two points: (40, -120) and (50, -110). You can use the MBROverlaps() function to check if the location of each place overlaps with the MBR of the rectangle. For example, you can execute the following statement:

SELECT name, location, type FROM places
WHERE MBROverlaps(location, ST_GeomFromText('LINESTRING(40 -120, 50 -110)'));

This will return the name, location, and type of the places that overlap with the rectangular area, or an empty result set if none of them do. For example, the result might look like this:

+----------+---------------------+--------+
| name     | location            | type   |
+----------+---------------------+--------+
| Seattle  | POINT(47.6062 -122.3321) | city   |
| Portland | POINT(45.5051 -122.6750) | city   |
| Boise    | POINT(43.6150 -116.2023) | city   |
| Yellowstone | POINT(44.4280 -110.5885) | park   |
+----------+---------------------+--------+

Note that the point (40, -120) does not overlap with the rectangle, because it is on the boundary of the rectangle, not inside it.

Example 2: Checking if a polygon overlaps with 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 overlap with the boundary of China. You can use the MBROverlaps() function to check if the boundary of each country overlaps with the MBR of the boundary of China. For example, you can execute the following statement:

SELECT c1.name, c1.area, c1.boundary FROM countries c1
JOIN countries c2 ON c2.name = 'China'
WHERE MBROverlaps(c1.boundary, c2.boundary);

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

+----------+----------+----------------------------------------------------------------+
| name     | area     | boundary                                                       |
+----------+----------+----------------------------------------------------------------+
| Mongolia | 1564110  | POLYGON((87.751264 49.297198, ... , 87.751264 49.297198))      |
| India    | 3287263  | POLYGON((68.176645 23.691965, ... , 68.176645 23.691965))      |
| Nepal    | 147181  | POLYGON((80.088425 28.794470, ... , 80.088425 28.794470))      |
| Pakistan | 881912  | POLYGON((60.874248 23.691965, ... , 60.874248 23.691965))      |
+----------+----------+----------------------------------------------------------------+

Note that the countries that are within the boundary of China, such as Hong Kong, Macau, and Taiwan, do not overlap with the boundary of China, because their MBRs are contained by the MBR of China, not overlapping with it.

Example 3: Checking if a line overlaps with a circle

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 overlap with a circular area with a radius of 10 kilometers and a center at (30, -100). You can use the MBROverlaps() function to check if the MBR of the path of each road overlaps with the MBR of the circle. For example, you can execute the following statement:

SELECT name, length, path FROM roads
WHERE MBROverlaps(path, ST_Buffer(ST_GeomFromText('POINT(30 -100)'), 10));

This will return the name, length, and path of the roads that overlap with the circular area, or an empty result set if none of them do. For example, the result might look like this:

+----------+--------+---------------------------------------------------------------+
| name     | length | path                                                          |
+----------+--------+---------------------------------------------------------------+
| Route 66 | 3940   | LINESTRING(34.746480 -92.289590, ... , 34.052230 -118.243680) |
| I-10     | 2460   | LINESTRING(30.396030 -88.885310, ... , 34.073620 -118.400360) |
| Road B   | 12.36  | LINESTRING(30 -100, 30.1 -99.9)                               |
+----------+--------+---------------------------------------------------------------+

Note that the line (30, -100) to (30.1, -99.9) overlaps with the circle, because it crosses the boundary of the circle, not because it is inside the circle.

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

  • MBREqual(): This function returns whether the MBRs of two geometries are equal to 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 MBROverlaps() 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 overlaps with the MBR of another geometry. This means that the two geometries share some points in common, but not all of them. You can also use some other related functions to perform other spatial operations, such as equal, within, disjoint, or contain. By using these functions, you can achieve a better analysis and understanding of your spatial data.