[Django] #27464: SRID should be stored in DB on MySQL backend

[Django]

#27464: SRID should be stored in DB on MySQL backend
------------------------------------------------+------------------------
Reporter: Sergey Fedoseev | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: GIS | Version: master
Severity: Normal | Keywords: mysql
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------------+------------------------
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
[https://dev.mysql.com/doc/refman/5.7/en/spatial-function-argument-
handling.html 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).

--
Ticket URL: <https://code.djangoproject.com/ticket/27464>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#27464: SRID should be stored in DB on MySQL backend

-------------------------------------+-------------------------------------
Reporter: Sergey Fedoseev | Owner: Sergey
Type: | Fedoseev
Cleanup/optimization | Status: assigned
Component: GIS | Version: master
Severity: Normal | Resolution:

Keywords: mysql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Sergey Fedoseev):

* owner: nobody => Sergey Fedoseev
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/27464#comment:1>

[Django]

#27464: SRID should be stored in DB on MySQL backend

-------------------------------------+-------------------------------------
Reporter: Sergey Fedoseev | Owner: Sergey
Type: | Fedoseev
Cleanup/optimization | Status: assigned

Component: GIS | Version: master
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Tim Graham):

* stage: Unreviewed => Accepted

Comment:

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
[https://groups.google.com/forum/#!forum/geodjango geodjango mailing list]
to make sure there's consensus about it.

--
Ticket URL: <https://code.djangoproject.com/ticket/27464#comment:2>

[Django]

#27464: SRID should be stored in DB on MySQL backend

-------------------------------------+-------------------------------------
Reporter: Sergey Fedoseev | Owner: Sergey
Type: | Fedoseev
Cleanup/optimization | Status: assigned

Component: GIS | Version: master

Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------

Comment (by 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/27464#comment:3>

[Django]

#27464: SRID should be stored in DB on MySQL backend

--------------------------------------+------------------------------------
Reporter: Sergey Fedoseev | Owner: (none)
Type: Cleanup/optimization | Status: new
Component: GIS | Version: dev

Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

--------------------------------------+------------------------------------
Changes (by Sergey Fedoseev):

* owner: Sergey Fedoseev => (none)
* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/27464#comment:4>

[Django]

#27464: SRID should be stored in DB on MySQL backend

--------------------------------------+------------------------------------
Reporter: Sergey Fedoseev | Owner: (none)

Type: Cleanup/optimization | Status: new

Component: GIS | Version: dev
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

--------------------------------------+------------------------------------
Comment (by Jacob Walls):

Replying to [ticket:27464 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
[https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html#:~:text=now%20supports%20all%20Cartesian%20projections
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
[https://github.com/django/django/pull/21887 test].

> Saving SRID will be breaking change because the old geometries in DB
will have SRID 0, Django will provide geometries with SRID and
[https://dev.mysql.com/doc/refman/5.7/en/spatial-function-argument-
handling.html 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:
[https://forum.djangoproject.com/t/django-srid-conflict-between-django-
and-mysql/40544 1], [https://forum.djangoproject.com/t/specifiying-srid-
to-mysql-using-bboverlaps/20319 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 - #XXXXX: 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
--
Ticket URL: <https://code.djangoproject.com/ticket/27464#comment:5>