Zip Code Tabulation Area

Dataset

Description

Zip Code Tabulation Areas (ZCTAs) are a representation of the USPS Zip Codes defined by the US Census Bureau. They are formulated by looking at all the Census Blocks that are associated with a Zip Code. Where a Census Block spans multiple Zip Codes, it is assigned to the ZCTA having the most addresses.

Examples

Zipcode Tabulation Area Demographics for a Property

The following example demonstrates how to establish the Zipcode Tabulation Area for a property and then extract the demographics for that Zipcode Tabulation Area

/*
 * Zipcode Tabulation Area demographics for a specific property.
 * Demonstrates how to match properties to Zipcode Tabulation Area data such as demographics
 */
SELECT 
	-- Property Details
	pa.[ATTOM ID],
	CONCAT(pa.SitusAddress,', ',pa.SitusCity,' ',pa.SitusState,' ',pa.SitusZip) AS Address,
    
    -- Zipcode Tablulation Area
    pbm.GeographyTypeKey,
    dt.Label,
    dt.AreaSquareMiles,
    
	-- Demographic data
	cd.Population,
	cd.Households
	
From adw.PropertyAddresses pa																-- Addresses
Left Join adw.PropertyBoundaryMatch pbm on pa.[ATTOM ID] = pbm.[ATTOM ID] 					-- Geocodes
Left Join adw.CommunityDemographics cd on pbm.GeographyKey = cd.GeographyKey And pbm.GeographyTypeKey = cd.GeographyTypeKey -- Demographics
Left Join adw.DimZipcodeTabulationArea dt on pbm.GeographyKey = dt.ZipCodeTabulationAreaKey  -- Zipcode tabulation area

Where
	pa.[ATTOM ID] = 26434730  And 		-- Specific Property
	pbm.GeographyTypeKey = 51			-- Zipcode Tabulation Area