PostGIS – Adding New Columns to Existing PostGIS Table from GeoPandas

geopandaspostgissql

I am trying to add some newly created columns to an existing table in PostGIS table from GeoPandas.

Previously, I've pulled the data out of my database, run geocoding, and then created new columns by manipulating the results. I now want to update my table with these new columns, but running the to_postgis function with the if_exists=append option produces a psycopg2.errors.UndefinedColumn: column of relation does not exist.

I assume this means that I need to do this via formatting an SQL statement and then executing it, but I was hoping there was some magic that would save me the trouble.

Best Answer

Per @Joe Be's comment, it's necessary to either write a SQL statement to add the additional columns to the DB, or to do so manually before running the script.

Related Solutions

[GIS] Cannot insert simple geometry data

You should use ST_GeomFromText.

The functions without the ST_ are the older functions, and are being phased out; They are deprecated, and shouldn't be used any more.

[GIS] Adding GeoPandas Dataframe to PostGIS table

Using Panda's to_sql method and SQLAlchemy you can store a dataframe in Postgres. And since you're storing a Geodataframe, GeoAlchemy will handle the geom column for you. Here's a code sample:

# Imports
from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import *
import pandas as pd
import geopandas as gpd

# Creating SQLAlchemy's engine to use
engine = create_engine('postgresql://username:password@host:socket/database')


geodataframe = gpd.GeoDataFrame(pd.DataFrame.from_csv('<your dataframe source>'))
#... [do something with the geodataframe]

geodataframe['geom'] = geodataframe['geometry'].apply(lambda x: WKTElement(x.wkt, srid=<your_SRID>)

#drop the geometry column as it is now duplicative
geodataframe.drop('geometry', 1, inplace=True)

# Use 'dtype' to specify column's type
# For the geom column, we will use GeoAlchemy's type 'Geometry'
geodataframe.to_sql(table_name, engine, if_exists='append', index=False, 
                         dtype={'geom': Geometry('POINT', srid= <your_srid>)})

Worth noting that 'if_exists' parameter allows you to handle the way the dataframe will be added to your postgres table:

    if_exists = replace: If table exists, drop it, recreate it, and insert data.
    if_exists = fail: If table exists, do nothing.
    if_exists = append: If table exists, insert data. Create if does not exist.

Best Answer

Related Solutions

[GIS] Cannot insert simple geometry data

[GIS] Adding GeoPandas Dataframe to PostGIS table

Related Question