3

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

alexisdevarennes
  • 5,437
  • 4
  • 24
  • 38

5 Answers5

3

An update of the conversion in MySQL for the lost souls that found their way to this question:

In version 8 you now have ST_Transform

mysql> SET @p = ST_GeomFromText('POINT(52.381389 13.064444)', 4326);
mysql> SELECT ST_AsText(@p);
+----------------------------+
| ST_AsText(@p)              |
+----------------------------+
| POINT(52.381389 13.064444) |
+----------------------------+
mysql> SET @p = ST_Transform(@p, 4230);
mysql> SELECT ST_AsText(@p);
+---------------------------------------------+
| ST_AsText(@p)                               |
+---------------------------------------------+
| POINT(52.38208611407426 13.065520672345304) |
+---------------------------------------------+
UncertaintyP
  • 171
  • 2
  • 4
2

You have two problems here.

  1. 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 has ST_SetSRID
  2. 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.

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
-1

In MySQL it looks like this:

UPDATE `table name` SET `column name` = ST_GeomFromText(ST_AsText(`column name`), 4326);

Hope this helps

Adrian D
  • 1
-1

This worked for me

(For MySQL)

UPDATE Table SET SpatialColumn = ST_GeomFromText(ST_AsText(SpatialColumn), 4326);
user4906240
  • 605
  • 1
  • 6
  • 20
  • This changes the SRID value, but does not transform the data itself, which will make it invalid. – Brideau Jun 17 '18 at 04:08
-2

I can only hope this will help you (either directly or as a pointer) as its an answer for SQL Server, but how about...

UPDATE Table SET SpatialColumn = Geometry::STGeomFromText(SpatialColumn.STAsText(), 4326);

Naturally, you can swap Geometry for Geography if you're using Geography columns. You'll need to replace Table with your table name and SpatialColumn with the name of your Spatial Column.

NOTE: This assumes your SRID of 31287 defines coordinates as decimal latitude / longitude values.

Jon Bellamy
  • 3,333
  • 20
  • 23
  • What does this have to do with MySQL? – Evan Carroll Nov 29 '17 at 18:20
  • 2
    @EvanCarroll Of course setting the SRID doesn't transform the coordinates. I misread the question when I answered it, simply seeing it as wanting to correct/convert the SRID value. But as for "What does this have to do with MySQL?" - absolutely nothing, but as no-one else offered any help, I hoped that a SQL answer (which is what I know) would help set the OP in the right direction and even stated this in the answer. Thankfully however for future viewers, you've provided a better and correct answer. – Jon Bellamy Nov 29 '17 at 22:11