(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!
Further to my GIS PointOnSurface (POS) email, which didn't garner any replies (no GIS programmers here?), I managed to "solve" it by doing both POS and Centroid and making sure I use the point returned by the function that actually gives me a point that is actually ST_Within() the polygon(s) (according to MariaDB).
ST_Centroid actually turned out to be much better, only failing on one state... guesses? ... Hawaii. Ya, guess the centroid of those polygons is somewhere in the pacific.
This still isn't a real solution because a centroid AND POS could both not give me a mysql-happy point within the state, but since these things almost never change, and weird states are already rare, it should be good enough for now. And when Mysql/Maria give me some better functions, like other systems do, I can just switch to using those.
Here's the states and whether they pass the centroid ("c") and POS ("pos") tests or not. Note how many fail POS.
+------+------+------+ | abbr | c | pos | +------+------+------+ | AL | 1 | 1 | | AK | 1 | 1 | | AB | 1 | 1 | | AZ | 1 | 0 | | AR | 1 | 1 | | BC | 1 | 1 | | CA | 1 | 1 | | CO | 1 | 1 | | CT | 1 | 1 | | DE | 1 | 1 | | DC | 1 | 1 | | FL | 1 | 1 | | GA | 1 | 1 | | HI | 0 | 1 | | ID | 1 | 1 | | IL | 1 | 1 | | IN | 1 | 1 | | IA | 1 | 1 | | KS | 1 | 1 | | KY | 1 | 0 | | LA | 1 | 1 | | ME | 1 | 1 | | MB | 1 | 1 | | MD | 1 | 1 | | MA | 1 | 0 | | MI | 1 | 1 | | MN | 1 | 0 | | MS | 1 | 1 | | MO | 1 | 1 | | MT | 1 | 1 | | NE | 1 | 0 | | NV | 1 | 0 | | NB | 1 | 1 | | NH | 1 | 1 | | NJ | 1 | 1 | | NM | 1 | 1 | | NY | 1 | 1 | | NL | 1 | 1 | | NC | 1 | 1 | | ND | 1 | 1 | | NT | 1 | 1 | | NS | 1 | 1 | | NU | 1 | 1 | | OH | 1 | 1 | | OK | 1 | 1 | | ON | 1 | 1 | | OR | 1 | 0 | | PA | 1 | 1 | | PE | 1 | 1 | | PR | 1 | 1 | | QC | 1 | 1 | | RI | 1 | 1 | | SK | 1 | 1 | | SC | 1 | 1 | | SD | 1 | 1 | | TN | 1 | 1 | | TX | 1 | 0 | | UT | 1 | 1 | | VT | 1 | 1 | | VA | 1 | 1 | | WA | 1 | 1 | | WV | 1 | 1 | | WI | 1 | 0 | | WY | 1 | 1 | | YT | 1 | 1 | +------+------+------+
If you want the GIS programmers, you should be asking in the Winnipeg GIS Users Group, I think? -Adam ________________________________ From: Trevor Cordes trevor@tecnopolis.ca Sent: June 18, 2025 01:57 To: MUUG RndTbl roundtable@muug.ca Subject: [RndTbl] Re: mysql GIS oddity: PointOnSurface not Contained?
Further to my GIS PointOnSurface (POS) email, which didn't garner any replies (no GIS programmers here?), I managed to "solve" it by doing both POS and Centroid and making sure I use the point returned by the function that actually gives me a point that is actually ST_Within() the polygon(s) (according to MariaDB).
ST_Centroid actually turned out to be much better, only failing on one state... guesses? ... Hawaii. Ya, guess the centroid of those polygons is somewhere in the pacific.
This still isn't a real solution because a centroid AND POS could both not give me a mysql-happy point within the state, but since these things almost never change, and weird states are already rare, it should be good enough for now. And when Mysql/Maria give me some better functions, like other systems do, I can just switch to using those.
Here's the states and whether they pass the centroid ("c") and POS ("pos") tests or not. Note how many fail POS.
+------+------+------+ | abbr | c | pos | +------+------+------+ | AL | 1 | 1 | | AK | 1 | 1 | | AB | 1 | 1 | | AZ | 1 | 0 | | AR | 1 | 1 | | BC | 1 | 1 | | CA | 1 | 1 | | CO | 1 | 1 | | CT | 1 | 1 | | DE | 1 | 1 | | DC | 1 | 1 | | FL | 1 | 1 | | GA | 1 | 1 | | HI | 0 | 1 | | ID | 1 | 1 | | IL | 1 | 1 | | IN | 1 | 1 | | IA | 1 | 1 | | KS | 1 | 1 | | KY | 1 | 0 | | LA | 1 | 1 | | ME | 1 | 1 | | MB | 1 | 1 | | MD | 1 | 1 | | MA | 1 | 0 | | MI | 1 | 1 | | MN | 1 | 0 | | MS | 1 | 1 | | MO | 1 | 1 | | MT | 1 | 1 | | NE | 1 | 0 | | NV | 1 | 0 | | NB | 1 | 1 | | NH | 1 | 1 | | NJ | 1 | 1 | | NM | 1 | 1 | | NY | 1 | 1 | | NL | 1 | 1 | | NC | 1 | 1 | | ND | 1 | 1 | | NT | 1 | 1 | | NS | 1 | 1 | | NU | 1 | 1 | | OH | 1 | 1 | | OK | 1 | 1 | | ON | 1 | 1 | | OR | 1 | 0 | | PA | 1 | 1 | | PE | 1 | 1 | | PR | 1 | 1 | | QC | 1 | 1 | | RI | 1 | 1 | | SK | 1 | 1 | | SC | 1 | 1 | | SD | 1 | 1 | | TN | 1 | 1 | | TX | 1 | 0 | | UT | 1 | 1 | | VT | 1 | 1 | | VA | 1 | 1 | | WA | 1 | 1 | | WV | 1 | 1 | | WI | 1 | 0 | | WY | 1 | 1 | | YT | 1 | 1 | +------+------+------+ _______________________________________________ Roundtable mailing list -- roundtable@muug.ca To unsubscribe send an email to roundtable-leave@muug.ca
Yup, we have the Manitoba GIS User Group (MGUG), where indeed, GIS = Geographic Information Systems:
There's also the 145-year-old Association of Manitoba Land Surveyors (AMLS):
Hartmut
On Wed 18 Jun 2025 at 22:13:17 -05:00, Trevor Cordes trevor@tecnopolis.ca wrote:
On 2025-06-18 Adam Thompson wrote:
If you want the GIS programmers, you should be asking in the Winnipeg GIS Users Group, I think? -Adam
There's a GIS group? LOL, yeah that would probably help! I'll check it out, thanks! _______________________________________________ Roundtable mailing list -- roundtable@muug.ca To unsubscribe send an email to roundtable-leave@muug.ca
-
Adam Thompson -
Hartmut W Sager -
Trevor Cordes