“Find which hotels have pubs within 500 meters.”

“Find which houses, priced under $750,000, are less than 2km away from a school.”

“Where were crimes committed closeby to other, similar crimes in the same neightbourhood?? (I would very much like to avoid living around a crime spree zone!)”

To answer questions like these, we need to be able to not only display spatial objects but compare them with one another.

Below, I have the locations of crimes in Sunset, Vancouver during 2017. I’m new to the city and am looking to purchase a house but want to first analyse crime in the area.

And there’s a lot of crime in this area for 2017. But not all of it is a deal-breaker, I’m really only concerned with theft, so I want to avoid areas where there have been clusters of thefts – that is, where a theft has occurred not to far from another theft.

To begin with how to answer these questions, let’s take a look at the spatial functions Tableau provides us.

Basic Spatial Functions

MAKEPOINT(lat, lon) – creates a spatial point from a given set of coordinates (typically a latitude and longitude).

MAKELINE(pointA, pointB) – draws a line between two points created using MAKEPOINT.

INTERSECTS – determines whether two geometries intersect.

Now, do any of these jump out at you? INTERSECTS looks perfect for our questions above – there’s just one catch. To compare two objects, they both need to be in the same record.

So how do we get there? Simple, we perform a join.

Joining Tables Where Two Geometries Intersect

For this exercise, I’m using Vancouver crime data (https://www.kaggle.com/datasets/wosaku/crime-in-vancouver). Our data, truncated to 2017, looks like this when imported:

Now, let’s refine our query so we know exactly how to join data –  I want to find all crimes of type ‘Thefts from vehicle’ that occurred within 150 meters of another crime of type ‘Theft of a vehicle’. Basically, I want to avoid areas with high vehicle crime because I just bought my dream car.

First, we join the table to itself.

Now, we want to join where this crime location is within 150 meters of the other table’s crime location. Therefore, we can join table where the latitude and longitude are equal because that only gives results where two crimes take place in the exact same location.

I want the location of one table to be a radius of 150 meters around each crime, then I want to find crimes in the second table that occur within that radius.

STEP 1

First, I’ll filter the two tables on the types of crimes I’m looking for.

STEP 2

I’ll join the tables. To join correctly, we need to create some calculations.

Calculation on the left table:

Calculation on the right table:

Finally, I select the ‘Intersects’ option when matching both fields. This ensures Tableau with find all points in the right table that falls in the area of the 150 meter buffer of points in the left table.

Building the Map

Now it’s time to build our map. I’ll go to a new sheet and create measures similar to those I’ve calculated in the join.

Crime 1 Point:

Crime 2 Point:

Crime 1 Buffer Area:

I then double-click on Crime 1 Buffer Area to create the base map. I then drag Crime 1 Point onto the map – as I do, a Layers dialog will appear – I drag the pill onto the layers icon to add those points as a new layer on the map.

Finally, I’ll repeat this with Crime 2 Point and select contrasting colours for both crime points.

And that gives us our final chart – exactly what we need! Compare this to the original chart…instead of manually searching everything, our join has narrowed the search, and I can clearly see areas I want to avoid!

This example used a fixed search radius of 150 meters – by making this variable, and accounting for multiple types of crimes that occur near each other, just think of how powerful an exploration tool this kind of spatial join can be.

 

 

The Data School
Author: The Data School