[GIS] Migrating data from Python to PostGIS

numpypandaspostgispython

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.

Best Answer

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!
Related Question