0

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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.