GeoPandas dataframe from PostGIS table having date column

Question

I have this PostgreSQL PostGIS database

and I want to read this data in GeoPandas.

I am able to create the GeoPandas database when I exclude the columns having dates/datetime

#Reading data from postgis in geopandas 

conne = psycopg2.connect(user='postgres', password='your_password', host='localhost', port= '5432', database="agro_dss")

print ("connection to the database suscessfull")

#all columns except date columns 


sql = "select geometry, state_name, district_name, rainfall, temperature_max, temperature_min, humidity, humidity2, wind_speed, wind_direction, cloud_cover from forecast_data"


pstgis_df = gpd.GeoDataFrame.from_postgis(sql, conne, geom_col='geometry')

pstgis_df

I have followed the steps as in GeoPandas documentation

but it is showing error

conne = psycopg2.connect(user='postgres', password='your_password', host='localhost', port= '5432', database="agro_dss")

print ("connection to the database suscessfull")

sql_base = "select geometry, rainfall from forecast_data"

pstgis_df = gpd.GeoDataFrame.from_postgis(sql, conne, geom_col='geometry' , parse_dates={'Issue_date': '%Y-%m-%d'})

what would be the proper syntax for adding the columns having date in postgis table to GeoPandas dataframe?

when I am passing date column in the SQL query, its showing error. How to add that column?

Answer 1

Postgres is case sensitive. The error message tells you that issue_date is not found, and goes as far as suggesting that you may be meaning Issue_date, with a capital I.

To reference an uppercase column name, it must be within double quotes

select "Issue_date" from myTable

You would have to escape it within the query string

sql_base = "select geometry, rainfall,\"Issue_date\" from forecast_data"