Mysql Question posted in
The official MySQL documentation can be found here.

MySQL 8 Spatial column with GEOMETRYCOLLECTION

We are upgrading MySQL from 5.7 to 8.0. Table cuatom_shapes has column shape with SRID=0, in which few rows have data with GEOMETRYCOLLECTION and few other rows have data with POLYGON.

Though column SRID is set to 0, for POLYGON data correct SRID is 4326. So we decided to set SRID=4326 at column level but due to GEOMETRYCOLLECTION rows we are unable to set the SRID.

How to handle this use case?

Tags:

2

Answers


  1. 0

    Were the values stored in the GEOMETRYCOLLECTION column rebuilt to have the different SRID?

    I think the answer is that you must dump the value, change the SRID, then re-insert.

    File a bug with bugs.mysql.com . It sounds like ALTER COLUMN fails to dig in to do the SRID change.

    Login or Signup to reply.
  2. 0

    I think the best way to handle this case is by creating a new column with the correct SRID and then updating values.

    In your case here is an example:

    alter table cuatom_shapes add column new_shape geometry SRID 4326;

update cuatom_shapes
set new_shape = ST_SRID(shape, 4326)
where ST_GeometryType(shape) = 'ST_Polygon';

update cuatom_shapes set new_shape = shape
where ST_GeometryTye(shape) = 'ST_GeometryCollection';

    Once you update those values, you can actually drop the old data and rename the new column.

    alter table cuatom_shapes drop column shape;
alter table cuatom_shapes rename column new_shape to shape;

    By following these rules you can set the correct SRID for the POLYGON data while keeping the GEOMETRYCOLLECTION data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search