Mapping Square Payments During the Super Bowl

Written by Tom Carden.

Data visualization touches many aspects of Square’s business, from the interactive reports on our merchant dashboards to our internal metrics and monitoring tools. In addition to these activities we look for occasional opportunities to visualize the activity around an event or promotion.

As sports fans descended on New Orleans in anticipation of Super Bowl XLVII last month, we were asked by qz.com’s Ritchie King if it was possible to illustrate the effects of the game on mobile payments in the city. To catch the Super Bowl news cycle we would need a quick turnaround and tight focus. Never ones to shy away from a challenge, I joined with Faryl Ury from Square’s communication team and we set about gathering the necessary data and experimenting with different views of payment activity.

Don’t miss Ritchie’s Quartz post for more background on the figures and trends. Below we outline some of the thinking behind the map and the tools and techniques we used to create it.

Fair Share

We want to faithfully share the stories in our data without revealing too much specific information about any one merchant or payment. This means striking a balance between illustration and representation. We felt that covering the whole city for a few days had the right level of detail while remaining sufficently general.

Provided we have the right credentials and are securely on our own network, Square’s data is readily accessible to us for analysis. Because we were dealing with a single city and only two weeks of activity we were able to export records of payment data (without personally identifiable information) into CSV files and work with them directly in our chosen tools.

*[CSV]: Comma Separated Values

For example, if we selected a three one-dollar payments taken inside the stadium at kickoff we would save the following file as exported_points.csv:

id,price_cents,latitude,longitude,created_at
1,100,29.950833,-90.081111,2013-02-03T23:30:00Z
2,100,29.950833,-90.081111,2013-02-03T23:30:00Z
3,100,29.950833,-90.081111,2013-02-03T23:30:00Z

Clearly our real data is more numerous and more diverse, but if you’re trying to make a similar map you should aim for something in the above form: showing where, when, and what.

We used PostGIS (geographic extensions to the Postgres database) to store and manipulate our data. Once we had prepared a CSV file in the form above, importing to PostGIS and adding geographic columns and indexes looked like this:

*-- clean slate:*
**DROP** **TABLE** IF **EXISTS** superbowl_payments;

-- create a table to store the columns from our CSV file:
CREATE TABLE superbowl_payments (
    id integer default NULL,
    price_cents integer default NULL,
    latitude float8,
    longitude float8,
    created_at timestamp NOT NULL
);

-- use COPY to import in one shot:
COPY superbowl_payments FROM '/path/to/exported_points.csv' WITH DELIMITER AS ',' CSV HEADER;

-- using the SRID from the existing census data (4269):
SELECT AddGeometryColumn ('public','superbowl_payments','geom',4269,'POINT',2);

-- assuming our source data used the SRID 4326 for WGS84 GPS points:
UPDATE superbowl_payments SET geom = ST_Transform(GeomFromEWKT('SRID=4326;POINT(' || longitude || ' ' || latitude || ')'),4269);

-- speed up geographic queries over our points:
CREATE INDEX ON superbowl_payments USING GIST(geom);

Setting The Scene

Our final graphic was produced with the open source TileMill application, which allowed us to quickly iterate on the design and combine data from several sources.

TileMill bundles together a large number of open source mapping technologies into an easy to use desktop package. It renders maps using the powerful Mapnik mapping engine, loads data from PostGIS or text files in GeoJSON format, and offers a CSS-like language called Carto for specifying visual appearance such as colors and fonts.

In addition to our own data we wanted an accurate but subtle background map to plot our points on. The US census provides many different kinds of geometry files, including the lowest level census “blocks”, which can be freely downloaded. These files are free to use for any purpose and can be joined with useful summary statistics about population, land area, water area and a multitude of demographic information as well. We downloaded the census block files for Louisiana in the popular shapefileformat and imported them into our PostGIS census database using GDAL’s shp2pgsql tool:

$ shp2pgsql -c -D -s 4269 -I tl_2010_22_tabblock10.shp la_census_blocks | psql -d census

In the above command tl_2010_22_tabblock10.shp is the Tiger Line 2010 shapefile of census blocks for state 22, which is Louisiana. Setting up PostGIS and the GDAL tools is left as an exercise for the reader. We used homebrew for Mac since we were working locally with TileMill on the desktop.

Lastly, once our base map and payment data were prepared we also wanted some simple overlay information; in this case a single point label marking the location of the game. This was a hand-written GeoJSON file with the location found on the Superdome’s wikipedia page:

{
    "type"**:** "Feature",
    "geometry"**:** {
        "type"**:** "Point",
        "coordinates"**:** [**-**90.081111,29.950833]
    },
    "properties"**:** {
        "name"**:** "Mercedes-Benz Superdome"
    }
}

