Difference between revisions of "Census Integration"

From SmartWiki
Jump to: navigation, search
(→‎Returning Census Data)
(updating categories)
 
(19 intermediate revisions by 3 users not shown)
Line 3: Line 3:
 
SmartSimple integrates with the U.S. Census online database to provide our clients with the ability to access the most up-to-date geographically-relevant data pertaining to their clients.
 
SmartSimple integrates with the U.S. Census online database to provide our clients with the ability to access the most up-to-date geographically-relevant data pertaining to their clients.
  
−
Census data is provided at three different levels: ''Tract'', ''County'' and ''State''. Census ''Tract''s are the lowest level of geographic region for which census data is aggregated.
+
Census data is provided at three different levels: ''Tract'', ''County'' and ''State''. Census ''Tract''s are the lowest level of geographic region for which census data can be returned.
  
 
In the example below, a {{WPV}} has been configured to display all three levels of census data on an organization [[profile]].
 
In the example below, a {{WPV}} has been configured to display all three levels of census data on an organization [[profile]].
Line 37: Line 37:
 
In the above example, the ''census block number'' for Abbeville Womens Equality's location is returned using the following [[variable]]:  
 
In the above example, the ''census block number'' for Abbeville Womens Equality's location is returned using the following [[variable]]:  
  
−
:<font size="3">'''@FFD(FIPS;;@address@ @city@ @state@)@'''</font>
+
:<font size="3">'''@ssGet(FFD,FIPS;;@address@ @city@ @state@)@'''</font>
 
<pre style="white-space: pre-wrap;  white-space: -moz-pre-wrap;  white-space: -pre-wrap;  white-space: -o-pre-wrap;  word-wrap: break-word;">FFD stands for "Federal Financial Data" and FIPS stands for Federal Information Processing Standards.</pre>
 
<pre style="white-space: pre-wrap;  white-space: -moz-pre-wrap;  white-space: -pre-wrap;  white-space: -o-pre-wrap;  word-wrap: break-word;">FFD stands for "Federal Financial Data" and FIPS stands for Federal Information Processing Standards.</pre>
  
 
On a {{UTA}} record, the ''census block number'' can be returned for an associated Organization by using:
 
On a {{UTA}} record, the ''census block number'' can be returned for an associated Organization by using:
  
−
:<font size="3">'''@FFD(FIPS;;@client.address@ @client.city@ @client.state@)@'''</font>
+
:<font size="3">'''@ssGet(FFD,FIPS;;@client.address@ @client.city@ @client.state@)@'''</font>
 +
 
 +
<pre style="white-space: pre-wrap;  white-space: -moz-pre-wrap;  white-space: -pre-wrap;  white-space: -o-pre-wrap;  word-wrap: break-word;">Note: The U.S. Federal Communications Commission writes:
 +
"Form 477 requires broadband providers to submit data on a census-tract basis. For the filing of data as of December 31, 2011, or later dates, the Form 477 electronic filing system will recognize as valid the combinations of County and Census tract codes from the Year 2010 Census."
 +
- from http://transition.fcc.gov/form477/censustracts.html
 +
Returning the FIPS code makes it easy for SmartSimple's clients to submit forms electronically to the FCC.</pre>
  
 
==Returning Census Data==
 
==Returning Census Data==
Line 50: Line 55:
 
===Census Data Variable Syntax===
 
===Census Data Variable Syntax===
  
−
:<font size="3">'''@FFD(Profile;;''year'';;''Address or FIPS code'';;''level'';;<nowiki>[[</nowiki>''template''<nowiki>]]</nowiki>)@'''</font>
+
:<font size="3">'''@ssGet(FFD,Profile;;''year'';;''Address or FIPS code'';;''level'';;<nowiki>[[</nowiki>''template''<nowiki>]]</nowiki>)@'''</font>
 
'''Where:'''
 
'''Where:'''
−
:* ''Profile''  
+
:* ''Profile'' is a constant.
−
:* ''year'' is the census year from which {need to enter allowable year values}
+
:* ''year'' is the census year from which the data is returned. Accepts '''2012''', '''2011''', and '''2010'''
−
:* ''Address or FIPS code''
+
:* ''Address or FIPS code'' accepts
−
:* ''level'' is one of  
+
::* a variable reference to the address, such as ''@address@ @city@ @state@''
 +
