So, you’re a swarm (former foursquare, before the split) user, and want to see all your check-ins in a map. Of course, you want to use Carto for that. Let’s do it, with a little help of IFTTT.

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?

seleccion_085
Click to enlarge

Ok. Get rid of the IMAGE( part, and leave just the VenueMapImageUrl field.

seleccion_085
Click to enlarge

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)

seleccion_088
Click to enlarge

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)

 

seleccion_089
Click to enlarge

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.

seleccion_090
Click to enlarge

 

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

seleccion_090
Click to enlarge

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)
seleccion_090
Click to enlarge

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[2];
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[1]::numeric, coords_array[2]::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[1]::numeric as lat, coords[2]::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

Selección_093.png

Not perfect, but cool enough B-)

 

Advertisements