Performing a spatial join of two large datasets. The first is a set of 7k recycling locations, the second is a UK wide postcode list of 1.6m postcode centroids. The spatial intersect is a makepoint of the RLs to a buffer around the postcodes.
This works well for small areas. However, when I try to scale up to UK level, the size of the joins runs into millions / low billions!
Rendering number of rows for different buffer distances:
- 1 mile = 4.4 million rows of data (5 minutes to calculate)
- 2 miles = 13.6 m rows (10 minutes to calculate)
- 5 miles = 58.2 m rows (40 minutes)
- 50 miles = too many to render
I am using a Mac Book Pro with Tableau Desktop. Data sources have been stripped down in Tableau Prep and extracts created for both datasets. Creating an extract of both data sources for 20 mile buffer was still running after 8 hours. I have reviewed optimising performance white paper for extracts.
Options to consider:
- Faster processing computer than laptop
- Relational database (budget may not allow)
- Stripping out columns of unnecessary data (testing underway)
Any general advice on best practice for spatial intersects for such big data sets? Both in terms of approaches and resources required.
Hi @Sarah Battersby
Does materializing extracts come into play at any point? If so, at which point?
https://help.tableau.com/current/pro/desktop/en-us/extracting_optimize.htm
I did try this last week, but it broke the link to the extract when I duplicated worksheets.
Thanks
Colin
