4

I need to overwrite a table in CartoDB using the Import API, but the system instead of overwriting the table, it creates another one with the same name and attaches an id auto-generated.

For instance:

The table stored in CartoDB is named coordinates.

When I overwrite the table, it creates a table named coordinates_1 for instance.

Is it possible to overwrite the same table with different data?

Joseph
  • 75,746
  • 7
  • 171
  • 282
Laura
  • 91
  • 1
  • 3
  • that's a good point. I am also interested on knowing if given a table we can have some real time update service to that table so the map is constantly being updated. That would be a great feature to have. hope someone answers. – Xavi Sep 24 '14 at 06:28
  • There's several real time update capabilities. You can use sync tables http://docs.cartodb.com/tutorials/realtime_maps_sync.html to automatically keep up to date online. You can also use the SQL API to update your tables directly. See http://docs.cartodb.com/cartodb-platform/sql-api.html#write-data-to-your-cartodb-account – cholmes Sep 26 '14 at 14:36

2 Answers2

1

I wrote an example commandline tool in python that does something like this. Blog post and code

The important piece is here, https://gist.github.com/andrewxhill/093c89fa45e5f657fec7#file-cartodb-utils-py-L76

You can see that the biggest limitation is that, since it does a naive DELETE followed by INSERT, if the columns of your tables differ, it will fail. You could make it a bit smarter by detecting column differences and doing an ALTER TABLE ADD COLUMN before the INSERT step.

andrewxhill
  • 2,468
  • 12
  • 17
0

In CartoDB many tables are linked to maps and cannot be overwritten directly. You need to use CartoDB PostGIS SQL to truncate your table first like this:

TRUNCATE TABLE my_layer

Then use ogr2ogr version 2.0 or greater to append new data to the truncated table using a command something like this:

ogr2ogr -append --config CARTODB_API_KEY abcdefghijabcdefghijabcdefghijabcdefghij -t_srs EPSG:4326 -f CartoDB "CartoDB:username" my_layer.shp

A detailed explanation is presented here: Data Synchronization with OGR

It isn't mentioned in the explanation above, but I use the same method to update non spatial tables with CSV files. Just use my_layer.csv to end of the ogr2ogr command instead of my_layer.shp.

Greg Krakow
  • 127
  • 2