How the ST_OVERLAPS() function works in Mariadb?

The ST_OVERLAPS() function in MariaDB is used to determine whether two geometries overlap.

Posted on

When working with spatial data in MariaDB, one of the most useful tools in your arsenal is the ST_OVERLAPS() function. This powerful spatial relationship function helps you determine when two geometries share space in a specific way - when they overlap without one completely containing the other. Whether you’re building location-based services, geographic information systems, or analyzing spatial relationships, understanding ST_OVERLAPS() can significantly enhance your database capabilities.

Understanding Spatial Overlaps

The ST_OVERLAPS() function checks whether two geometries overlap with each other. But what exactly does “overlap” mean in spatial terms? Two geometries overlap when they share some interior space but neither completely contains the other. This is different from containment or touching relationships.

Imagine two neighboring countries where their borders cross and each claims part of the same territory - that’s a real-world example of overlap. In database terms, this function returns 1 (true) if the geometries overlap, and 0 (false) if they don’t.

Basic Syntax and Usage

The basic structure of the function is straightforward:

ST_OVERLAPS(geometry1, geometry2)

Both parameters must be valid geometry objects in MariaDB. Here’s a simple example comparing two polygons:

SELECT ST_OVERLAPS(
    ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'),
    ST_GeomFromText('POLYGON((3 3, 3 8, 8 8, 8 3, 3 3))')
) AS does_overlap;

This query will return 1 because the two squares overlap in the area from (3,3) to (5,5).

Comparing Different Geometry Types

ST_OVERLAPS() works with various geometry types, not just polygons. Let’s look at how it behaves with different combinations:

LineString and Polygon:

SELECT ST_OVERLAPS(
    ST_GeomFromText('LINESTRING(2 2, 6 6)'),
    ST_GeomFromText('POLYGON((3 3, 3 8, 8 8, 8 3, 3 3))')
) AS line_poly_overlap;

This returns 1 because the line passes through the interior of the polygon.

Point and Polygon:

SELECT ST_OVERLAPS(
    ST_GeomFromText('POINT(4 4)'),
    ST_GeomFromText('POLYGON((3 3, 3 8, 8 8, 8 3, 3 3))')
) AS point_poly_overlap;

This returns 0 because a point cannot overlap with a polygon - it’s either inside, on the boundary, or outside. Overlap requires both geometries to have some area in common.

Practical Applications in Queries

The real power of ST_OVERLAPS() becomes apparent when you use it in table queries. Imagine you have a table of land parcels and want to find all parcels that overlap with a proposed construction site:

SELECT parcel_id, parcel_name
FROM land_parcels
WHERE ST_OVERLAPS(parcel_geometry, ST_GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'));

This query efficiently filters parcels to only those sharing space with your construction area.

Combining with Other Spatial Functions

ST_OVERLAPS() often works in concert with other spatial functions. For example, you might first filter with ST_OVERLAPS() and then calculate the exact overlapping area:

SELECT
    a.parcel_id,
    ST_AREA(ST_INTERSECTION(a.parcel_geometry, b.project_area)) AS overlap_area
FROM land_parcels a, construction_projects b
WHERE ST_OVERLAPS(a.parcel_geometry, b.project_area);

This query finds all overlapping parcels and calculates exactly how much area overlaps with each construction project.

Performance Considerations

When working with large spatial datasets, ST_OVERLAPS() can be resource-intensive. To improve performance:

  1. Use spatial indexes on your geometry columns
  2. Consider first filtering with simpler checks like bounding box comparisons
  3. Limit your result set with other WHERE conditions before applying the overlap check
-- Example using a bounding box filter first
SELECT *
FROM large_spatial_table
WHERE MBRINTERSECTS(geom, ST_GeomFromText('POLYGON((...))'))
AND ST_OVERLAPS(geom, ST_GeomFromText('POLYGON((...))'));

Common Pitfalls and Solutions

A frequent mistake is confusing ST_OVERLAPS() with similar functions like ST_INTERSECTS() or ST_CONTAINS(). Remember:

  • ST_OVERLAPS() requires both geometries to have some interior space in common
  • It returns false if one geometry completely contains the other
  • It returns false if the geometries only touch at their boundaries

For example:

-- These rectangles touch but don't overlap
SELECT ST_OVERLAPS(
    ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'),
    ST_GeomFromText('POLYGON((5 0, 5 5, 10 5, 10 0, 5 0))')
) AS touching_rectangles;  -- Returns 0

Wrapping Up Spatial Overlaps

The ST_OVERLAPS() function in MariaDB provides precise control when you need to identify geometries that share interior space without complete containment. From urban planning to environmental studies, this function helps answer complex spatial questions efficiently. By combining it with other spatial functions and proper indexing strategies, you can build powerful geographic applications directly in your database.

Remember that spatial relationships can be subtle - always test your queries with known data to ensure you’re getting exactly the results you expect. With practice, ST_OVERLAPS() will become an indispensable tool in your spatial analysis toolkit.