[GIS] Does MySQL has function which will return the points inside polygon

Question

I have MySQL database with polygons, and also the database with points (column longitude and column latitude), is it possible to return all points inside one polygon?

Answer 1

If you are using MySQL 5.6.1+, take a look at ST_Contains.

Given table called points and polygons, with a primary key called id and a geometry column called geometry, this should work:

SELECT points.id FROM polygons, points WHERE ST_CONTAINS(polygons.geom, points.geom);

If there's no geometry column for the points table, but there are latitude and longitude columns, you can use the MySQL Point() function to convert long/lat columns to a point, like this:

SELECT points.id FROM polygons, points WHERE ST_CONTAINS(polygons.geom, Point(points.longitude, points.latitude));

Note that MySQL versions prior to 5.6.1 provide a Contains function which uses the MBR of the polygon, not the actual polygon boundaries, which might also work for you.