Identify Overlapping Polygons in a Defined Region

Find Polygons That Intersect a Given Area

Write a MySQL query to select all records from the GeoData table where the stored polygon intersects a specified polygon.

Solution:

-- Retrieve records from the GeoData table where the 'area' polygon intersects with a defined polygon.
SELECT 

    -- Select the 'name' column to display the name of the geographic entity.
    name

-- Retrieve data from the GeoData table.
FROM GeoData

    -- Use the ST_Intersects function to check if the 'area' column (a POLYGON) intersects with 
    -- the specified polygon created using ST_GeomFromText. Only rows where the intersection condition is true are returned.
    WHERE ST_Intersects(area, ST_GeomFromText('POLYGON((-73.970 40.770, -73.960 40.770, -73.960 40.780, -73.970 40.780, -73.970 40.770))'));

Explanation:

• Purpose of the Query:
• The goal is to identify areas that overlap with a specified region.
• This demonstrates the use of ST_Intersects for spatial intersection tests.
• Key Components:
• ST_Intersects(area, ST_GeomFromText('POLYGON(...)')) : Checks for overlapping geometries.
• Real-World Application:
• Useful for geographic information systems (GIS) that need to analyze overlapping boundaries.

Notes:

• Ensure both geometries use the same spatial reference system.

For more Practice: Solve these Related Problems:

• Write a MySQL query to select records from "GeoData" where the stored polygon intersects with a provided polygon using ST_Intersects.
• Write a MySQL query to retrieve records from "MapAreas" that overlap with a specified region defined via ST_GeomFromText.
• Write a MySQL query to find all overlapping polygons in the "Regions" table using ST_Intersects with a dynamic input polygon.
• Write a MySQL query to filter records in "AreaData" where the polygon column intersects a drawn boundary polygon.

Go to:

• Hands-on MySQL Spatial Data Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Insert a Polygon Using ST_GeomFromText.
NEXT : Calculate the Area of a Polygon.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.