Search code examples
mysql-workbenchworkbench

How can I add SRID 4326 (Spatial Types) to Workbench when adding columns?

When I add a column with type POINT in the EER Diagram, is there anything I can do with that diagram so when I generate automatically the scripts, SRID 4326 is attached to CREATE TABLE script? If I don't setup that number, then by default is zero (flat), but I do need 4326 (sphere).

If not possible, does that mean I cannot synchronise my model with my server automatically and I have to add these changes manually all the time?

Solution

  • I couldn't figure this out either. I believe adding a SRID to a column is currently not supported by MySQL Workbench.

    To check that it is indeed not supported, I did the following:

    1. Added a SRID to a column of an existing DB
    2. Reversed engineered a script of this DB (using Workbench)
    3. Checked the script if it included the set SRID for the column
    4. Was disappointed that it didn't...

    The "good" news is though, that as it is not supported, MySQL Workbench won't pick up on a missing SRID on a column when synchronizing sources.

    This means that once you set the SRID on a column yourself, it won't cause any problems when synchronizing in the futures.

    Note that in order to set a SRID on a column, there can't be a (spatial) index on that column. Therefore, you must remove the index, set the SRID and then add the index back.

    Below is a short and simple script, which I used to do this. Don't forget to update it to your use-case:

    DROP INDEX `my_idx` ON my_table;
ALTER TABLE my_table MODIFY COLUMN my_column POINT NOT NULL SRID 4326;
ALTER TABLE my_table ADD SPATIAL INDEX `my_idx` (`my_column`) VISIBLE;