Show All The Things

With the data in place, building a map in TileMill is relatively straight forward. We began with a blank black canvas:

Map {
  **background-color:** black;
}

From our three data sources we created three layers: Census Blocks (id=blocks), Square Payments (id=payments) and Labels (id=superdome).

Census Blocks

The blocks were sourced from PostGIS with a single query:

(**select** *****
 **from** la_census_blocks
 **where** aland10 **>** 0
) **as** blocks

And the SRS was set to +proj=longlat +ellps=GRS80 +datum=NAD83 +no_defsso that TileMill could correctly match up the geometry with its default Google Maps-style Mercator projection.

*[SRS]: Spatial Reference System

The block styles were the simplest to write in Carto, since the query already filtered out blocks that had no land we could style the remaining blocks dark gray on our black background:

**#blocks** {
  line**-color:**#000;
  line**-width:**0**.**5;
  polygon**-opacity:**1;
  polygon**-**fill**:**#333;
}

The Carto file is translated into a Mapnik style file which will find the required symbolizers to render the polygons as described. Having written Mapnik files directly — but being new to Carto — this was very refreshing!

Superdome Label

The label was simply added to TileMill from the GeoJSON file shown above. The styles were a little more verbose thank the blocks, specifying styling for both a simple circular marker and a text label in Square’s new in-house typeface, Market. The text-name uses TileMill’s expression support to use the name property of the object from our GeoJSON file:

**#superdome** {
  marker**-width:**10;
  marker**-**fill**:**#fff;
  marker**-**line**-color:** #000;
  marker**-**line**-width:** 1;
  marker**-**allow**-**overlap**:**true;
  **text-**name**:** [name];
  **text-**halo**-**fill**:** #000;
  **text-**halo**-**radius**:** 1;
  **text-**face**-**name**:** 'SQ Market Bold';
  **text-align:** **center**;
  **text-**allow**-**overlap**:**true;
  **text-**wrap**-width:** 100;
  **text-**dy**:** 20;
  **text-**fill**:** #fff;
  **text-size:** 24;
}

Payment Points

The query for our payment layer was a little more complex. Though TileMill supports expressions for point size it’s easier to put complex calculations in the query so that the expression doesn’t need to be repeated multiple times in the style file. Here we query the points and use square-root to ensure that when we set the radius of the circle the resulting area is proportional to the payment amount. We also select the day of the query (accounting for the time zone difference) so that we can style each day individually later. Finally we sort by the price so that larger payments are drawn first and all payments will be at least partially visible:

(**select** *****,
        sqrt(price_cents*****quantity) **as** sqrt_price_cents,
        text(date(created_at **-** interval '6 hours')) **as** **day**
 **from** superbowl_payments
 **order** **by** sqrt_price_cents **desc**
) **as** points

Finally, the styles for the points used one of TileMill’s nicest features, composite operations, to enable additive blending and create the attractive glow effect we were looking for.

**#payments** {
  marker**-width:** 1 **+** [sqrt_price_cents] **/** 10;
  marker**-**fill**-opacity:** 0;
  marker**-**line**-width:** 0;
  marker**-**line**-color:** black;
  marker**-**fill**:**#128;
  marker**-**allow**-**overlap**:**true;
  marker**-**comp**-**op**:** plus;
  */* filters for the days of interest: */*
  [day **=** "2013-02-01"]**,**
  [day **=** "2013-02-02"]**,**
  [day **=** "2013-02-03"] {
    marker**-**fill**:**#128;
    marker**-**fill**-opacity:** 0**.**2;
  }
}

The marker-comp-op means that when the circles are rendered on top of each other the colors are combined with addition instead of conventional opacity calculations (a linear blend of the source and destination). This has the effect of blowing out or over exposing the colors in areas of high activity. For a true statistical graphic this effect would be undesirable, but as we move towards more illustrative data graphics it makes an effective compromise. We get the best of both worlds: our graphic is data-driven and it looks cool.

Final Graphic

With the above layers and styles in place we were able to quickly create three graphics. To create before and after graphics we simply adjusted the day filters in the point styles, commenting things out and exporting files as needed.

The first showed payment activity the weekend before the Super Bowl:

The second showed payment activity the weekend of the Super Bowl itself:

And the last showed payments from both weekends, with the Super Bowl weekend in red:

There are many ways to achieve similar results but we feel that TileMill and PostGIS allowed us to iterate on these graphics much faster than would have been possible without custom software. We’ll definitely be reaching for them again for future projects. Tom Carden (@RandomEtc) | Twitter *The latest Tweets from Tom Carden (@RandomEtc). British designer/developer in San Francisco. Currently @square…*twitter.com