Category Archives: APIGateway

API Gateway

County Monthly Workforce Trends

Goto ProximityOne  Keep up-to-date with current county monthly workforce patterns and trends using the CV APIGateway. Automatically updated with multi-sourced demographic-economic data, the APIGateway Integrated Profile (IP) provides monthly workforce updates from the Bureau of Labor Statistics (BLS) Census of Employment and Wages (place of work data) and Local Area Unemployment Statistics (LAU – place of residence data). This section is focused on data and analytical tools to examine county workforce trends and patterns using the Local Area Unemployment Statistics data.

A unique and important property of the LAU data is that they are the only source of semi-official data available on a national scale for each/all counties and larger places providing current and monthly data on unemployment and the unemployment rate. The next runner-up to these estimates are the ACS 5 year estimates that are a) for a five year period and b) at least 3 years old (to the mid-point of the estimate period) when they become available. These highly perishable and time-sensitive economic measures are important to understanding the relative and absolute strength or weakness of workforce conditions and the employment sector on a local and regional basis.

The table presented below is a section taken from the APIGateway IP for Santa Clara County, CA (San Jose-Cupertino metro). The monthly/annual LAU data shown in the table are available for each/all counties. The APIGateway IP shows these four workforce series from 2011 forward. It auto-updates monthly with only a two-month lag to the reference time period. View a series as a chart: click graphic; on new page click a link shown in the table. Optionally set a wider time interval.
Santa Clara County, CA Monthly Labor Force Characteristics

Using GIS Resources for Visual Pattern Analysis
Separate from the APIGateway IP, the 2008-2012 annual time series data have been integrated into a U.S. by county shapefile. The graphic shown below illustrates how these tools can be used to examine the change in employment conditions by county since the recession started. This view shows the change in the unemployment rate from 2008 to 2012. Counties with red coloring experienced/are experiencing/ a 3-percent or more increase in the unemployment rate in 2012 compared to 2008. Using this measure alone, it is easy to see the extent to which the economy in many areas is still struggling to compensate for the recession impact and exactly which county/regional areas are impacted and how much. While one county might be prospering, many adjacent or nearby counties are not. View zoom-in to Santa Clara county area.

Interactive Table
Click the graphic shown below to use this interactive table to examine this same set of annual workforce data 2008-2012. Viewing the interactive table, click ShowAll button below table and then FindInName with default value “Santa Clara” to view the 5-year series for the same scope of subject matter as shown above in monthly form. The interactive table provides a convenient structure to view, rank, compare counties using these measures.


Next Steps
The U.S. by county shapefile with workforce data shown in the table and used to develop maps shown in this section are available to ProximityOne User Group members. (join now, no cost). Use these resources to develop wide-ranging variations of this view and analyze tabular data on your own computer; integrate your own data; add other types of geography; zoom-in to specific metros/regions. Visit the CountyTrends Web section for more information on county demographic-economic data and analytics.

2013 Metros: Houston, TX

Goto ProximityOne  94% of the U.S. population live in metropolitan areas.  Metropolitan areas are comprised of one or more contiguous counties having a high degree of economic and social integration. This section is one in a continuing series of posts focused on a specific metropolitan area — this one on the Houston-The Woodlands-Sugar Land, TX MSA.   This section illustrates how relevant Decision-Making Information (DMI) resources can be brought together to examine patterns and change and develop insights.  The data, tools and methods can be applied to any metro. About metros.

Focus on Houston-The Woodlands-Sugar Land, TX MSA
A thumbnail … in 2012, the Houston-The Woodlands-Sugar Land, TX MSA had a per capita personal income (PCPI) of $51,004. This PCPI ranked 23rd in the United States and was 117 percent of the national average, $43,735. The 2012 PCPI reflected an increase of 4.5 percent from 2011. The 2011-2012 national change was 3.4 percent. In 2002 the PCPI of the Houston MSA was $34,696 and ranked 37th in the United States. The 2002-2012 compound annual growth rate of PCPI was 3.9 percent. The compound annual growth rate for the nation was 3.2 percent.  These data are based in part on the Regional Economic Information System (REIS).  More detail from REIS for the Houston metro at the end of this section.

Geography of the Houston MSA
The geography of the Houston-The Woodlands-Sugar Land, TX MSA is shown in the graphic below.  The green boundary shows the 2013 vintage metro, black boundary/hatch pattern shows the 2010 vintage boundary, counties labeled. San Jacinto County is no longer a part of the metro.


