Something like:

SELECT * from polygons WHERE bounds CONTAINS(SELECT geom_point from points)

Can someone help me out with the syntax? Bounds will always be a rectangular polygon and everything is in lon/lat. I could just check that each points lat/lng is between the min/max lat/lng of each polygon - but I cannot figure out the syntax to get MYSQL to loop / check all polys vs all points.

Thanks in advance.

  • MYSQL is not a true spatial database - see stackoverflow.com/questions/15394645/… – Mapperz Aug 9 '13 at 15:01
  • "As of MySQL 5.6.1, corresponding versions [of spatial functions] are available that use precise object shapes. These versions are named with an ST_ prefix. For example, Contains() uses object bounding rectangles, whereas ST_Contains() uses object shapes." via dev.mysql.com/doc/refman/5.6/en/… – DPierce Aug 9 '13 at 20:32
  • I realise this, but since my polygons are rectangles, there shouldnt be a problem.. im sure this query is possible.. i guess it just comes down to syntax. – Chris Aug 10 '13 at 1:00

Given a polygons table with rectangular polygons, and a points table called points, both with geometry columns called "geom", this should return any polygons that contain any of the points:

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

  • For some reason the CONTAINS function didn't work, but I ended up using this same syntax but pulling out the min/max long/lats of each box and just checking that a points long/lat was BETWEEN the polygon's bounds. This syntax seems correct though, so i will accept it as the answer - just seems that mysql spatially is a bit of a pig. – Chris Aug 14 '13 at 7:43
  • Most recommend PostGIS over MySQL spatial for this reason. PostGIS has much more spatial functionality and is likely to be more bug free. – lreeder Aug 14 '13 at 13:22

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.