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)})
Advertisement
Answer
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 thedtype
parameter of.to_sql()
to store them in aLONGTEXT
column:(this also works withfrom sqlalchemy.dialects.mysql import LONGTEXT gdf.to_sql('tab', con, if_exists='replace', index=False, dtype = {'geometry': LONGTEXT})
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;