Search a table for Point in Polygon using MySQL

Question

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.

Answer 1

Solution of Pavlos Papanikolaou is wonderfull. In my case Table : TestPoly and column : pol Insert Query

SET @g = 'POLYGON((22.367582117085913 70.71181669186944, 22.225161442616514 70.65582486840117, 22.20736264867434 70.83229276390898, 22.18701840565626 70.9867880031668, 22.22452581029355 71.0918447658621, 22.382709129816103 70.98884793969023, 22.40112042636022 70.94078275414336, 22.411912121843205 70.7849142238699, 22.367582117085913 70.71181669186944))';
INSERT INTO TestPoly (pol) VALUES (ST_GeomFromText(@g))

Select Query

set @p = GeomFromText('POINT(22.4053386588057 70.86240663480157)');
select * FROM TestPoly where ST_Contains(pol, @p);

Answer 2

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.