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?

  • 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 6:28
  • There's several real time update capabilities. You can use sync tables 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 docs.cartodb.com/cartodb-platform/… – cholmes Sep 26 '14 at 14:36
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.

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.

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.