Move metadata tables to Google Spreadsheets and sync to CartoDB

[peterdesmet]

After talking with @karenrobberechts, I realized that the CartoDB editor is not ideal for metadata maintenance. Unlink a spreadsheet, it cannot filter and edit at same time, does not allow bulk editing or importing without SQL knowledge, and does not have an undo function. It is useful for creating maps and to connect data with metadata through its API though.

I explored a solution that would allow to do all that, and still have the CartoDB functionality: Google Spreadsheets synced with CartoDB. The way this would work is to manage the metadata in a Google Spreadsheet and sync it automatically every hour with CartoDB.

Advantages

• Bulk editing
• Copy/pasting
• Undo functionality
• History of file revisions (i.e. can serve as backups)
• Optionally less complicated date formats
• Possible to add comments
• All files can be contained as sheets in a single file (though I have to test the sync mechanism)
• CartoDB account only required for those who want to make visualizations (not required to view them).
• Still possible to create maps in CartoDB
• Still possible to use CartoDB API

  Drawbacks (I think these are minor)

• Requires a Google account
• Less rigorous data validation, but conditional formatting goes a long way
• Receiver locations cannot be moved on the map: you'd have to update the coordinates.
• Only syncs every hour, so there is some lag
• Less control over the data formats, but CartoDB seems to do a good job detecting those.

@ammouton, @karenrobberechts what do you think?

[peterdesmet]

Decided to do this.

[karenrobberechts]

the order of the columns for table 'fish_tracking_tags'

project catched_at tag_id tag_type extra column 'PIT number' scientific_name catch_lenght catch_weight sex extra column 'DURIF' catch_location catch_latitude catch_logitude released_at released_location released_latutude released_longitude is_ghost remarks

[karenrobberechts]

the order of the columns for table 'fish_tracking_receiver'

project receiver_id receiver_code location the_geom deployed_at data_downloaded_at battery_empty_at removed_at location_manager_id inbo_code remarks

[peterdesmet]

All these changes have been implemented.

• Receivers are synced every hour
• Tags are synced every day
• Data validation has been added for numerous fields

@karenrobberechts, pass by if you want so input on how to use these files.