1

I have a geoDataFrame with the following structure:

<class 'geopandas.geodataframe.GeoDataFrame'>
RangeIndex: 28 entries, 0 to 27
Data columns (total 3 columns):
 #   Column       Non-Null Count  Dtype   
---  ------       --------------  -----   
 0   Name         28 non-null     object  
 1   Description  28 non-null     object  
 2   geometry     28 non-null     geometry
dtypes: geometry(1), object(2)
memory usage: 800.0+ bytes

Here is a pic of what my df looks like: enter image description here

I am trying to save it in an MySQL database by using gdf.to_sql and my connection is using SQLAlchemy but I get the following error: AttributeError: 'GeometryDtype' object has no attribute 'base'.

I have been looking around and found this solution but I am not able to find the correct syntax make it work for MySQL.

Mel
  • 311
  • 1
  • 9

1 Answers1

3

After trying many things I noticed that the to_sql function was not generating the correct MySQL syntax for it to work. Also with the approach to change to wkb MySQL still did not recognized that column as geometry if I leave the text as is (see picture in the question).

What worked for me was to change the geometry field to string and updated it in python so it would look like this: enter image description here

After that, I proceeded to use the code below, where I send the dataframe to MySQL and then updated the table to set the geometry column:

regions.to_sql('pr_regions', con=conn, schema='eq_pr_db',
               if_exists='replace', index=False)

#add column type Polygon

conn.execute('''ALTER TABLE `eq_pr_db`.`pr_regions` 
                ADD COLUMN `geom` Polygon;''')

#populate new column by applying the ST_GeomFromText function to transform the string to geometry type.

conn.execute('''UPDATE `eq_pr_db`.`pr_regions`
                SET geom =  ST_GeomFromText(geometry) ;''')
Mel
  • 311
  • 1
  • 9