0

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?

Improve this question
8
  • thanks for the suggestion, but how to fix " parse_dates={Issue_date: '%Y-%m-%d'}) " here in : pstgis_df = gpd.GeoDataFrame.from_postgis(sql, conne, geom_col='geometry' , parse_dates={Issue_date: '%Y-%m-%d'}) @BERA Commented Dec 15, 2020 at 11:29
  • I don't know how to do that, i just pasted the code from my jupyter notebook, and it was showing like its showing now, that's why i have posted the snaps for reference. @BERA Commented Dec 15, 2020 at 11:32
  • tried that here already i.sstatic.net/UBNcy.png @BERA Commented Dec 15, 2020 at 11:37
  • sorry @BERA , forgot to put the column name in inverted-commas. Thanks, it parsed the date column, but by doing so, it is parsing the date but not showing that column in dataframe. For doing so I added the column name in sql query, and when i am doing so, it’s showing error i.sstatic.net/pqfiQ.png, even after correcting the suggestions i.sstatic.net/1f0ko.png Commented Dec 15, 2020 at 12:04
  • Please don't post pictures of error messages/code but the actual text. Commented Dec 15, 2020 at 13:16

1 Answer 1

Reset to default
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"
Improve this answer
2
  • Since I am new to stackoverflow, I am not yet acquainted with the options available here. I tried to vote as this answer is useful but it showed in the popup : Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score. @JGH, thanks for your time though. Commented Dec 15, 2020 at 15:04
  • :-) don't worry, this aspect is indeed a bit unclear: only the user that asked a question can "accept" the answer. Anybody - including the user that asked the question - that find the answer useful can "upvote" it (or "downvote" it!), provided they have at least 15 points of reputation Commented Dec 15, 2020 at 15:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.