Creating Custom Regions using PostGIS and Tableau

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!

Getting started

If you aren’t familiar with PostGIS or how to get started with it, go back and read my blog post on it here.

I pulled down a list of Starbucks locations from POI Factory, and ran a simple clustering algorithm to simulate regions. You can download the Excel file here if you want to follow along.

First I’m going to upload these into PostGIS. I created a table with the following structure:

postgis_regions_01

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:

postgis_regions_02

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
FROM starbucks
GROUP BY 1;

postgis_regions_03

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
FROM starbucks
GROUP BY 1) region_centers;

postgis_regions_04

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
FROM starbucks
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
on 1=1

You should get something that looks like this:

postgis_regions_05

Bringing it all together

Let’s tie our region numbers back to the new custom regions we created:

postgis_regions_06

The results aren’t the prettiest, but not every “sub” region is populated with a Starbucks location.

Now adding our Starbucks locations back in:

postgis_regions_08

postgis_regions_07

 

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.

Happy mapping!

-Paul

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s