Keareys’s gists

Keareys / gist:75a4e7a4e1b7260b3099563378424684

Last active November 8, 2020 17:08

Census API Example Calls for Key Demographic Attributes used in Environmental Justice Work at MTC


	Primary Census API Documentation Page:
	http://www.census.gov/data/developers/data-sets.html

	ACS Summary File Documentation

	Other Useful Documentation Examples:
	http://www.opengeocode.org/tutorials/USCensusAPI.php
	https://www.socialexplorer.com/data/ACS2010/documentation/781dcba1-deed-47f9-a223-0cbc4e2b65b6

Keareys / EJ_2016_Databuilder.sql

Created December 14, 2016 20:06

Sal statement that builds the EJ Map series data tables.

	--Key Variables for EJ Data
	--Main COC Database Table: [Analysis].[ACS_2014_ALL_COC_DATA_TRACTS]
	--Map 1a CoCs DONE
	Go
	create view EJ_2016.Map1a as
	select
	GEOID,
	[COCFLAG_2017]
	From [Analysis].[ACS_2014_ALL_COC_DATA_TRACTS]
	Go

Keareys / EJ_2016_Correspondence.sql

Created December 14, 2016 23:59

	--drop view Analysis.Census2K_ACS2014_EJ_Select_Variables_Compare_Detail
	create view Analysis.Census2K_ACS2014_EJ_Select_Variables_Compare_Detail as
	/WHERE (C2K.TotalPopulation IS NOT NULL)/
	SELECT
	ACS.GEOID, ACS.county,

	ACS.TotalPopulation AS TotalPop_ACS2014,
	CASE WHEN rel.PART00 = 'W' THEN (ACS.TotalPopulation - C2k.TotalPopulation)
	WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0)
	THEN (ACS.TotalPopulation - C2k.TotalPopulation)

Keareys / EJ_2016_ACS_C2000_Pop_Check.sql

Created December 16, 2016 17:54

This query checks population totals for ACS 2014 and Census 2000.

	SELECT Count(geoid) as [Total Tracts], SUM(TotalPopulation) AS [Total Population]
	FROM [EJ_2016].[ACS_2014_EJ_Selected_Variables]

	--Has the correct number of tracts
	SELECT Count(geoid) as [Total Tracts], SUM([TotalPopulation]) AS [Total Population]
	FROM [EJ_2016].[CENSUS2000_EJ_SELECTEDVARIABLES]

	SELECT Count(geoid) as [Total Tracts],SUM([Total_pop]) AS [Total Population]
	FROM [EJ_2016].[CENSUS2000_EJ_SELECTEDVARIABLES]

Keareys / EJ 2014 to 2000 Compare.sql

Created December 16, 2016 22:22

Comparison script that looks at the change between selected variables ACS 2014 Dataset and the Census 2000 Dataset

	SELECT
	b.OBJECTID,
	a.GEOID,
	a.county,
	a.TotalPop_ACS2014,
	Case When a.TotalPop_ACS2014 - a.PopChange<0 Then 0 Else a.TotalPop_ACS2014 - a.PopChange END AS Pop_2000,
	CASE When (a.TotalPop_ACS2014 - a.PopChange)<0 Then a.TotalPop_ACS2014
	Else a.PopChange END as PopChange,
	CASE
	WHEN a.PopChange < 0 AND a.TotalPop_ACS2014 = 0 THEN -100

Keareys / ACS Census 2010 to Census 2000 Corespondence.sql

Created December 16, 2016 22:25

Correspondence between ACS 2014 and Census 2000

	SELECT
	ACS.GEOID, ACS.county,

	ACS.TotalPopulation AS TotalPop_ACS2014,
	CASE WHEN rel.PART00 = 'W' THEN (ACS.TotalPopulation - C2k.TotalPopulation)
	WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0)
	THEN (ACS.TotalPopulation - C2k.TotalPopulation)
	ELSE (ACS.TotalPopulation - Round((Rel.POPPCT00 / 100) * C2K.TotalPopulation, 0)) END AS PopChange,

	ACS.Total_White_Alone AS TotalWhitePop_ACS2014,

Keareys / Summary View by 2010 Geography.sql

Last active December 17, 2016 01:55

Census: Creates a summary view by 2010 geography. This query is needed because there a multiple tract parts for 2010 tracts within the correspondence table (2010 to 2000)

	SELECT
	GEOID,
	county,
	TotalPop_ACS2014,
	TotalWhitePop_ACS2014,
	SUM(White_Alone_PopChange) as WhitePopChange,
	TotalBlackPop_ACS2014,
	SUM(Black_Alone_PopChange) as BlackPopChange,
	Total_Hispanic_Latino_Pop_ACS2014,
	SUM(Hispanic_Latino_PopChange) as Hispanic_LatinoPopChange,

Keareys / ACS_2011_2015_Home_Value.sql

Last active February 8, 2017 02:39

ACS 2011 - 2015 Median Household Income to Home Value Ratio by County and Urban Areas. Uses ESRI 2016 FeatureClasses for Counties and Urban Areas as defined by the Census.

	Go
	create view Analysis.Income_to_HomeValue_Urban_Areas as
	SELECT
	Top 50000
	hv.OBJECTID,
	--hv.Id,
	hv.Id2,
	--hv.Geography,
	c.STATE_FIPS as StateFips,
	c.NAME as CountyName,

Keareys / Gmaps_API.r

Created January 25, 2018 19:55

Gmaps API Location Search for City Names in California

	# Example Script for GMAPS API Search
	library(anonymizer)
	library(readr)
	library(dplyr)
	library(stringr)
	library(RPostgreSQL)
	library(data.table)
	library(tidyr)
	library(chron)
	library(lettercase)

Kearey Smith Keareys