CODEWITHSUNDEEP
mysqlspatialmysql-8.0srid
pramodtech
pramodtech

Reputation: 6280

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.

Sample truncated data in shape column

GEOMETRYCOLLECTION (POLYGON ((-94.469378 39.129666, -94.469356 
39.129359)), ... LINESTRING (-94.439262 39.13177, -94.440642 
39.13078)) 

MULTIPOLYGON (((-121.793932 38.548102, ... , -121.793937 38.549311)))

POLYGON ((-90.259966 38.708518, ... , -90.260353 38.708028))

How to handle this use case?

Upvotes: 2

Views: 108

Answers (2)

Vijay Panwar
Vijay Panwar

Reputation: 273

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.

Upvotes: 0

Rick James
Rick James

Reputation: 142483

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.

Upvotes: 0

Related Questions