Changing Metro Structures Reflect Demographic Dynamics
Click here
to view a profile for the 2013 vintage Houston metro. Use this interactive table to view demographic attributes of these counties and rank/compare with other counties.

The Census 2010 population of the 2013 vintage metro is 5,920,416 (6th largest MSA) compared to the 2012 estimate of 6,177,035 (5th largest MSA). See interactive table to examine other metros in a similar manner.

Demographic-Economic Characteristics
View selected ACS 2012 demographic-economic characteristics for the Houston metro (2010 vintage) in this interactive table.  Examine this metro in context of peer metros; e.g., similarly sized metros.  In 2012, the Houston metro had a median household income of $55,910, percent high school graduates 81.1%, percent college graduates 29.6% and 16.4% in poverty.

Houston Demographic-Economic Profiles
Use the APIGateway to access detailed ACS 2012 demographic-economic profiles.  A partial view of the Houston 2010 metro DE-3 economic characteristics profile is shown below.  Install the no fee CV XE tools on your PC to view extended profiles for Houston or any metro. See U.S. ACS 2012 demographic-economic profiles.  Viewing graphic with gesture/zoom enabled device suggested.  

Houston 2010 vintage MSA Economic Characteristics

Houston Metro Gross Domestic Product
View selected Houston 2013 vintage metro Gross Domestic Product (GDP) patterns in this interactive table.  The Houston metro 2012 real per capita GDP is estimated to be $62,438 ($385,683M real GDP/6,177,035 population).

Examining Longer-Term Demographic Historical Change
— Use this interactive table to view, rank, compare Census 2000 and Census 2010 population for Census 2010 vintage metros (all metros).
— Use this interactive table to view, rank, compare 2013 vintage metros (all metros) — Census 2000, Census 2010, 2012 estimates population and related data.

Houston Metro by County Population Projections to 2060
The graphic presented below shows county population projections to 2060 for the 2013 vintage metro.  Use this interactive table to view similar projections for all counties.  The metro population is projected to increase to 2.8 million by 2030 and to 3.4 million by 2060 based based on current trends and model assumptions. Viewing graphic with gesture/zoom enabled device suggested.

Houston Metro Population Projections by County to 2060

Thematic Maps & Visual Analysis
The graphic below shows the 2013 vintage metro (bold boundary) counties labeled with county name and county per capita personal income (PCPI).  The legend shows the change in PCPI from 2008 to 2012.

The above graphic illustrates the power of using visual analysis tools (CV XE GIS).  These data are from the  Regional Economic Information System (REIS) introduced earlier in this section.  Use the links shown below to examine much more detail from REIS at the metro and county level.  A thematic pattern map could be developed for any one of these items.  The REIS data are annual time series starting in 1970 and continue to 2012.  Click a link to view a sample profile spreadsheet for Harris County, TX and the Houston MSA for 2011 and 2012.
• Personal income, per capita personal income, and population (CA1-3)
• Personal income summary (CA04)
• Personal income and earnings by industry (CA05, CA05N)
• Compensation of employees by industry (CA06, CA06N)
• Economic profiles (CA30)
• Gross flow of earnings (CA91)

Join us in an Upcoming Decision-Making Information Webinar
We will review topics and data used in this section in the upcoming webinar “Metropolitan Area Geographic-Demographic-Economic Characteristics & Trends” on January 9, 2014.  This is one of many topics covered in the DMI Webinars (see more).  Register here (one hour, no fee).

About Metropolitan Areas
By definition, metropolitan areas are comprised of one or more contiguous counties. Metropolitan areas are not single cities and typically include many cities. Metropolitan areas contain urban and rural areas and often have large expanses of rural territory. A business and demographic-economic synergy exists within each metro; metros often interact with adjacent metros. The demographic-economic makeup of metros vary widely and change often.

2013 vintage metropolitan areas include approximately 94 percent of the U.S. population — 85 percent in metropolitan statistical areas (MSAs) and 9 percent in micropolitan statistical areas (MISAs). Of 3,143 counties in the United States, 1,167 are in the 381 MSAs in the U.S. and 641 counties are in the 536 MISAs (1,335 counties are in non-metro areas).

Homeownership Patterns by Census Block

The homeownership rate peaked in America in 2004 at approximately 69.2 percent.  Homeownership is defined as the percent of occupied housing units (households) that are owner-occupied.  The homeownership rate in 2013 is roughly the same as in 1995. The gradual decline continues.

        Homeownership Rate 1970Q1-2013Q3 not seasonally adjusted; based on CPS/HVS