::* the 15-digit ''census block number''
 +
:* ''level'' accepts three values, one of  
 
::* '''tract'''
 
::* '''tract'''
 
::* '''county''' or
 
::* '''county''' or
 
::* '''state'''
 
::* '''state'''
−
:* ''template'' Note that the template should be enclosed within a double set of square brackets (e.g., <nowiki>[[</nowiki>B01003_001E<nowiki>]]</nowiki>)
+
:* ''template'' is the type of census data to be returned. Over 45,000 different database fields can be reported on from the online census database. [[Census_Integration#List_of_Commonly-used_Templates|Examples]] are listed below. Note that the template should be enclosed within a double set of square brackets (e.g., <nowiki>[[</nowiki>B01003_001E<nowiki>]]</nowiki> will return the ''Total Population'')
−
You would then use the following template: for the tract level @FFD(Profile;;year;;Address or FIPS code;;tract;;template)
+
'''Example:'''
−
          for the county level @FFD(Profile;;year;;Address or FIPS code;;county;;template)
+
In the example at the top of the page, the Median Family Income for Louisiana in 2012 ($56,047.00) is returned using
−
          for the state level @FFD(Profile;;year;;Address or FIPS code;;state;;template)
+
 
−
                Example: @FFD(profile;;2012;;@address@ @city@ @state@;;county;; [[B01003_001E]])
+
:<font size="3">'''@ssGet(FFD,Profile;;2012;;@address@ @city@ @state@;;state;;<nowiki>[[</nowiki>B19113_001E<nowiki>]]</nowiki>)@'''</font>
  
 +
==Characteristics Available in the Census Templates==
  
−
Profile – constant, query name
+
A variety of datasets are available from the Census Bureau's templates. Using the [[List of census templates|templates]], you can return census data broken out by the following categories:
−
Year – integer value of year like 2012,2013.  Normally last year statistics are not available until second halve of the current year
+
*'''Demographic Characteristics'''
−
Address or FIPS code – can be full address or fips code like “@address@ @city@ @state@“ or “@FIPS@
+
**Sex
−
Template – html template with statistic variables like  “Earnings for Households:$[[B06002_001E]]”
+
**Age
−
Where B06002_001E is field name of Census data (please refer to the xlsx for complete list I sent earlier) ,2 square brackets are used to embed a fieldname
+
**Race
 +
**Hispanic Origin
 +
*'''Social Characteristics'''
 +
**Education
 +
**Marital Status
 +
**Fertility
 +
**Grandparent Caregivers
 +
**Veterans
 +
**Disability Status
 +
**Health Insurance
 +
**Place of Birth
 +
**Citizenship
 +
**Year of Entry
 +
**Language Spoken at Home
 +
**Ancestry / Tribal Affiliation
 +
*'''Economic Characteristics'''
 +
**Income
 +
**Benefits
 +
**Employment Status
 +
**Occupation
 +
**Industry
 +
**Commuting to Work
 +
**Place of Work
 +
**Housing Characteristics
 +
**Housing Tenure
 +
**Occupancy & Structure
 +
**Housing Value
 +
**Taxes & Insurance
 +
**Utilities
 +
**Mortgage/Monthly Rent
  
 
==List of Commonly-used Templates==
 
==List of Commonly-used Templates==
Line 99: Line 136:
 
|}
 
|}
  
−
A full list of the census data templates can be found here: {link to census templates}
+
A full list of the census data templates can be found here: [[List of census templates]]
−
 
 
−
 
 
  
−
+
==See Also==
−
http://www.ffiec.gov/Geocode/default.aspx
+
* [[SsGet]]
−
+
* [[US census graphs]]
  
 
[[Category:Integration]]
 
[[Category:Integration]]
 +
[[Category:External Data Source]]

Latest revision as of 13:17, 20 July 2017

Overview

SmartSimple integrates with the U.S. Census online database to provide our clients with the ability to access the most up-to-date geographically-relevant data pertaining to their clients.

Census data is provided at three different levels: Tract, County and State. Census Tracts are the lowest level of geographic region for which census data can be returned.

