First things first: Sign up to IFTT, and enable this recipe. It will create a Google Drive spreadsheet and add a new line for each new check-in in your swarm account. But WAIT.
Go to the recipe. Do you see the formatted row form field?
Ok. Get rid of the IMAGE( part, and leave just the VenueMapImageUrl field.
That’s it. Now, make a test check-in, just to make sure everything is working properly, Go to your Drive account, and look for the sheet named Foursquare checkins. Do you see something like this? (Just one row, right now)
Cool! Now, add a new row to your sheet, with the field names (this will ease the SQL query you will need to throw later)
Ooooook. Now, all your checkins will be added to your Google Drive Spreadsheet. Great, right?
Time for the Carto part. SQL fun!
Go to your Carto account, and create new sync table, connecting with Google Drive.
Once you hit Connect and give Carto permission to access your Google account, you’ll see a list of your Google Drive docs. Just pick the Foursquare_checkins table
When asked, select a sync period. It depends on how often you use swarm, but 1 hour sounds just fine.
At this point, we have two pieces of a puzzle that need to fit together if we want to see our checkins in the map:
- We have a Carto table, containing a geometry column, named the_geom, which needs to be populated in order to see something in the map
- We have a field in that Carto table, named map_url, containing a Google Maps url like this one (framed in red, the interesting part)
Do you see where this is going? The idea is to extract latitude and longitude from map_url, and use them to build the_geom column. And when? Each time this table is updated with a new checkin. Using this trigger
CREATE OR REPLACE FUNCTION update_geom_column() RETURNS trigger AS $BODY$ DECLARE coords_array varchar := ARRAY; BEGIN if new.map_url is null then raise exception 'map_url cannot be null'; end if; -- Get coords using regexp_matches select regexp_matches(NEW.map_url, 'center=(-?[\d]*\.[\d]*),(-?[\d]*\.[\d]*)&') into coords_array; NEW.the_geom = CDB_LatLng(coords_array::numeric, coords_array::numeric); return NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; drop trigger if exists update_geom_column_trigger on foursquare_checkins; CREATE TRIGGER update_geom_column_trigger BEFORE INSERT OR UPDATE ON foursquare_checkins FOR EACH ROW EXECUTE PROCEDURE update_geom_column();
That sounds like a good plan, right?
Well… not yet …
The problem with sync tables in Carto is they are re-created each time the synchronization happens. So, the trigger won’t work. I opened an issue about it.
Ok, don’t worry. There’s another solution. We can still build the_geom by manually running this query, after our table is synchronized
with foo as (select cartodb_id, regexp_matches(map_url, 'center=(-?[\d]*\.[\d]*),(-?[\d]*\.[\d]*)&') as coords from foursquare_checkins), bar as (select cartodb_id, coords::numeric as lat, coords::numeric as lng from foo) update foursquare_checkins set the_geom=CDB_LatLng(lat, lng) from bar where foursquare_checkins.cartodb_id = bar.cartodb_id
So, you should run this query each time you enter the table after a synchronization. Or write a cron script to run the query, via Carto SQL API, from time to time if you want.
At the end, you’ll see something like this
Not perfect, but cool enough B-)