I find such limited resources for my problems and have looked for a similar problem online, but didn't have any luck.
I have the following Pandas, not geopandas, dataframe:
RecAreaName RecAreaCategory Province Comments coordinates
0 Whistler World Class BC None POLYGON ((-123.052382 50.094969, -123.050613 5...
1 Tofino Ucluelet World Class BC None POLYGON ((-125.946713 49.24364400000002, -126....
2 Banff World Class AB None POLYGON ((-115.564478 51.11632900000002, -115....
3 Canmore World Class AB None POLYGON ((-115.396185 51.07281599999998, -115....
4 Lake Louise World Class AB None POLYGON ((-116.275023 51.43238300000001, -116....
.. ... ... ... ... ...
159 Ski Chantecler None QC None POLYGON ((-74.18784099999998 45.96117599999999...
160 Belle Niege None QC None POLYGON ((-74.215389 45.99528800000001, -74.20...
161 Valée Blue None QC None POLYGON ((-74.245796 46.03202199999999, -74.23...
162 Ski Garceau None QC None POLYGON ((-74.22637400000001 46.34051399999998...
163 Ski La Reserve None QC None POLYGON ((-74.18463800000001 46.27545499999999...
The coordinates column is a Shapely object, a Polygon. I'm trying to write to my database (mariaDB) but the normal _to_sql method obviously fails.
engine = sqlalchemy.create_engine('mysql+pymysql://root:maria@localhost:3306/topology?unix_socket=/data/mysql/mysql.sock')
dataframe.to_sql(
name='TestTable',
con=engine,
if_exists='fail')
I realize I can convert the Shapely polygon to a wkt and then upload it as a string but thing is that I need to merge this dataframe with another table(containing lat and long) using ST_WITHIN and need to write it as a geom type in my database all with python. Do I need to install geopandas and geoalchemy for this and if so how?
