TransWikia.com

GeoPandas dataframe from PostGIS table having date column

Geographic Information Systems Asked on August 10, 2021

I have this PostgreSQL PostGIS database

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?

One Answer

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"

Correct answer by JGH on August 10, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP