Keareys

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

--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

--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

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

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

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

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

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

# 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)