Search a table for Point in Polygon using MySQL

mysql, point-in-polygon

Solution

After a night 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 is working for MySQL 5.6.1 where ST_ prefix function have been implemented. Although I haven't any measurments from a classical approach (x-ray algorithm) I believe that is quite fast. It needs 0.17 seconds to locate a point in 2700 polygons with some polygons having well more than 1,500 points.

Problem

I have create a table (municipal_border), in MySQL 5.5, that holds some boundaries. ``` CREATE TABLE `municipal_border` ( `boundary` polygon NOT NULL, `municipalID` int(10) NOT NULL, ) ENGINE=InnoDB ``` The field municipalID is not unique. I'm using the code below to test if a point belongs into 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 statement set @r = ... returns just one row and I selected it specially for testing. It works just great. What I want to do is to search the whole table (erasing, in other words, the WHERE part from the SQL question) and find in which polygon the point is in.

Original source