CartoDB Sync with Merged Tables

Question

I'm trying to inspect town assets, like pavement and signs. I want to display that information on a web-based map (CartoDB). I also want to be able to pull the most up to date information to generate reports using Crystal Reports or other similar reporting software.

My first attempt was to upload the shapefiles to CartoDB. Then I created a table with all the attributes that I would be filling out in the field that would stay synced in my DropBox. This would allow me to access the table to perform reports or update any information using excel, etc on my desktop. But I needed to merged that table with the shapefile, and if I did that, it would turn off syncing.

Does anyone know how I could get this to work? And what the best method of field collecting the data would be in order to keep everything synced with that one table in my DropBox?

Answer 1

You can apply queries against both of the tables and the result of the query will be updated dynamically every time someone looks at a map that uses the query. For instance, if you have a table called sync_data, and another table of interesting things that is not sync'd interesting_polygons, you could make a map with a query like this, which will leverage the results from the sync'd table:

SELECT
  count(s.*) as num_attrs,
  p.the_geom_webmerctor
FROM
  interesting_polygons as p
JOIN
  sync_data as s
ON ST_Intersects(s.the_geom, p.the_geom)
GROUP BY p.the_geom_webmercator