(I lied, I'm using MariaDB 10.11.11, but it's mostly the same as latest MySQL for GIS.)
I have multipolygons (1+ polygons making up 1 geometric shape). (They bound the US states.)
I use ST_PointOnSurface to grab a random point on/inside one of these multipolys (using my states dataset).
But when I do a ST_Contains search using that point, on the exact same geometry I used to get the point, some states are getting the result where the point is not within the state!! My whole goal is to turn a state into a point and then have that point be within the state, using existing MySQL functions.
In my mind, this should ALWAYS work, instead of returning 0 rows:
SELECT * FROM states WHERE ST_Contains(multipolygon, (SELECT ST_PointOnSurface(multipolygon) FROM states WHERE abbr='tx') )
I did find: https://lin-ear-th-inking.blogspot.com/2007/06/subtleties-of-ogc-covers-spat... Where the guy says Contains doesn't include the boundary of the poly itself! That would explain it. He says better/newer systems have a Cover fn which will make the above work as intended. Mysql/Maria don't seem to have that (yet?).
So I tried emulating it... after all I should be able to use Intersects or something to cover the case where it's on the boundary itself. But it doesn't find a match either. I tried every function Maria offers and none will find the point ST_PointOnSurface gives me within the original multipolygons.
I'm not sure why the point-on-boundary type functions like Intersects aren't finding anything. Maybe because we're dealing with a zillion decimal places, nothing is really on the boundary.
I think I could use Centroid instead of PointOnSurface, but then I'd also have to yank out just 1 polygon instead of the multi, because the centroid is not guaranteed to be on the surface! And even then, a pahtological shape of the single poly (say a donut) would also have a centroid not on the surface, right? Argh...
Yes, I checked the point ST_PointOnSurface returns for TX and it's legit in TX, a little farther north than the Rio Grande, in what appears to be the southernmost point of TX.
If anyone has any ideas, it would make my day! Ideally an idea on how to emulate Cover would work; or ways to get a point a little more "inside" the polygon without worrying if we are still inside the polygon or not! Thanks!