When importing spaces in polygon format from one DB to another something doesn't preserve.
It is not possible to run ST_Intersects successfully on the imported data even though MySQL knows what SRID it was stored with and thus must be queried with, it still doesn't get the expected matches (using different SRID:s results in fatal error).
The workaround is currently to generate all polygon fields specifically for the production database (i e for the airspace manager to import all airspaces anew for the new database rather than doing an SQL export/import).
This will be a problem in the future. Has anyone encountered a similar problem or can figure out what about sql exports/imports doesn't preserve in relation to the polygon data type?
the export was made with PhpMyAdmin which might be a reason for this problem. Perhaps the export file generated by PhpMyAdmin has no support for preserving the SRID information?
I have verified that there is no export tool publicly available (not PhpMyAdmin, not TablePlus and not even the native Mysqldump) that will include the SRID property of the Polygon field, which is very disappointing. the textual content of the polygon field before and after export is identical, so there must be metadata (SRID) that is lost.
I suppose moving Polygon fields between two MySQL databases is only possible then if SRID 0 (default) is used. In our case it is an actual map coordinate system (WGS84) so that is not an option to us either.
Surprising if no one has encountered this issue before since MySQL spatial functions have been around for a long time now.
I hope I am missing something.
I have also tried to afterward set the SRID with
UPDATE airspaces SET coordinates_polygon = ST_GeomFromText(ST_AsText(coordinates_polygon), 4326);
Even if I first set it to 0 and then back to 4326 it makes no difference, still don't get any match on ST_Intersects().
