mySQL Spatial ST_contains does not include all Results

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?

Why is this tagged both MySQL and PostGIS? It looks like this is a MySQL question? — Evan Carroll, Jun 03 '18 at 00:09
Can you provide actual data to demonstrate this? Without which I can't see this helping anyone? Or anyone helping you. — Evan Carroll, Jun 03 '18 at 00:10

score 1 · Answer 1 · edited Jun 02 '18 at 20:19

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.

mySQL Spatial ST_contains does not include all Results

1 Answers1