MySQL geospatial functions to identify whether a rectangle have points in a non uniform four sided polygon and vice versa

Question

Inputs: There are two inputs, both are drawn in earth's surface

Input 1: A rectangular shape, with the top left corner coordinates and bottom right corner coordinates. These coordinates are defined in latitude,longitude pairs.
Input 2: A four sided polygon(a shape strictly not a rectangle), with top left,top right,bottom left and bottom right coordinates. These coordinates are defined in latitude,longitude pairs.Data type of each value is decimal and the data is stored in a mySQL table.

How can I identify if any point inside the rectangle comes inside the polygon and vice versa using MySQL geospatial functions ?

Please see a pictorial representation at http://i.stack.imgur.com/KDju4.jpg

Yes. But, the cases with one shape comes completely inside the other also should be handled. And the referred link uses minimum bound rectangles rather than accurate shapes. Trying to work with a combination of mysql functions which uses accurate object shapes Thank you — Nimmy Alice Mathew
Yes, sorry, the link above is old. Use ST_Intersects and ST_Contains, since 5.6, MySQL has supported proper spatial functions, not just mbr, as in the link you put in the comments. I actually was on of the beta testers, and it was so long ago, I kind of forget that mbr was even an issue, and just assume everyone knows that intersects/contains means proper polygonal functions. Sorry for any confusion. — John Powell

Nimmy Alice Mathew Nimmy Alice Mathew · Accepted Answer · 2014-12-18T13:20:25

select * from( (SELECT * FROM table WHERE ST_Intersects(geomfromtext( 'POLYGON((
                            $rectanglePoint1X $rectanglePoint1Y, $rectanglePoint2X $rectanglePoint2Y,$rectanglePoint3X
                            $rectanglePoint3Y,$rectanglePoint4X $rectanglePoint4Y,$rectanglePoint1X $rectanglePoint1Y))') , geomfromtext( 'POLYGON((
                            $polygonPoint1X $polygonPoint1Y, $polygonPoint2X $polygonPoint2Y,$polygonPoint3X
                            $polygonPoint3Y,$polygonPoint4X $polygonPoint4Y,$polygonPoint1X $polygonPoint1Y))'))) 
        union(SELECT * FROM usgs_data_repo WHERE ST_Contains(geomfromtext( 'POLYGON((
                            $rectanglePoint1X $rectanglePoint1Y, $rectanglePoint2X $rectanglePoint2Y,$rectanglePoint3X
                            $rectanglePoint3Y,$rectanglePoint4X $rectanglePoint4Y,$rectanglePoint1X $rectanglePoint1Y))') , geomfromtext( 'POLYGON((
                            $polygonPoint1X $polygonPoint1Y, $polygonPoint2X $polygonPoint2Y,$polygonPoint3X
                            $polygonPoint3Y,$polygonPoint4X $polygonPoint4Y,$polygonPoint1X $polygonPoint1Y))'))) 
        union(SELECT * FROM usgs_data_repo WHERE ST_Contains(geomfromtext( 'POLYGON((
                            $polygonPoint1X $polygonPoint1Y, $polygonPoint2X $polygonPoint2Y,$polygonPoint3X
                            $polygonPoint3Y,$polygonPoint4X $polygonPoint4Y,$polygonPoint1X $polygonPoint1Y))'),geomfromtext( 'POLYGON((
                            $rectanglePoint1X $rectanglePoint1Y, $rectanglePoint2X $rectanglePoint2Y,$rectanglePoint3X
                            $rectanglePoint3Y,$rectanglePoint4X $rectanglePoint4Y,$rectanglePoint1X $rectanglePoint1Y))') )))

where { $rectanglePoint1X $rectanglePoint1Y, $rectanglePoint2X $rectanglePoint2Y,$rectanglePoint3X $rectanglePoint3Y,$rectanglePoint4X $rectanglePoint4Y} are the co-ordinates of the rectangle(Input 1)

and {$polygonPoint1X $polygonPoint1Y, $polygonPoint2X $polygonPoint2Y,$polygonPoint3X $polygonPoint3Y,$polygonPoint4X $polygonPoint4Y} are the co-ordinates of the polygon(Input 2)

Note that the above code will work in the case of any polygon instead of a rectangle as Input1

MySQL geospatial functions to identify whether a rectangle have points in a non uniform four sided polygon and vice versa

1 Answers