US Population and Household Count within Trade Areas

This community has been fantastic in helping me learn quickly how to use Designer...and I'm positive someone will be able to help me once again.

I'm in need of counting households and population inside trade areas. Good thing is the trade areas are super simple for the time being (radii rings for 1, 5, and 10mi)

I have the US Consumer insights and census data packs installed.

I've been able to do some counting, but it's wildly inefficient...one workflow took 45 hrs to run only to fail due to exceeding excel's limit for rows (oops)

This is what I've pieced together after dozens of google searches. I load my input file, geocode, create my trade areas, then throw the DNB database against it for only the states that I care about & it returns hundreds of thousands of rows and takes hours to complete. I am confident there's a better way...hoping someone can help. In an ideal state, my output would show my location name (found in my input file), the population count in 1, 5, and 10mi radii and also household counts in the same 1, 5, 10mi radii.

Please help the new guy...and beat me up for my inefficient workflows...I learn best by being abused (lol)

Spatial Analysis

Accepted answers

gabrielvilella

You have shared the workflow, but with no sample for the data. Here is an example on how to match polygons and then count.

Spatial.yxzp

All comments

Qiu

@bremere
Maybe you can upload your flow so we can take a close look?

And you mentioned count, but I dont see any Summarize Tool in your snapshot.

bremere

@Qiu - hopefully this gets you what you're looking for

That probably would've been helpful.

As for the summarize tool - not something I've used before (calling myself a novice would be offensive to novices)

USPOP and HH Counter.yxmd

gabrielvilella

You have shared the workflow, but with no sample for the data. Here is an example on how to match polygons and then count.

Spatial.yxzp

Qiu

@bremere
I could not get it works as well due to missing data.
But @gabrielvilella has given a greate sample.
And there is a good weekly challenge that you can get familar with Spatial issues with.

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-184-Closest-Pubs-to-Tobacco-Dock/td-p/473331

Ben_H

Hi @bremere,

A few points, I don't know what your data actually looks like so your milage may vary!

I think you've got the Target and Universe in your spatial match backwards - I would swap the inputs round. I have it in my head for some reason that it's more efficient that way not that I can remember why.
You might need to uncheck your trade area in the spatial match tool - if it's possibe for you to get multiple matches from your DNB data against a single trade area you'll get a row for each match. This will duplicate the trade area each time and can rapidly eat up memory. It's better to do the spatial match, summarise the data (i.e. your count or whatever) then join the trade area back on after if you actually need it.
Following on from the last point, if you have ended up with any duplication in your spatial match it would mean that your allocate tool will be doing more work than it needs to.
If the problem is down to the sheer volume of data you might consider running smaller chunks in a batch macro, i.e. get it to run TX, FL etc one at a time.

Regards,

Ben

bremere

@Qiu Thank you for the weekly spatial challenge link - I'm certainly going to play around with it this afternoon.

@Ben_H Thank you all for the assistance. It's most helpful. I'm trying to modify the workflow now for my needs. Swapping out the source file for the one attached below and crossing my fingers.

@gabrielvilella I think you're onto something with the workflow doing more work than it needs to - the first time I tried to run the 5mi radius ring it took almost 14 hrs. Ben's example above runs in fractions of a second...I firmly believe I've built this workflow to work hard and not smart.

I've attached the store list file. I don't know why I thought it was embedded in the workflow - my apologies. The intended output is supposed to be simple: tell me how many people (US Pop) and how many households exist inside the trade area. A simple output with the store number, population inside the trade areas, and households inside the trade areas would suffice...something I thought would be simple has caused me to burn a ton of mental calories.

I really appreciate each of you for your help in my challenge. This community is truly amazing!

wmt locations.xlsx

bremere

Well - here's where I stand. After using @gabrielvilella example - I've made some modifications. I believe what I've done is created an input file at the block group level with US population and US households...then I applied the trade areas to create a groupings by state for the population and households. My problem is I don't trust the block group population and household data. I'm beyond stumped...

I created the blockgroup file by using allocate input > then identified the states I wanted to pull blockgroups from, and output to file

I did, however, learn how to export workflows with data packaged together. Hopefully someone can point me in the right direction.

Attempt at spatial count.yxzp

BlockGroupCount Creator.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703