The focus of this section is on creating thematic pattern map views depicting homeownership by census block for the Washington, DC area.  This section builds on the previous post Mapping Demographic Patterns: Census Blocks.

The starting place is where Mapping Demographic Patterns: Census Blocks left off — the required software and GIS project are installed on the computer.  The next step is to start the CV XE GIS software, open the DC GIS project and set the intervals for the thematic pattern views.

To compute homeownership, the minimum required data items/fields are the number of owner-occupied housing units and the number of housing units. By examining the Census 2010 SF1 table shells (xls), these items are found in Table H4. Tenure — owner/renter occupancy of occupied units (line 9259 in the xls file).  Looking at the  SF1 technical documentation (pdf) matrix section (sequential page 483, numbered page 6-321), it is determined that the items field needed are (H0040002 + H0040003) — owner occupied housing units and H0040001 (occupied housing units).  These items are already loaded into the DC block shapefile dbf.

Intervals are defined for the map view with queries that set blue to blocks with homeownership rate of 65% or more, orange to blocks with rate 50%-65% and red to population blocks with a rate below 50%.  Zero population blocks are set to gray.  The initial view shown below tells the visual story that more of Washington, DC has a homeownership rate below the national average than above the national average — and how these homeownership rate patterns are distributed by block.

Homeownership Rate by Census Block — Washington, DC

Zoom-in to area east of U.S. Capitol complex
— transparency set to 60% enabling “see through” of color patterns
— mini profile of blue block at pointer
— h0040001: 79 occupied housing units and 54 owner occupied housing units (h0040002+h0040003)


Similar thematic maps showing patterns of homeownership rate by census block may be created for any area in the U.S.  Procedures to access and use these no cost resources for Census 2010 Summary File 1 census block applications are summarized in the the APIGateway Guide.

In a future post, homeownership rates will be reviewed for all states and metros, 2008 through 2012, using annual American Community Survey (ACS) data.

Using Census 2010 Summary File 1 with API Technology

The Census 2010 Summary File 1 (SF1) contains the most detailed Census 2010 summary statistic data available that is tabulated at the census block  level.  The SF1 data are, and will continue to be, an important data resource throughout the 2010s and beyond. The scope of subject matter includes cross-tabulations of age, sex, households, families, relationship to householder, housing units, detailed race and Hispanic or Latino origin groups, and group quarters.

Skip the downloads!  Using pre-API technology methods, you would download the very large state by state zip files and proceed through a number of steps to use the data.  An alternative would be to use the Census Bureau FactFinder, but use of this tool is infeasible for most types of dataset development operations.

As an example, the Texas Census 2010 SF1 — view these data here (not recommended) — is contained in a 815 MB downloadable zip file. The zip file expands into a set of 48 files comprised of a geographic segment and 47 comma-delimited (CSV) structured files.  The expanded 48 files require 8.5 GB of space, still in CSV structure.  Specialized software is then required to transform these data into usable structures/data. This is for reference/history, we can now skip over using that enormous and complex to use data.

Using API Technology
How things change!  Use of API technology to access these data is reviewed in this section.  By using API-based applications, you can avoid downloading the very large SF1. This section reviews how you can use the APIGateway to develop a census block dataset. Many statistical programs and all popular geographies are supported by this tool, but the focus here is on census block demographics from Census 2010 SF1.

The next series of steps illustrate how to develop the sample dataset shown in the graphic presented below.  The graphic shows a spreadsheet oriented view of rows corresponding to census blocks and columns comprised of a census block geoid (the geocode and structured shorthand by which the geographic area is referenced) followed by columns/fields of selected SF1 items.

Washington DC tract 004100 SF1 extract

Using the CV XE APIGateway with Census 2010 SF1
Follow these steps to run through the demonstration application.  You can then use the APIGateway tool to process on geography and subject matter items of interest to you.

1. Install CV XE GIS Software
Use the CV XE GIS installer to install the software on your Windows computer.  Take all default settings.  More information about CV XE GIS.

2. Get Washington, DC GIS Project Files
Expand to folder c:\cvxe\1.

3. Start CV XE.  With CV XE running, start APIGateway
Use File>APIGateway from main menu bar.  The APIGateway form appears as shown below.


The Batch Extraction operation involves two steps.
1 – Click main menu Settings>Batch Operation and specify settings.
2 – Close the Setting form. Click Tools>Batch Extraction to start processing.