In the example below, a Web Page View has been configured to display all three levels of census data on an organization profile.

CensusProfileWPV.png

Census Data can be referenced anywhere in SmartSimple:

...on a list view:

CensusVariableInListView.png

...on a record:

CensusFieldsOnL1.png

...or anywhere else. Data from the U.S. census can also be used to drive branching logic on SmartSimple workflows, or perform other calculations.

Returning the Tract Code

SmartSimple's Census Integration works by deriving the census block number from your organization or contact's address details. The census block number is a 15-digit code that incorporates - reading from left to right - the location's two-digit State code, three-digit County code, and six-digit census tract code.

TractCode.png

In the above example:

  • 22 is the State code for Louisiana,
  • 113 is the County code for Vermilion Parish and
  • 951100 is the census tract code for the address of Abbeville Womens Equality.

FIPS Code Syntax

In the above example, the census block number for Abbeville Womens Equality's location is returned using the following variable:

@ssGet(FFD,FIPS;;@address@ @city@ @state@)@
FFD stands for "Federal Financial Data" and FIPS stands for Federal Information Processing Standards.

On a record, the census block number can be returned for an associated Organization by using:

@ssGet(FFD,FIPS;;@client.address@ @client.city@ @client.state@)@
Note: The U.S. Federal Communications Commission writes:
"Form 477 requires broadband providers to submit data on a census-tract basis. For the filing of data as of December 31, 2011, or later dates, the Form 477 electronic filing system will recognize as valid the combinations of County and Census tract codes from the Year 2010 Census."
- from http://transition.fcc.gov/form477/censustracts.html
Returning the FIPS code makes it easy for SmartSimple's clients to submit forms electronically to the FCC.

Returning Census Data

Detailed census data can be retrieved at three levels, the tract, county, and state.

Census Data Variable Syntax

@ssGet(FFD,Profile;;year;;Address or FIPS code;;level;;[[template]])@

Where:

  • Profile is a constant.
  • year is the census year from which the data is returned. Accepts 2012, 2011, and 2010
  • Address or FIPS code accepts
  • a variable reference to the address, such as @address@ @city@ @state@
  • the 15-digit census block number
  • level accepts three values, one of
  • tract
  • county or
  • state
  • template is the type of census data to be returned. Over 45,000 different database fields can be reported on from the online census database. Examples are listed below. Note that the template should be enclosed within a double set of square brackets (e.g., [[B01003_001E]] will return the Total Population)

Example: In the example at the top of the page, the Median Family Income for Louisiana in 2012 ($56,047.00) is returned using

@ssGet(FFD,Profile;;2012;;@address@ @city@ @state@;;state;;[[B19113_001E]])@

Characteristics Available in the Census Templates

A variety of datasets are available from the Census Bureau's templates. Using the templates, you can return census data broken out by the following categories:

  • Demographic Characteristics
    • Sex
    • Age
    • Race
    • Hispanic Origin
  • Social Characteristics
    • Education
    • Marital Status
    • Fertility
    • Grandparent Caregivers
    • Veterans
    • Disability Status
    • Health Insurance
    • Place of Birth
    • Citizenship
    • Year of Entry
    • Language Spoken at Home
    • Ancestry / Tribal Affiliation
  • Economic Characteristics
    • Income
    • Benefits
    • Employment Status
    • Occupation
    • Industry
    • Commuting to Work
    • Place of Work
    • Housing Characteristics
    • Housing Tenure
    • Occupancy & Structure
    • Housing Value
    • Taxes & Insurance
    • Utilities
    • Mortgage/Monthly Rent

List of Commonly-used Templates

The census data templates used in the illustration at the top of the page are as follows:

Template Name Description
B01003_001E Total Population
B05010_001E Ratio of Income to Poverty Level in the Past 12 Months of Children by Living Arrangements and Nativity of Parents
B06002_001E Median Age By Place of Birth In the United States
B17008_001E Aggregate Income Deficit (Dollars) of Unrelated Individuals by Sex
B19051_001E Earnings for Households
B19113_001E Median Family Income

A full list of the census data templates can be found here: List of census templates

See Also

Retrieved from ‘https://wiki.smartsimple.com/index.php?title=Census_Integration&oldid=30547’