Migrating data from Python to PostGIS

Question

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.

Answer 1

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)
)
my_table.create(engine)
# DBAPI's executemany with list of dicts
conn = engine.connect()
conn.execute(my_table.insert(), df.to_dict('records'))
# voila!

Answer 2

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.