The Batch Operations setting form is shown below.
APIGateway Batch Extract Settings

These settings will operate with no modification.  To use these settings,  close the form and click Tools>Batch Extraction to start processing.  The output file c:\cvxe\1\blk_sf1_2010_p003p004.dbf is created (Output Dataset) and contains the fields shown in the Field Names edit box.  The output file created will contain ALL records that meet the criteria of the Control File Query (substr(geoid,1,11)=’11001004100′) and processing blocks contained in the Control File (c:\cvxe\1\tl_2013_11_tabblock_dp.dbf).  The graphic at the top of this section provides a partial view of the file created.

You can use this version of the APIGateway to create your own datasets for any area of the U.S.  The Batch Operation in this version operates only with Census 2010 SF1 block level data.  The full version operates with many types of source data and supports wide ranging geographic levels — including ACS block group level data.

See the APIGateway Guide for more details regarding operations.

Using the Dataset Generated
There are at least two main ways the output dataset can be used.
1 – the block level data may be aggregated/analyzed in a tabular manner.
2 – the dbase version of the output dataset is structured in a manner that can be immediately joined with a shapefile for mapping and geospatial applications.

More about the Sample Dataset
The first row of the dataset shows selected data for census block 11-001-004100-1000.  P0030001 is the fieldname/shorthand for Census 2010 total population.  You can see the list describing these items using the table shells (xls) — see table P3 in the xls file. Field/item.column p0030002 is the White alone population.  Field name spelling is nitpicky; one character missed, incorrect or out of place can cause an error.  See sequential page 184 (numbered page 6-22 in the matrix section)in the SF1 technical documentation (pdf) to view the exact spelling of the field names and an alternative view of the table structure.

Washington DC tract 004100 SF1 extract

See related post regarding Mapping Census Blocks.  The next post on using data generated by the APIGateway will be November 19,2013.

Neighborhood Patterns of Economic Prosperity by Congressional District

While congressional districts are similar in that the total population of the district is roughly the same, the similarity often stops there.  The 2012 median household income among the 113th Congressional Districts ranged from $23,314 to $108,068.  The educational attainment among the 113th Congressional Districts ranged from 52% to 96.1% high school graduates. Use this interactive table to view/rank/compare districts based on selected 2012 demographic-economic measures.  Use interactive tables accessible on this page to rank/compare congressional districts based on a broader set of data. Access much more detailed 2012 demographic-economic data in the form of structured profiles using the CV XE APIGateway.  Compare one congressional district to others in a comparative analysis spreadsheet structure.

These measures tell us a great deal about the characteristics of the population and housing for the entire area covered by each district.  But how do patterns of economic prosperity, among other such measures, vary by neighborhood across any specific congressional district? The graphic presented below shows a thematic map of median household income by census tract.  New York Congressional District 12 is shown with the  bold black boundary.  See this larger view that shows the legend and pattern/color by median household income interval/range.

New York 12th Congressional District

New York 12th Congressional District

View this gallery section to see similar thematic maps for this and all New York 113th Congressional Districts.  These maps provide visually-based insights into demographic-economic patterns across each district.  Congresspeople, their staffs and stakeholders can develop a better understanding of diversity and needs within a district using GIS tools, relevant data and methods such as shown here.

Neighborhood Geography
Census tracts are an imperfect geography to provide a 100% equivalent to a neighborhood area.  But they are the closest geographic equivalent to a neighborhood which are available wall-to-wall across the U.S.  It might be argued that block group level geography would be better as block groups are subdivisions of census tracts.

Economic Prosperity Measures
Similarly, median household income (MHI) is an imperfect measure of economic prosperity.  But, if we were going to pick just one measure, MHI might be the best choice.  MHI and related measures of economic prosperity are updated annually at the census tract and block group levels by the American Community Survey (ACS) 5-year estimates.  Soon, the growing set of these data will evolve into a time-series and also enable analyzing these same geographies and MHI patterns as trends over time.

Visual Analysis of Each 113th Congressional District
The 113th Congressional Districts Analytical Gallery provides thematic map views of neighborhood patterns of economic prosperity for each/every congressional district.  Click on the link above and navigate to your districts of interest.  See how patterns across the district are the same or dissimilar — and how and where they differ.

An updated (one year more recent) median household income estimate by census tract and block group will be available in December 2013.  We will re-visit this topic in early 2014.