MySQL 8.4 Reference Manual Including MySQL NDB Cluster 8.4

14.16.9.2 Spatial Relation Functions That Use Minimum Bounding Rectangles

MySQL provides several MySQL-specific functions that test the relationship between minimum bounding rectangles (MBRs) of two geometries g1 and g2. The return values 1 and 0 indicate true and false, respectively.

The MBR (also known as the bounding box) for a two-dimensional geometry is the smallest rectangle which holds all points in the geometry, and so encloses the area between its greatest extents in both coordinate directions. In other words, it is the rectangle bounded by the points (min(x), min(y)), (min(x), max(y)), (max(x), max(y)), and (max(x), min(y)), where min() and max() represent the geometry's minimum and maximum x-coordinate or y-coordinate, respectively.

When speaking of relationships between geometries, it is important to distinguish between containment and covering, as described here:

Let us define a rectangle g1 and points p1, p2, and p3 using the SQL statements shown here: 

SET 
  @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'),

  @p1 = ST_GeomFromText('Point(1 1)'),
  @p2 = ST_GeomFromText('Point(3 3)'),
  @p3 = ST_GeomFromText('Point(5 5)');

g1 contains and covers p1; p1 is entirely within g1 and does not touch any of its boundaries, as we can see from the SELECT statement shown here: 

mysql> SELECT
    ->   ST_Contains(@g1, @p1), ST_Within(@p1, @g1),
    ->   MBRContains(@g1, @p1),
    ->   MBRCovers(@g1, @p1), MBRCoveredBy(@p1, @g1), 
    ->   ST_Disjoint(@g1, @p1), ST_Intersects(@g1, @p1)\G
*************************** 1. row ***************************
  ST_Contains(@g1, @p1): 1
    ST_Within(@p1, @g1): 1
  MBRContains(@g1, @p1): 1
    MBRCovers(@g1, @p1): 1
 MBRCoveredBy(@p1, @g1): 1
  ST_Disjoint(@g1, @p1): 0
ST_Intersects(@g1, @p1): 1
1 row in set (0.01 sec)

Using the same query with @p2 in place of @p1, we can see that g2 covers p2, but does not contain it, because p2 is included in the boundary of g2, but does not lie within its interior. (That is, min(x) <= a <= max(x) and min(y) <= b <= max(y) are true, but min(x) < a < max(x) and min(y) < b < max(y) are not.) 

mysql> SELECT
    ->   ST_Contains(@g1, @p2), ST_Within(@p2, @g1),
    ->   MBRContains(@g1, @p2),
    ->   MBRCovers(@g1, @p2), MBRCoveredBy(@p2, @g1), 
    ->   ST_Disjoint(@g1, @p2), ST_Intersects(@g1, @p2)\G
*************************** 1. row ***************************
  ST_Contains(@g1, @p2): 0
    ST_Within(@p2, @g1): 0
  MBRContains(@g1, @p2): 0
    MBRCovers(@g1, @p2): 1
 MBRCoveredBy(@p2, @g1): 1
  ST_Disjoint(@g1, @p2): 0
ST_Intersects(@g1, @p2): 1
1 row in set (0.00 sec)

Executing the query—this time using @p3 rather than @p2 or @p1—shows us that p3 is disjoint from g1; the two geometries have no points in common, and g1 neither contains nor covers p3. ST_Disjoint(g1, p3) returns true; ST_Intersects(g1, p3) returns false. 

mysql> SELECT
    ->   ST_Contains(@g1, @p3), ST_Within(@p3, @g1),
    ->   MBRContains(@g1, @p3),
    ->   MBRCovers(@g1, @p3), MBRCoveredBy(@p3, @g1), 
    ->   ST_Disjoint(@g1, @p3), ST_Intersects(@g1, @p3)\G
*************************** 1. row ***************************
  ST_Contains(@g1, @p3): 0
    ST_Within(@p3, @g1): 0
  MBRContains(@g1, @p3): 0
    MBRCovers(@g1, @p3): 0
 MBRCoveredBy(@p3, @g1): 0
  ST_Disjoint(@g1, @p3): 1
ST_Intersects(@g1, @p3): 0
1 row in set (0.00 sec)

The function descriptions shown later in this section and in Section 14.16.9.1, “Spatial Relation Functions That Use Object Shapes” provide additional examples.

The bounding box of a point is interpreted as a point that is both boundary and interior.

The bounding box of a straight horizontal or vertical line is interpreted as a line where the interior of the line is also boundary. The endpoints are boundary points.

If any of the parameters are geometry collections, the interior, boundary, and exterior of those parameters are those of the union of all elements in the collection.

Functions in this section detect arguments in either Cartesian or geographic spatial reference systems (SRSs), and return results appropriate to the SRS.

Unless otherwise specified, functions in this section handle their geometry arguments as follows:

These MBR functions are available for testing geometry relationships:

MySQL Documentation Library