SRID POINT Mysql 8+ Not saved

[tebaly]

Mysql 8 can save PONT like this: ST_SRID(POINT(...), 4326)
But

            $point = new Point($lon, $lat);
            $point->setSrid(4326); # !!!!!!!!!!
            $entity->setPoint($point);

Not working - SRID did not saved

[Alexandre-T]

Hello,

Currently the MySQL code doesn't use the SRID value. This is something that'll need to be implemented. Currently SRID are saved, but they aren't persisted.

We have to enhance the convertToDatabaseValueSQL in the MySQL classes for the Geographic types, but if someone wants to contribute, he should be careful and check the MySQL functionalities, because I remember that it wasn't compliant with first versions of MySQL5. I even don't know if SRID are now compliant with MySQL5.*

[c1tru55]

saving srid for MySQL is critical, because it affects on lat/lon order. see:

SELECT
    ST_AsText(ST_GeomFromText('POINT(45 90)')),
    ST_X(ST_GeomFromText('POINT(45 90)')),
    ST_Y(ST_GeomFromText('POINT(45 90)')),
#     ST_Latitude(ST_GeomFromText('POINT(45 90)')),
#     ST_Longitude(ST_GeomFromText('POINT(45 90)')),
    ST_SRID(ST_GeomFromText('POINT(45 90)')),

    ST_AsText(ST_GeomFromText('POINT(45 90)', 4326)),
    ST_X(ST_GeomFromText('POINT(45 90)', 4326)),
    ST_Y(ST_GeomFromText('POINT(45 90)', 4326)),
    ST_Latitude(ST_GeomFromText('POINT(45 90)', 4326)),
    ST_Longitude(ST_GeomFromText('POINT(45 90)', 4326)),
    ST_SRID(ST_GeomFromText('POINT(45 90)', 4326)),

    ST_AsText(POINT(45, 90)),
    ST_X(POINT(45, 90)),
    ST_Y(POINT(45, 90)),
#     ST_Latitude(POINT(45, 90)),
#     ST_Longitude(POINT(45, 90)),
    ST_SRID(POINT(45, 90)),

    ST_AsText(ST_SRID(POINT(45, 90), 4326)),
    ST_X(ST_SRID(POINT(45, 90), 4326)),
    ST_Y(ST_SRID(POINT(45, 90), 4326)),
    ST_Latitude(ST_SRID(POINT(45, 90), 4326)),
    ST_Longitude(ST_SRID(POINT(45, 90), 4326)),
    ST_SRID(ST_SRID(POINT(45, 90), 4326))
;

| ST_AsText(ST_GeomFromText('POINT(45 90)'))          | POINT(45 90) |
| ST_X(ST_GeomFromText('POINT(45 90)'))               | 45           |
| ST_Y(ST_GeomFromText('POINT(45 90)'))               | 90           |
| ST_SRID(ST_GeomFromText('POINT(45 90)'))            | 0            |
| ST_AsText(ST_GeomFromText('POINT(45 90)', 4326))    | POINT(45 90) |
| ST_X(ST_GeomFromText('POINT(45 90)', 4326))         | 45           |
| ST_Y(ST_GeomFromText('POINT(45 90)', 4326))         | 90           |
| ST_Latitude(ST_GeomFromText('POINT(45 90)', 4326))  | 45           |
| ST_Longitude(ST_GeomFromText('POINT(45 90)', 4326)) | 90           |
| ST_SRID(ST_GeomFromText('POINT(45 90)', 4326))      | 4326         |
| ST_AsText(POINT(45, 90))                            | POINT(45 90) |
| ST_X(POINT(45, 90))                                 | 45           |
| ST_Y(POINT(45, 90))                                 | 90           |
| ST_SRID(POINT(45, 90))                              | 0            |
| ST_AsText(ST_SRID(POINT(45, 90), 4326))             | POINT(90 45) | <---
| ST_X(ST_SRID(POINT(45, 90), 4326))                  | 90           | <---
| ST_Y(ST_SRID(POINT(45, 90), 4326))                  | 45           | <---
| ST_Latitude(ST_SRID(POINT(45, 90), 4326))           | 90           | <---
| ST_Longitude(ST_SRID(POINT(45, 90), 4326))          | 45           | <---
| ST_SRID(ST_SRID(POINT(45, 90), 4326))               | 4326         |

PS: also it is possible to define SRID in MySQL column definition, like:

p POINT SRID 4326

see: https://dev.mysql.com/doc/refman/8.4/en/spatial-type-overview.html
but currently it is ignored, and resulting SQL is just:

g POINT DEFAULT NULL COMMENT \'(DC2Type:geography_point)\'

[Alexandre-T]

Yes, it is. I'm currently working on it. MySQL has its own method to store data and Doctrine ORM only accepts one parameter, I cannot store coordinates and SRID with the current engine. So, I'm working on sub-libraries to convert the point into the internal binary storage system of MySQL. IMO, I still need 7 or 8 days of development to make it working. But I only work on it on my free time.

[Alexandre-T]

If you're waiting for this new feature, you can yet use the SRID method with a second argument. This is not a good solution, but this is a temporary way to do it. (The best way, currently, is to update your column to specify a default SRID)