0

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?

|improve this question

closed as off-topic by Evan Carroll, xunilk, BERA, Andre Silva, nmtoken Jun 4 '18 at 10:22

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This problem cannot or can no longer be reproduced. Changes to the system or to the asker's circumstances may have rendered the question obsolete, or the question does not include a procedure to enable potential answerers to reproduce the same symptoms. Such questions are off-topic as they are unlikely to help future readers, but editing them to include more details can lead to re-opening." – xunilk, BERA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Why is this tagged both MySQL and PostGIS? It looks like this is a MySQL question? – Evan Carroll Jun 3 '18 at 0: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 3 '18 at 0:10
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.

|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.