Welcome back, friends!
With the release of Tableau 2019.2 and the native support of PostGIS, I’ve been working on how to incorporate this new functionality into my day-to-day work.
This is similar to a problem that came up at work: How do you create custom outlines of regions based on store locations? I know this might seem trivial, but you could have situations where stores even in the same zip code are in different regions.
So how do you solve this? PostGIS to the rescue!
If you aren’t familiar with PostGIS or how to get started with it, go back and read my blog post on it here.
First I’m going to upload these into PostGIS. I created a table with the following structure:
Then, I clicked on the table, clicked Import/Export and import your data (after saving my file as a csv from Excel).
Next, lets create our geometry column. I have a tendency of importing my lat and long in as separate columns, and then running a couple SQL statements:
alter table starbucks add column geom geometry(Point, 4326);
update starbucks set geom=st_SetSrid(st_MakePoint(longitude, latitude), 4326);
create index starbucks_geom_idx on starbucks using gist (geom);
What this does in a few words:
1.) Adds a geometry column.
2.) Populates the new geometry column with the lat long fields.
3.) Creates a spatial index on the new geometry column.
Visualizing this in Tableau, we see something like this:
Creating the custom regions
To create the custom regions, were going to use Voronoi Polygons. From Wikipedia, a Voronoi diagram is a partitioning of a plane into regions based on distance to points in a specific subset of the plane.
In plain English, we will be creating a region around a point, until it reaches another point. Luckily, there’s a function for this in PostGIS.
First we’re going to find the centroids for each region:
SELECT region, (ST_Dump(ST_Centroid(ST_collect(geom)))).geom::Geometry(point, 4326) AS geom
GROUP BY 1;
Now, use a voronoi diagram to get actual dividing edges between the region centroids:
SELECT (ST_Dump(ST_VoronoiPolygons(ST_collect(geom)))).geom::Geometry(polygon, 4326) AS geom
FROM (SELECT region, (ST_Dump(ST_Centroid(ST_collect(geom)))).geom::Geometry(point, 4326) AS geom
GROUP BY 1) region_centers;
As you can see you get some weird stuff, that covers a lot of the ocean, etc. Let’s intersect it with the outline of the United States. I’m also going to create a table with a spatial index to speed up performance:
create table starbucks_voronoi AS
SELECT (ST_Dump(ST_VoronoiPolygons(ST_collect(centroid_geom)))).geom::Geometry(Polygon, 4326) AS geom
FROM (SELECT region, (ST_Dump(ST_Centroid(ST_collect(geom)))).geom::Geometry(point, 4326) AS centroid_geom
GROUP BY 1) region_centers;
CREATE INDEX starbucks_voronoi_gix
ON starbucks_voronoi USING GIST (geom);
SELECT (ST_Dump(ST_Intersection(a.geom, b.geom))).geom::Geometry(Polygon, 4326) AS geom
FROM tl_2018_us_state a
inner JOIN starbucks_voronoi b
You should get something that looks like this:
Bringing it all together
Let’s tie our region numbers back to the new custom regions we created:
The results aren’t the prettiest, but not every “sub” region is populated with a Starbucks location.
Now adding our Starbucks locations back in:
That’s it. I’ll work on cleaning this up (including getting rid of the state outlines), but that gives you an idea of how to do it.