MySQL Spatial - Convert from SRID 31287 to 4326
In MySQL I have a a database with around 100 tables.
They all contain a column called ´shape´, this is a polygon type field.
It contains information in what I believe (st_srid returns 1, but it's wrong) is SRID 31287.
I would like to convert it to SRID 4326, how would I go about to do this?
UPDATE This is a very old question with very old answers, recently a new answer was submitted which is IMHO the approach to use if the versioning is correct. I am not marking the new answer as correct as it's unfair to the person who gave the correct answer at the time of the question. If you're on version 8 however, follow this answer to this question by UncertaintyP : https://stackoverflow.com/a/65314337/2973474
Answer
You have two problems here.
- Your internal representation is wrong, it's SRID 1, and it should be SRID 31287. Changing an internal representation is possible in every database: MySQL will get a mutator for it in version 8,
ST_SRID, PostGIS hasST_SetSRID - You need to actually convert from SRID 31287 to SRID 4326. This is possible ONLY in PostGIS. Neither MySQL nor Microsoft SQL can reproject an SRID.
To tackle the first problem in PostGIS, you'd use ST_SetSRID, and to tackle the second problem you'd use ST_Transform.