I have pandas DataFrame with these columns:

                                   point            datetime      value
1   Point(-24.6064453125 26.3683280945) 2014-09-11 19:00:00  24.885258
2   Point(-24.6472167969 26.4629898071) 2014-09-11 19:00:00  24.854557
3   Point(-24.6881408691 26.5576820374) 2014-09-11 19:00:00  24.822819

with types: string, datetime and float.

What would be the easiest way to migrate this data to a new table in PostGIS enabled database?

Pandas has to_sql function (working through sqlalchemy connector, which is based on psycopg2), but I don't think there is an option for declaring geometry/geography data type.


Here is quite an easy way with the help of sqlalchemy and geoalchemy2 and pandas great flexibility (assuming above DataFrame table as df):

from sqlalchemy import *
from geoalchemy2 import Geometry

engine = create_engine('postgresql://user:password@localhost:5432/my_postgis_database')

# create table
meta = MetaData(engine)
my_table = Table('my_table', meta,
    Column('id', Integer, primary_key=True),
    Column('point', Geometry('Point', srid=4326)),
    Column('datetime', DateTime),
    Column('value', Float)

# DBAPI's executemany with list of dicts
conn = engine.connect()
conn.execute(my_table.insert(), df.to_dict('records'))

# voila!

You can definitely use to_sql too. Just specify the dtype:

from geoalchemy2 import Geometry
df.to_sql(..., dtype={'point': Geometry(geometry_type='POINT', srid=4326)})

It works great when the data is already in WKT format.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.