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?

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 Dec 15 '20 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 Dec 15 '20 at 11:32
  • tried that here already i.stack.imgur.com/UBNcy.png @BERA Dec 15 '20 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.stack.imgur.com/pqfiQ.png, even after correcting the suggestions i.stack.imgur.com/1f0ko.png Dec 15 '20 at 12:04
  • Please don't post pictures of error messages/code but the actual text.
    – JGH
    Dec 15 '20 at 13:16
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"
3
  • thanks a lot @JGH, it resolved the problem. Dec 15 '20 at 14:44
  • 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. Dec 15 '20 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
    – JGH
    Dec 15 '20 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.