MySQL Spatial - 从 SRID 31287 转换为 4326 [英] MySQL Spatial - Convert from SRID 31287 to 4326

问题描述

在 MySQL 中，我有一个包含大约 100 个表的数据库.

In MySQL I have a a database with around 100 tables.

它们都包含一个名为shape"的列，这是一个多边形类型的字段.

They all contain a column called ´shape´, this is a polygon type field.

它包含我认为的信息(st_srid 返回 1，但它是错误的)是 SRID 31287.

It contains information in what I believe (st_srid returns 1, but it's wrong) is SRID 31287.

我想将其转换为 SRID 4326，我该怎么做?

I would like to convert it to SRID 4326, how would I go about to do this?

更新这是一个非常古老的问题，答案非常旧，最近提交了一个新答案，恕我直言，如果版本控制正确，则使用该方法.我没有将新答案标记为正确，因为这对在问题时给出正确答案的人不公平.但是，如果您使用的是版本 8，请按照 UncertaintyP 对此问题的回答:https://stackoverflow.com/a/65314337/2973474

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

推荐答案

这里有两个问题.

1. 您的内部表示是错误的，它是 SRID 1，应该是 SRID 31287.在每个数据库中都可以更改内部表示:MySQL 将在版本 8 中为它获得一个 mutator，ST_SRID，PostGIS 有 ST_SetSRID
2. 您需要实际将 SRID 31287 转换为 SRID 4326.这在 PostGIS 中仅可能.MySQL 和 Microsoft SQL 可以重新投影 SRID.

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.

要解决 PostGIS 中的第一个问题，您可以使用 ST_SetSRID，并解决第二个问题，您将使用 ST_Transform.

To tackle the first problem in PostGIS, you'd use ST_SetSRID, and to tackle the second problem you'd use ST_Transform.

这篇关于MySQL Spatial - 从 SRID 31287 转换为 4326的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！