Finding a table for a point in a polygon using MySQL

Question

I created a table (community_border) in MySQL 5.5 that has some boundaries.

CREATE TABLE `municipal_border` ( `boundary` polygon NOT NULL, `municipalID` int(10) NOT NULL, ) ENGINE=InnoDB 

The Municipal Field ID is not unique.

I use the following code to check if a point belongs to a polygon.

 set @r = (SELECT municipal_border.boundary FROM municipal_border WHERE municipalID=9001); set @p = GeomFromText('POINT(24.1621 41.0548)'); select if(contains(@r, @p), 'yes', 'no'); 

The first set @r = ... statement returns only one row, and I selected it specifically for testing. It works great.

What I want to do is search the entire table (erasing, in other words, the WHERE part from the SQL query) and find which polygon the point is in.

Answer 1

After a night's sleep, I found the following solution.

 set @p = GeomFromText('POINT(23.923739342824817 38.224714465253733)'); select municipalID FROM ecovis.municipal_border where ST_Contains(municipal_border.boundary, @p); 

It works for MySQL 5.6.1, where the ST_ prefix function is implemented. Although I do not have any measurements from the classical approach (X-ray algorithm), I find it pretty fast. It takes 0.17 seconds to find a point in 2700 polygons, and some polygons have more than 1500 points.