Navid_pdp11 Navid_pdp11 - 9 months ago 64
MySQL Question

How to search for all points inside multi polygons which have intersect?

I am working with spatial object in MySQL. I have a table that saves the location of estate in database as "longitude" and "latitude", and I search for these estates inside of polygons that user requests. I am using the

function in MySQL, like this:

select * from estate where ST_CONTAINS(GEOMFROMTEXT(region),POINT(plat,plon)));

In the code snippet region has this format:

"POLYGON((lat1 lng1,lat2 lng2,lat3 lng3,lat4 lng4,lat1 lng1),(lat6 lng6,lat7 lng7,lat8 lng8,lat9 lng9,lat6 lng6))"

Everything works well when polygons have no overlap with each other. However, if polygons have overlap, MySQL subtracts that overlap area and does not retrieve estates in the overlap area. I add this image for more explanation:

enter image description here

How do I make this search work correctly, even with overlapped polygons?


In MySQL, we have the type MULTIPOLYGON for this situation. You can see complete document in MySQL Spatial Data Types.

Your region will need to be defined like this:

"MULTIPOLYGON(((lat1 lng1,lat2 lng2,lat3 lng3,lat4 lng4,lat1 lng1)),((lat6 lng6,lat7 lng7,lat8 lng8,lat9 lng9,lat6 lng6)))"