Invalid Geometry MYSQL

Question

My multi polygon is constantly refused by mysql because it's invalid. However when previewed through online tools, it looks perfectly fine... The mysql function st_isValid is returning false to be more precise

Looking through various forums, ST_makeValid is something PostGis users use to fix that kind of problem. How would one, "fix" this polygon using mysql spatial functions or any easy tool for the matter.

Here is the geometry in geoJson:

{ "type": "Polygon", "coordinates": [ [ [ -73.698313, 45.546876 ], [ -73.698462, 45.546844 ], [ -73.699371, 45.546608 ], [ -73.699627, 45.546491 ], [ -73.699793, 45.546463 ], [ -73.699896, 45.546472 ], [ -73.700075, 45.546481 ], [ -73.700498, 45.546624 ], [ -73.700588, 45.546606 ], [ -73.700742, 45.546527 ], [ -73.701113, 45.546223 ], [ -73.701124, 45.546191 ], [ -73.701085, 45.546137 ], [ -73.701111, 45.546092 ], [ -73.701238, 45.545984 ], [ -73.701366, 45.545957 ], [ -73.701648, 45.545965 ], [ -73.701891, 45.545956 ], [ -73.70197, 45.545941 ], [ -73.702086, 45.54587 ], [ -73.702321, 45.545699 ], [ -73.702444, 45.545534 ], [ -73.702512, 45.545478 ], [ -73.702581, 45.545343 ], [ -73.702645, 45.545288 ], [ -73.702683, 45.545216 ], [ -73.702683, 45.545207 ], [ -73.702569, 45.545094 ], [ -73.702316, 45.544992 ], [ -73.702318, 45.544894 ], [ -73.70237, 45.544849 ], [ -73.702378, 45.544842 ], [ -73.702387, 45.544812 ], [ -73.702489, 45.544731 ], [ -73.702642, 45.544668 ], [ -73.702758, 45.544676 ], [ -73.702912, 45.544757 ], [ -73.703027, 45.544793 ], [ -73.703142, 45.544802 ], [ -73.703258, 45.544801 ], [ -73.703501, 45.544765 ], [ -73.703616, 45.544711 ], [ -73.703807, 45.544584 ], [ -73.703948, 45.544404 ], [ -73.704191, 45.544269 ], [ -73.705047, 45.543988 ], [ -73.70556, 45.54414 ], [ -73.70565, 45.544122 ], [ -73.705777, 45.544005 ], [ -73.705908, 45.543959 ], [ -73.706044, 45.543856 ], [ -73.706165, 45.543846 ], [ -73.706212, 45.543824 ], [ -73.706391, 45.543788 ], [ -73.70666, 45.543769 ], [ -73.706814, 45.543742 ], [ -73.706916, 45.543706 ], [ -73.707044, 45.543616 ], [ -73.707197, 45.543427 ], [ -73.707286, 45.543273 ], [ -73.707349, 45.543129 ], [ -73.707375, 45.543003 ], [ -73.707489, 45.542823 ], [ -73.70754, 45.542787 ], [ -73.707578, 45.542718 ], [ -73.707624, 45.542532 ], [ -73.707743, 45.542414 ], [ -73.707756, 45.542391 ], [ -73.707837, 45.542312 ], [ -73.707695, 45.542218 ], [ -73.707799, 45.542165 ], [ -73.707964, 45.54223 ], [ -73.70864, 45.541779 ], [ -73.709086, 45.541334 ], [ -73.70926, 45.540994 ], [ -73.709678, 45.540647 ], [ -73.710124, 45.540184 ], [ -73.710752, 45.539606 ], [ -73.710862, 45.539522 ], [ -73.7119, 45.538719 ], [ -73.712186, 45.538533 ], [ -73.712343, 45.538382 ], [ -73.712576, 45.538295 ], [ -73.713026, 45.538201 ], [ -73.713466, 45.538008 ], [ -73.713586, 45.537784 ], [ -73.713871, 45.53767 ], [ -73.714244, 45.537593 ], [ -73.714528, 45.537489 ], [ -73.71485, 45.537429 ], [ -73.715157, 45.537406 ], [ -73.715391, 45.537291 ], [ -73.715417, 45.537247 ], [ -73.715547, 45.537176 ], [ -73.715665, 45.537051 ], [ -73.715795, 45.536972 ], [ -73.715863, 45.536802 ], [ -73.71592, 45.536523 ], [ -73.715915, 45.536208 ], [ -73.716052, 45.535814 ], [ -73.716071, 45.535535 ], [ -73.716052, 45.535247 ], [ -73.71588, 45.534939 ], [ -73.716084, 45.534977 ], [ -73.716442, 45.534999 ], [ -73.716836, 45.535129 ], [ -73.716882, 45.535364 ], [ -73.716829, 45.535435 ], [ -73.716683, 45.535641 ], [ -73.716334, 45.535808 ], [ -73.71619, 45.535914 ], [ -73.716214, 45.536004 ], [ -73.716249, 45.536158 ], [ -73.716494, 45.536656 ], [ -73.716476, 45.536871 ], [ -73.716406, 45.537123 ], [ -73.716443, 45.537204 ], [ -73.717134, 45.537221 ], [ -73.717826, 45.537202 ], [ -73.718647, 45.537139 ], [ -73.718905, 45.537079 ], [ -73.719008, 45.537053 ], [ -73.719393, 45.537021 ], [ -73.720214, 45.536967 ], [ -73.720718, 45.536775 ], [ -73.721323, 45.536638 ], [ -73.723005, 45.535883 ], [ -73.723043, 45.535883 ], [ -73.723865, 45.535308 ], [ -73.724449, 45.5349 ], [ -73.725114, 45.534367 ], [ -73.725856, 45.533826 ], [ -73.726142, 45.53364 ], [ -73.726247, 45.533533 ], [ -73.726299, 45.532958 ], [ -73.726355, 45.532743 ], [ -73.726475, 45.53251 ], [ -73.726659, 45.532296 ], [ -73.726867, 45.53219 ], [ -73.726906, 45.532164 ], [ -73.727198, 45.532158 ], [ -73.727213, 45.532158 ], [ -73.727599, 45.532099 ], [ -73.728068, 45.531727 ], [ -73.728224, 45.531647 ], [ -73.728484, 45.531461 ], [ -73.728553, 45.531246 ], [ -73.728529, 45.531156 ], [ -73.728517, 45.531128 ], [ -73.728353, 45.531037 ], [ -73.728277, 45.530964 ], [ -73.728266, 45.530892 ], [ -73.728721, 45.530591 ], [ -73.729368, 45.530292 ], [ -73.729717, 45.530161 ], [ -73.730596, 45.529819 ], [ -73.73101, 45.529617 ], [ -73.73141, 45.529504 ], [ -73.731832, 45.529509 ], [ -73.732443, 45.529696 ], [ -73.732702, 45.529555 ], [ -73.733496, 45.52896 ], [ -73.733746, 45.528693 ], [ -73.734217, 45.528221 ], [ -73.734641, 45.527614 ], [ -73.734958, 45.527149 ], [ -73.735257, 45.526991 ], [ -73.736568, 45.526177 ], [ -73.737348, 45.525664 ], [ -73.737667, 45.525694 ], [ -73.738211, 45.526006 ], [ -73.738313, 45.526043 ], [ -73.738646, 45.526038 ], [ -73.739047, 45.525862 ], [ -73.739423, 45.525624 ], [ -73.739645, 45.525446 ], [ -73.739881, 45.525206 ], [ -73.740005, 45.524811 ], [ -73.740078, 45.524389 ], [ -73.740203, 45.523976 ], [ -73.740336, 45.523752 ], [ -73.740421, 45.523661 ], [ -73.740493, 45.523583 ], [ -73.741125, 45.523383 ], [ -73.74173, 45.523246 ], [ -73.741829, 45.522824 ], [ -73.741822, 45.522572 ], [ -73.742126, 45.522134 ], [ -73.742277, 45.521686 ], [ -73.742565, 45.521392 ], [ -73.742617, 45.520807 ], [ -73.742951, 45.520181 ], [ -73.744178, 45.519114 ], [ -73.744184, 45.51911 ], [ -73.744569, 45.518803 ], [ -73.74484, 45.518716 ], [ -73.744966, 45.518817 ], [ -73.745869, 45.517954 ], [ -73.746613, 45.517296 ], [ -73.74691, 45.517173 ], [ -73.747035, 45.517309 ], [ -73.747053, 45.51767 ], [ -73.747219, 45.517671 ], [ -73.747682, 45.517595 ], [ -73.747788, 45.517435 ], [ -73.747865, 45.517399 ], [ -73.748441, 45.517451 ], [ -73.748557, 45.517398 ], [ -73.748778, 45.517229 ], [ -73.749537, 45.5165 ], [ -73.749915, 45.51618 ], [ -73.750334, 45.515761 ], [ -73.750655, 45.515162 ], [ -73.750665, 45.514712 ], [ -73.750769, 45.514047 ], [ -73.750946, 45.513581 ], [ -73.75157, 45.513155 ], [ -73.751765, 45.513032 ], [ -73.752117, 45.512747 ], [ -73.752464, 45.512688 ], [ -73.752745, 45.512691 ], [ -73.752954, 45.512513 ], [ -73.752951, 45.512072 ], [ -73.753383, 45.512203 ], [ -73.753531, 45.512197 ], [ -73.753563, 45.512196 ], [ -73.753833, 45.512118 ], [ -73.754196, 45.511933 ], [ -73.754596, 45.511775 ], [ -73.755124, 45.511637 ], [ -73.755124, 45.511664 ], [ -73.755043, 45.511816 ], [ -73.755029, 45.512491 ], [ -73.755398, 45.512558 ], [ -73.756062, 45.512664 ], [ -73.756843, 45.512673 ], [ -73.757022, 45.512675 ], [ -73.757356, 45.512597 ], [ -73.758469, 45.512069 ], [ -73.758612, 45.511963 ], [ -73.759013, 45.511769 ], [ -73.75918, 45.511753 ], [ -73.759238, 45.511457 ], [ -73.759422, 45.511216 ], [ -73.759592, 45.511065 ], [ -73.759903, 45.510879 ], [ -73.760382, 45.51065 ], [ -73.760742, 45.510582 ], [ -73.761129, 45.510469 ], [ -73.761283, 45.510438 ], [ -73.761231, 45.510405 ], [ -73.761135, 45.510343 ], [ -73.755167, 45.506485 ], [ -73.755064, 45.506523 ], [ -73.752055, 45.507622 ], [ -73.748701, 45.508835 ], [ -73.747916, 45.509124 ], [ -73.740198, 45.511933 ], [ -73.735099, 45.513786 ], [ -73.728501, 45.516087 ], [ -73.735899, 45.520728 ], [ -73.733579, 45.52237 ], [ -73.731767, 45.523673 ], [ -73.728065, 45.521351 ], [ -73.725348, 45.523671 ], [ -73.722848, 45.525778 ], [ -73.721705, 45.526745 ], [ -73.718494, 45.524743 ], [ -73.718088, 45.525066 ], [ -73.717292, 45.525696 ], [ -73.714647, 45.523987 ], [ -73.714558, 45.523931 ], [ -73.713114, 45.52516 ], [ -73.712405, 45.524875 ], [ -73.712005, 45.524693 ], [ -73.711113, 45.524223 ], [ -73.710785, 45.524028 ], [ -73.709259, 45.523045 ], [ -73.702788, 45.525374 ], [ -73.701599, 45.525801 ], [ -73.699039, 45.526713 ], [ -73.698205, 45.527013 ], [ -73.695128, 45.52806 ], [ -73.693888, 45.528364 ], [ -73.692614, 45.528591 ], [ -73.691609, 45.528713 ], [ -73.690335, 45.528797 ], [ -73.68906, 45.528803 ], [ -73.68808, 45.528755 ], [ -73.687082, 45.52866 ], [ -73.686373, 45.529235 ], [ -73.685703, 45.529704 ], [ -73.685171, 45.530131 ], [ -73.6833, 45.530567 ], [ -73.683117, 45.530608 ], [ -73.680603, 45.531184 ], [ -73.679687, 45.53139 ], [ -73.677974, 45.531786 ], [ -73.677103, 45.531982 ], [ -73.676413, 45.532125 ], [ -73.675417, 45.532251 ], [ -73.674756, 45.532307 ], [ -73.67352, 45.532325 ], [ -73.672612, 45.532299 ], [ -73.671688, 45.532209 ], [ -73.670994, 45.532123 ], [ -73.670372, 45.532011 ], [ -73.669885, 45.531883 ], [ -73.668872, 45.531592 ], [ -73.665397, 45.530594 ], [ -73.655305, 45.527667 ], [ -73.654584, 45.527464 ], [ -73.654545, 45.527413 ], [ -73.653898, 45.527224 ], [ -73.651026, 45.526388 ], [ -73.650751, 45.526307 ], [ -73.650361, 45.526975 ], [ -73.649993, 45.527604 ], [ -73.648908, 45.529464 ], [ -73.64819, 45.530719 ], [ -73.648546, 45.530826 ], [ -73.648152, 45.531403 ], [ -73.647684, 45.532154 ], [ -73.646582, 45.533875 ], [ -73.64578, 45.535135 ], [ -73.646015, 45.535174 ], [ -73.655549, 45.536747 ], [ -73.655702, 45.536773 ], [ -73.660609, 45.537587 ], [ -73.661152, 45.537677 ], [ -73.664283, 45.5382 ], [ -73.664934, 45.538308 ], [ -73.665077, 45.538331 ], [ -73.677017, 45.540292 ], [ -73.677199, 45.540322 ], [ -73.691123, 45.542604 ], [ -73.693725, 45.543364 ], [ -73.694021, 45.54349 ], [ -73.695828, 45.544494 ], [ -73.697563, 45.546075 ], [ -73.69764, 45.546156 ], [ -73.69827, 45.546831 ], [ -73.698313, 45.546876 ] ], [ [ -73.751959, 45.517552 ], [ -73.751979, 45.517228 ], [ -73.75238, 45.516468 ], [ -73.752415, 45.516063 ], [ -73.75269, 45.515175 ], [ -73.752875, 45.514916 ], [ -73.753071, 45.514747 ], [ -73.753395, 45.514571 ], [ -73.753709, 45.514268 ], [ -73.753955, 45.514154 ], [ -73.753993, 45.514154 ], [ -73.754813, 45.514145 ], [ -73.756053, 45.514212 ], [ -73.756309, 45.514206 ], [ -73.75655, 45.514344 ], [ -73.756774, 45.514625 ], [ -73.756819, 45.514887 ], [ -73.757024, 45.515483 ], [ -73.757026, 45.515987 ], [ -73.756854, 45.516219 ], [ -73.756142, 45.516581 ], [ -73.755854, 45.516857 ], [ -73.755478, 45.517087 ], [ -73.754019, 45.517647 ], [ -73.753336, 45.517846 ], [ -73.752923, 45.517995 ], [ -73.752551, 45.517991 ], [ -73.752245, 45.517933 ], [ -73.75198, 45.517768 ], [ -73.751959, 45.517552 ] ] ] }

Answer 1

The problem is that the geometry is represented by a Polygon with a hole outside the exterior. It should be a MultiPolygon with two exterior rings. This can be fixed in the JSON (see examples here).

With PostGIS, it can be fixed by rebuilding the polygons with:

SELECT ST_CollectionHomogenize(ST_Polygonize(ST_Boundary(ST_GeomFromGeoJSON(json))))

Which returns a valid MultiPolygon. I'm not sure if MySQL offers any of these functions.

Answer 2

ST_buffer is the easiest way to fix those strange problems in raw MySQL. However, the cost is that its extremely demanding.

ST_buffer(geometry,0.00001) By using this function, you are basically recreating the polygon but with a delta distance of .00001 which is extremely small. Using 0 do not work.

More documentation on ST_buffer can be found here

However, I'm not sure to like this solution, if someone has a better idea. Feel free to correct me.