If you are looking to take a spatial file containing different areas (such as regions, states, boroughs) and overlay it with additional information of an occurrence within those areas, this blog will help you. This example will specifically result in a grid-like style using the Make Grid tool in Alteryx, but be aware there are other methods out there to reach the desired output.
The following example is looking at the number of rat sightings in New York. We need two datasets:
1) Rat Sightings: contains the latitude and longitude for each rat sighting across New York
2) Spatial File: contains geometry information for each borough in New York
The steps to be taken in Alteryx:
1) Input the Rat Sightings dataset and clean this up to ensure fields with missing latitudes and longitudes are filtered out of the view.
2) Drag and drop a Create Points tool - this will create a column named 'Centroid' that contains a new spatial point per row. This is made using the longitude and latitude for that row.
Hint: if you add a Browse tool after the Create Points tool here you will be able to view all the different rat sightings on a map of New York.
3) Input the Spatial file containing geometry data for the New York boroughs - in this example this is Polygon data.
4) We want Alteryx to create a grid of the spatial data provided, but first to ensure only one grid is created we must combine the different borough spatial objects. To do this use a Summarize tool, select the spatial object field and select the spatial > combine action so that your configuration looks like this:
5) Now the data is ready to be made into a grid. Drag and drop the Spatial Make Grid tool into the workflow. In the configuration window ensure your spatial object field is selected and set up your desired grid size view. In this example I have selected a grid size of 1 mile. Finally, select how you wish to format your grid with the following options:
a) Generate Single Grid for Entire Layer: this will create one grid for the extent of the data points within the dataset.
b) Generate Grids for Each Object: this will create a grid for each spatial object within the layer and cover the extent of the object. The option to 'Clip to Polygons' is available here too which allows the grid to cut its boundary to the size of the polygon.
In this example, Generate Grids for Each Object was selected with Clip to Polygons checked as shown below:
So far the workflow for this section will look like this:
6) The two data streams are now ready to be combined together and this can be achieved using the Spatial Match tool. This forms a relationship between to sets of spatial objects to determine if the objects contain one another.
There are two anchors to a Spatial Match tool and my coach Andy used a good analogy for remembering the difference:
a) Target: think of this as each individual dart
b) Universe: think of this as the dartboard (or the bigger one)
Overall, it is about where the target intersects the universe. Therefore, within this example our Rat Sightings dataset is the Target and the NY borough dataset, which we made the grid with, is the Universe.
The output of a Spatial Match tool is either 'M' for matched or 'U' for unmatched. We will be using Matched here.
7) Finally, the last step before this can be taken into Tableau Desktop, involves counting how many sightings there were within our new grids. This can be done with another Summarize tool. In this case each sighting was given a 'Unique Key' and so we count this field. We want to know per grid so we group by our 'Grid Name' field and finally we want to return our spatial field 'Grid' so that Tableau automatically populates this as a map (spatial) view. Therefore, the configuration will look like this following:
8) The workflow is now ready to view with a Browse tool and output with the Output tool.
The Browse will offer a view like the following in Alteryx, whereby the left map visualises the grids and the right results table allows you to select a row and highlight where that sits on the map. We can also see here the 'Count' of sightings per Grid.
Last but not least, my end workflow looked like this:
If you wanted to use this within Tableau Desktop now you would be able to create a view like the following:
Note: I removed borders here, but with them you would see a similar grid like view as seen in Alteryx.