mySQL Spatial ST_contains does not include all Results [closed]

Question

I when I run the first command, mySQL 5.7 only shows a sub-collection of all results. Only when I run the 2nd command I get the correct answer. The problem with the 2nd command: I cannot use the performance boost of the spatial index anymore (checked with the DESCRIBE command).

SELECT id FROM table1 WHERE ST_CONTAINS(@mls, coords);

SELECT id, ST_CONTAINS(@mls,coords) FROM table1 WHERE ST_CONTAINS(@mls,coords)>0;

• coords: Column with x,y POINT spatial data type (with spatial index)
• @mls: a user variable containing a multilinestring, made to a polygon with ST_BUFFER.

Does anyone have a solution for that issue?

Answer 1

Using ST_Intersects returns "incomplete" results I found a solution:

Buffer every POINT! choose a very small buffer size for the points. Therefore I added a new column coordsB with a GEOMETRY data type. In my case:

ALTER TABLE table1 ADD coordsB GEOMETRY;
UPDATE table1 coordsB = ST_BUFFER(coords,0.00001);
ALTER TABLE table1 modify coords point not null;
ALTER TABLE table1 ADD SPATIAL INDEX(coordsB);

Now you can find all buffered Points.

Last Remark: The function ST_DWithin() mentioned in the Post is not yet available for mySQL, so it seems like one has to buffer Points. I do not understand why that happens, but it is a workaround. The whole procedure might take a while, using a script might be useful.