sqlmariadbspatialspatial-query

How to select where st_contains one or more points

How can I select all rows where a polygon column st_conatins one or more points, I have a list of points and I want to select all rows where at least one of the points is in thiere polygon

For example, this query will give me all areas that contain a single point

SELECT *
FROM areas
WHERE ST_Contains(areas.geometry, POINT('lon', 'lat'))

But what I want is to test against multiple points (like IN query), hypothetical code would be

SELECT *
FROM areas
WHERE ST_Contains(areas.geometry, IN(POINT('lon', 'lat'), POINT('lon2', 'lat2'), POINT('lon3', 'lat3')))

Thank you.

Solution

  • Instead of checking each single point use a collection of points with `MULTIPOINT'

    SET @mp:= GeomFromText('MULTIPOINT(1 1, 3 3, 9 9)');
SET @area:= GeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0))');

    Since ST_CONTAINS returns TRUE only if all points are in the area, you have to use ST_INTERSECTS instead.

    select st_intersects(@mp,@area);
+--------------------------+
| st_intersects(@mp,@area) |
+--------------------------+
|                        1 |
+--------------------------+