2

I have a postgres spatial table that I want to convert to a geopandas GeoDataFrame. In order to do so I wrote the following piece of code:

#'pg_tab_clip' is the name of the postgres spatial table
#'b' is an integer
#'con' is the connection to postgres

    select = etl.fromdb(con, "SELECT ST_AsText(geom) FROM {0} WHERE buffer = {1}".format(pg_tab_clip, b))
    temp = select[1:len(select)]
    buffer_polygs = [shapely.wkt.loads(t[0]) for t in temp]

    buffer_gdf = gpd.GeoDataFrame()
    for i, bp in enumerate(buffer_polygs):
        buffer_gdf.loc[i, 'geometry'] = bp

When I run the script I get the following error:

Traceback (most recent call last):
  File "02_culvert_processing_v1.py", line 417, in <module>
    main(working_folder, alr_path, pg_tab_culv, N, margin, pg_con)
  File "02_culvert_processing_v1.py", line 390, in main
    loop_tiles(N, margin, con, cursor, alr_path, srid, pg_tab_buf, pg_tab_tile1, pg_tab_clip, buf)
  File "02_culvert_processing_v1.py", line 274, in loop_tiles
    process_raster(con, pg_tab_clip, tile2_tif, srid, i, j, margin, culv_count, buf)
  File "02_culvert_processing_v1.py", line 143, in process_raster
    buffer_gdf.loc[i, 'geometry'] = bp
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py", line 190, in __setitem__
    self._setitem_with_indexer(indexer, value)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py", line 366, in _setitem_with_indexer
    self._setitem_with_indexer(new_indexer, value)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py", line 611, in _setitem_with_indexer
    raise ValueError('Must have equal len keys and value '
ValueError: Must have equal len keys and value when setting with an iterable

I don't know what's causing this problem. The strangest of all is that the script doesn't actually fail every time I run it but only sometimes (???). Also, is there perhaps a more elegant/pythonic way to do what I am trying to do? Is the iterable 'buffer_polygs' actually unnecessary?

1

Try:

buffer_gdf = gpd.GeoDataFrame(geometry=buffer_polygs)

Another way using from_postgis copied from the answer to Geopandas PostGIS connection:

import geopandas as gpd
import psycopg2  # (if it is postgres/postgis)

con = psycopg2.connect(database="your database", user="user", password="password",
    host="your host")
sql = "select geom, x,y,z from your_table"
df = gpd.GeoDataFrame.from_postgis(sql, con, geom_col='geom' )
  • 1
    First answer seems to be working fine and second answer is a very useful tip. Excellent. – Pitrako Junior Aug 14 at 17:49
  • Any ideas what the error I was getting is about? It's quite strange because I have divided a UK raster in 91 tiles (13x7) and I do some processing on each of them using the code I mentioned before. It works well until it reaches the tile number 67. Then it throws that error. – Pitrako Junior Aug 14 at 17:52
  • Nice. No sorry I dont know what is causing the error – BERA Aug 14 at 18:57

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.