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.

Pavlos Papanikolaou · Accepted Answer · 2013-04-21T03:45:14.497

22

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.

edited Apr 21 '13 at 03:45

answered Mar 28 '13 at 13:46

Pavlos Papanikolaou

593
2
7
23

It doesn't look like this is available for 5.5, did you ever try any work arounds? I'm stuck in 5.5 currently. – John Sly Sep 04 '14 at 17:55
No I did not implement anything else as I was very satisfied with the ST_ functions. In my notes I have this [link](http://assemblysys.com/php-point-in-polygon-algorithm/) which might be an alternative. – Pavlos Papanikolaou Sep 11 '14 at 19:00

score 4 · Answer 2 · answered Mar 14 '19 at 05:23

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);

Search a table for Point in Polygon using MySQL

2 Answers2

Linked