0

I have the following table in my MariaDB database:

SQL table with WKT column

This table contains a column called 'coordinates' (type: LONGTEXT) with a wkt representation of either a MultiPolygon or a Polygon. I am attempting to convert this wkt representation to a type geom.

To do this, I tried running the following SQL Statements:

ALTER TABLE `database1`.`Federal_Info` ADD COLUMN `geom` Polygon;

UPDATE `database1`.`Federal_Info` SET geom =  ST_GeomFromText(coordinates, 4326);

However, I seem to be getting an error:

#1366 - Incorrect POLYGON value: 'MULTIPOLYGON' for column 'geom' at row 1

I also did try to see if there was anything wrong with the coordinates string, so I copied the entire string from the first row by executing the following command in sql:

SET @g= ST_GEOMFROMTEXT('MULTIPOLYGON (((-53.15910091775785 46.63184979843835, -53.15895811730544 46.63187509350104, ..., -53.96597790139074 47.43489159991098)))')

This code works without error so I'm incredibly confused why converting the column is causing a problem.

A bit of background to the table: I parsed a KML file, converted each of the coordinates to a Polygon Shapely object, and used the unary_union function to convert each Polygon into a MultiPolygon. From there I uploaded the dataframe (in Python) to my database.

Improve this question

1 Answer 1

Reset to default
0

You are trying to store a MultiPolygon in a Polygon (sized) column so it won't go.

Make your column a MultiPolygon column and then use st_multi to make any Polygons into MultiPolygons.

Improve this answer
4
  • Thank you for the input. I realize I was trying to store a MultiPolygon into a Polygon column. So I changed the code to: ALTER TABLE `database1`.`Federal_Info` ADD COLUMN `geom` MultiPolygon; and from there I have to execute the following code: UPDATE `database1`.`Federal_Info` SET geom = ST_MULTI(ST_GeomFromText(coordinates, 4326));? or did I get this wrong? And this means that the only way I can do this is keep all my columns either MultiPolygons or Polygons, so it would be necessary to convert the Polygons to a MultiPolygon?
    – NewLearner991
    Mar 17, 2022 at 16:56
  • Yes, that's right - usually you promote to multi if you need any of your geometries to be multi
    – Ian Turton ♦
    Mar 17, 2022 at 17:09
  • thanks, I believe I understand. The thing is that since I am using MariaDB and not PostGIS, it says FUNCTION database1.ST_MULTI does not exist. I could split the two columns by adjusting my Python Code and keep two columns (one for MultiPolygon and one for Polygon). Would that be a better way?
    – NewLearner991
    Mar 17, 2022 at 17:14
  • Two columns would be a nightmare for maintenance and performance - from a quick look at the Maria docs - try using MultiPolygon(poly1,poly2,...) on your polygons
    – Ian Turton ♦
    Mar 18, 2022 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.