SRID should be stored in DB on MySQL backend

[Sergey Fedoseev]

MySQL does not support SRID constraints on columns and SRID is stored together with the geometry itself.
Django always saves geometries with SRID 0 on MySQL backend.
This leads to the problem I encountered while working on #26967: AsGeoJSON function never returns the coordinate system because SRID is always 0.
Probably such Django behavior will lead to more problems in the future (e.g. if/when MySQL will get ST_Transform function).

Saving SRID will be breaking change because the old geometries in DB will have SRID 0, Django will provide geometries with SRID and ​MySQL 5.7+ raises error if geometry arguments have different SRIDs. To make it work MySQL users will have to set SRIDs for geometries in DB (call save() for every object of every GIS model).

[Tim Graham]

I'm not a GIS user, but based on the description, it seems like it could be a large backwards-incompatibility. It might be nice to raise it on the ​geodjango mailing list to make sure there's consensus about it.

[Sergey Fedoseev]

MySQL 8 allows to create GEOMETRY columns with explicitly set SRID.: ​https://dev.mysql.com/doc/refman/8.0/en/spatial-type-overview.html.

[Jacob Walls]

Replying to Sergey Fedoseev:

Probably such Django behavior will lead to more problems in the future (e.g. if/when MySQL will get ST_Transform function).

MySQL has an ST_Transform function since 8.0.13, and "all" Cartesian transforms are ​supported since 8.0.32.

AsGeoJSON function never returns the coordinate system because SRID is always 0.

Indeed, MySQL now returns CRS 0 with the result, which is misleading, as it's just a Django workaround -- see updated ​test.

Saving SRID will be breaking change because the old geometries in DB will have SRID 0, Django will provide geometries with SRID and ​MySQL 5.7+ raises error if geometry arguments have different SRIDs. To make it work MySQL users will have to set SRIDs for geometries in DB (call save() for every object of every GIS model).

Yes. But if we offer a deprecation period for users to cope with the change, and we offer a migration operation users can add to their projects to handle the conversion, I think we should still do it, since the current situation is not ideal, see forum bug reports: ​1, ​2.

We could add support for SRS and Transform in a backward compatible way separately, and then come back to this ticket to handle the new SRID column/storage with a migration operation and a deprecation period.

Roadmap:
1 - #33120: Add support for SRS
2 - #37320: Add support for Transform (supports explicit SRID, but backward-compatible with SRID-0 read/writes)
3 - This ticket: Deprecate SRID-0 read/writes, and offer an upgrade path to SRID-aware columns