CartoDB/cartoframes

Missing column when uploading CSV through GeoPandas to CARTO

Closed this issue · 2 comments

manmorjim commented

When we read a CSV file using read_file from Geopandas and then we upload it to CARTO specifying the geometry column name with geom_col the error column "geometry" of relation "test_geopandas" does not exist is thrown:

from cartoframes import to_carto
from cartoframes.auth import set_default_credentials
import geopandas

set_default_credentials("test_creds.json")

gdf = geopandas.read_file("files/acs_sociodemogr_b758e778_min.csv")

to_carto(gdf, table_name="test_geopandas", if_exists="replace", geom_col="geom")

Error stack trace:

Traceback (most recent call last):
  File "test_newapi.py", line 9, in <module>
    to_carto(gdf, table_name="test_geopandas", if_exists="replace", geom_col="geom")
  File "/home/ubuntu/.local/lib/python3.7/site-packages/cartoframes/io/carto.py", line 116, in to_carto
    table_name = context_manager.copy_from(gdf, table_name, if_exists, cartodbfy)
  File "/home/ubuntu/.local/lib/python3.7/site-packages/cartoframes/io/managers/context_manager.py", line 61, in copy_from
    self._copy_from(gdf, table_name, columns)
  File "/home/ubuntu/.local/lib/python3.7/site-packages/cartoframes/io/managers/context_manager.py", line 281, in _copy_from
    self.copy_client.copyfrom(query, data)
  File "/home/ubuntu/.local/lib/python3.7/site-packages/carto/sql.py", line 381, in copyfrom
    raise CartoException(e)
carto.exceptions.CartoException: ['column "geometry" of relation "test_geopandas" does not exist']

CSV:

geoid,do_label,do_area,do_perimeter,do_num_vertices,geom
"090093508004",Block Group 4,"375860.883","3458.473","41","POLYGON((-73.051797 41.570421, -73.052307 41.569889, -73.052673 41.569435, -73.052788 41.569214, -73.053047 41.56723, -73.052963 41.566769, -73.052765 41.566185, -73.052345 41.565197, -73.051933 41.56451, -73.051193 41.563366, -73.051048 41.563286, -73.050223 41.563036, -73.048123 41.563471, -73.048412 41.56422, -73.047242 41.564436, -73.046625 41.564463, -73.044368 41.564178, -73.043259 41.564101, -73.043014 41.564769, -73.042794 41.56493, -73.042731 41.566441, -73.043388 41.566443, -73.043833 41.566374, -73.044334 41.566039, -73.045565 41.566278, -73.045831 41.566411, -73.04571 41.568111, -73.046673 41.568136, -73.046885 41.567202, -73.046965 41.566427, -73.047032 41.566389, -73.048036 41.566396, -73.048209 41.566606, -73.048331 41.567177, -73.048847 41.567247, -73.048623 41.569197, -73.048595 41.569599, -73.048559 41.570013, -73.050301 41.570003, -73.050851 41.570074, -73.051797 41.570421))"
"340130217022",Block Group 2,"545594.248","3764.484","41","POLYGON((-74.289875 40.838533, -74.290016 40.838427, -74.290592 40.838309, -74.290991 40.837937, -74.291318 40.837277, -74.290559 40.836888, -74.289672 40.837701, -74.288522 40.837032, -74.28695 40.838486, -74.286882 40.838688, -74.286603 40.838819, -74.286288 40.838749, -74.28512 40.838349, -74.28383 40.838209, -74.283275 40.838074, -74.282949 40.837944, -74.282594 40.837937, -74.28239 40.838175, -74.282483 40.838502, -74.282074 40.838811, -74.280951 40.838417, -74.27885 40.841141, -74.27976 40.841539, -74.280459 40.842052, -74.283611 40.844947, -74.284358 40.845241, -74.28623 40.845571, -74.287131 40.845729, -74.287198 40.845615, -74.288756 40.843781, -74.289413 40.843079, -74.290215 40.842163, -74.289607 40.841586, -74.288784 40.841286, -74.287642 40.840574, -74.287775 40.840422, -74.288618 40.839743, -74.289227 40.839183, -74.288523 40.838638, -74.289275 40.838511, -74.289875 40.838533))"
"390410124003",Block Group 3,"3365443.78","7237.71","47","POLYGON((-82.997559 40.150599, -82.997345 40.150627, -82.996283 40.150393, -82.995522 40.150181, -82.994649 40.149796, -82.993921 40.149309, -82.993463 40.148894, -82.992887 40.148138, -82.992625 40.147628, -82.99183 40.145948, -82.991295 40.145089, -82.990841 40.144528, -82.990414 40.144093, -82.989827 40.143589, -82.989199 40.143142, -82.988473 40.142718, -82.987298 40.142193, -82.986403 40.141899, -82.985304 40.141647, -82.984182 40.141501, -82.982752 40.141406, -82.981356 40.141367, -82.979541 40.141398, -82.97838 40.141469, -82.976264 40.141639, -82.974986 40.141771, -82.97265 40.141987, -82.972467 40.141934, -82.970806 40.14208, -82.970154 40.150949, -82.970026 40.152179, -82.969733 40.153946, -82.969405 40.155426, -82.969707 40.155619, -82.970404 40.155967, -82.971457 40.156325, -82.972095 40.156457, -82.972994 40.156539, -82.976861 40.156701, -82.979853 40.156829, -82.983353 40.156962, -82.984611 40.157022, -82.987361 40.157128, -82.992681 40.157349, -82.993411 40.157326, -82.994978 40.157209, -82.997559 40.150599))"

GeoDataframe:

          geoid       do_label     do_area do_perimeter do_num_vertices                                               geom geometry
0  090093508004  Block Group 4  375860.883     3458.473              41  POLYGON((-73.051797 41.570421, -73.052307 41.5...     None
1  340130217022  Block Group 2  545594.248     3764.484              41  POLYGON((-74.289875 40.838533, -74.290016 40.8...     None
2  390410124003  Block Group 3  3365443.78      7237.71              47  POLYGON((-82.997559 40.150599, -82.997345 40.1...     None
manmorjim commented

Environment

cartoframes version: 1.0rc1
Python version: Python 3.7.3 (default, Oct 7 2019, 12:56:13)
OS: Ubuntu 19.04

Jesus89 commented

When using geopandas.read_file it adds an extra column to the DataFrame called geometry, which is filled with None.

Then CARTO removed that column when "cartodbfying" the new table, because it only allows one geometry column, and two are uploaded (geom and geometry).

The recommendation is to use pandas instead of geopandas to load the CSV file. Instead of geopandas.read_file('file.csv') do pandas.read_csv('file.csv'). Without the extra geometry column, everything goes OK. However, I'll add a fix to run your code without issues.

  • 🚀1