4

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
    You say the points have longitude and latitude columns. Do they also have a geometry column? – blah238 Nov 12 '13 at 23:16
6

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.

  • Will this work if the "point" records do not have a geometry column? – blah238 Nov 12 '13 at 23:16
  • @blah238 - You're right, it won't work without a geom column, but I updated my answer with another approach when points are represented by lat/lng columns. – lreeder Nov 12 '13 at 23:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.