Geopandas to_sql shows geometry column as text in mysql

Question

I am attempting to take multipolygon data from a geojson, read it into a dataframe, and then create a table in mysql containing the data.

What's weird to me is that checking the dtype at the end of the script will show the geometry column correctly as geometry. However, checking the mysql db this column is showing as text. Trying to convert the column to geometry or multipolygon type raises the error

1416 - Cannot get geometry object from data you send to the GEOMETRY field

I tried the following query, which may be where my issue is?

ALTER TABLE [table]
MODIFY COLUMN [column] GEOMETRY

Similar questions gave an answer to convert the data to WKT or WKB. However, using the to_wkb (or to_wkt) method and then running a query will result in the previously mentioned error as well. I also tried just making my own function with no luck. Python code below.

import geopandas
from geoalchemy2 import Geometry
from sqlalchemy import create_engine, types


df = geopandas.read_file('geodata.geojson')

# geodataframe = df.to_wkb()

hostname="localhost"
dbname="mydbname"
uname="iamroot"
pwd = "madeyoulook"

engine = create_engine(f'''mysql://{uname}:{pwd}@{hostname}/{dbname}''')

df.to_sql('geodatacounty', engine, if_exists='replace', index=False, dtype={'shape_leng': types.FLOAT , 'shape_area': types.FLOAT, '`geometry`': Geometry(geometry_type='MULTIPOLYGON', srid=4326)})

buddemat · Accepted Answer · 2021-10-15T11:00:19.667

AFAIK, neither sqlalchemy nor geoalchemy2 have a geometry type that is directly compatible with MySQL, so the example you have that probably works for PostGIS does not generate a syntactically correct statement for MySQL. Therefore, you need to work around this, e.g. by first importing the column as TEXT and later converting the data to GEOMETRY.

If you have your polygon data in a TEXT type column, you can convert it a geometry using ST_GeomFromText(). To make sure that you can correctly store the result, create an additional column of type GEOMETRY (or MULTIPOLYGON or whatever you want) first:

ALTER TABLE tab ADD COLUMN newcolumn GEOMETRY;

Then update that column:

UPDATE tab SET newcolumn = ST_GeomFromText(oldcolumn);

Note:

If you should have difficulties importing your data in the first place, changing the encoding of your db may help:
```
ALTER DATABASE mydbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
```
In case you encounter errors that the WKT text representations of your geometries are too large for a TEXT column in the first place , you can explicitly use the dtype parameter of .to_sql() to store them in a LONGTEXT column:
```
from sqlalchemy.dialects.mysql import LONGTEXT

gdf.to_sql('tab', con, if_exists='replace', index=False, dtype = {'geometry': LONGTEXT})
```
(this also works with MEDIUMTEXT).

Full Python example that works for me in a quick test with MySQL 5.7:

import geopandas as gpd
from sqlalchemy import create_engine, sql

hostname = 'localhost'
dbname = 'mydbname'
uname = 'iamroot'
pwd = 'madeyoulook'

engine = create_engine(f'''mysql+pymysql://{uname}:{pwd}@{hostname}/{dbname}''')

gdf = gpd.read_file('geodata.geojson')
gdf = gdf.to_wkt()

with engine.connect() as con:
    # may not be necessary, see above. 
    con.execute(sql.text("""ALTER DATABASE mydbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;"""))

    gdf.to_sql('tab', con, if_exists='replace', index=False)

    con.execute(sql.text("""ALTER TABLE mydbname.tab ADD COLUMN new_geometry GEOMETRY;"""))
    con.execute(sql.text("""UPDATE mydbname.tab SET new_geometry = ST_GeomFromText(geometry);"""))

You then should have the according column new_geometry of type GEOMETRY in the table tab that stores the geometry in MySQL's internal format. You can e.g. use the MySQL Geometry Format Conversion Functions on that column to get back the WKT representation:

SELECT ST_AsWKT(new_geometry) from tab;

The ```UPDATE``` query doesn't work for me. Using the ```.to_wkt()``` method on my dataframe returns ```Error Code: 1366. Incorrect string value: '\xE9\xF0\x10\xC6O\xFD...' for column 'geometry' at row 1``` when I run this query. I get this same error if I don't try to convert the geometry column to wkt or wkb. Using the ```to_wkb``` method returns ```Error Code: 3037. Invalid GIS data provided to function st_geomfromtext.``` — br0steen, Oct 14 '21 at 13:20
I have edited the post. The problem in my old answer was that the result of `ST_GeomFromText()` could not be stored in the `TEXT` column. By storing it in a new column that already is of type `GEOMETRY` you can avoid that problem. There also is a possible encoding issue involved. My answer now includes a full example that works for me. — buddemat, Oct 15 '21 at 09:44
This worked perfectly, thank you for the additional explanation and link to related question! — br0steen, Oct 25 '21 at 00:09

Geopandas to_sql shows geometry column as text in mysql

1 Answers1