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?
1 Answer
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.