Skip to content

Instantly share code, notes, and snippets.

View Keareys's full-sized avatar

Kearey Smith Keareys

View GitHub Profile
@Keareys
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)
@Keareys
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
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
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
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
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]
--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
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
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