Geographic Information Systems Asked on August 10, 2021
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?
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP