Geocoding

What is Geocoding?

Civis takes a table with address information and outputs geocodes as well as census information to help your organization create mapping visualizations. As a best practice, you should first use the CASS/NCOA job to standardize your addresses. This can greatly improve geocoding accuracy.

Creation

1. Go to "Tools" =>"Enhancements".
2. Choose "Geocode".

Options

• Database: ​ The Redshift cluster containing the data you would like to geocode.
• Credential: ​ The Redshift credential that has access to the data you would like to geocode.
• Input Schema: The schema where the target table resides.
• Input Table: ​ The table of address records you would like to geocode.
• Primary Key Columns: ​ The primary and foreign keys on the source table.
• WHERE clause: ​ If you’d like to limit which records are geocoded, do so here.
• Output Schema: ​ The schema you would like the job output to be written to.
• Output Table: ​ The name of the table the job will create.
• If Table Exists: What the job should do if the output schema.table already exists (e.g. fail, append, wipe, or drop).
• Output parsed address:​  This will take the address Civis is geocoding and parse it out into individual columns based on data type. See “Output Parsed Address Option” section below for specifics. Only applicable if those columns don’t already exist in your table; will fail the job if they do.

Required Fields

There are three sets of column types that this job can accept:

• full_address
• addr (or address, street_address, street_address1) + city + state (or state_code) + zip (or zip_code, zip5, zip9)
• latitude (or lat) and longitude (or lon)

Note that the columns must​ match the names listed above. If your table’s column names don’t match those listed, you can name them using the following SQL template:

alter table schema.table rename column state_abbreviation to state_code;

If you need to concatenate multiple columns, you can also use the following SQL template:

create table schema.new_table as (
    select id , address || ‘, ‘ || city || ‘, ‘ || state || ‘ ‘ || zip_code 
    as full_address
from schema.old_table );

Job Output

Once the job is complete, the output will be the multipart keys you selected when you set up the job, as well as the following columns:

• provider: PostGIS or Geocoder_CA
• civis_geocode_rating: ​ This field is calculated differently for each provider. Lower is better and a rating of 1 can be considered highly accurate for all providers.
  • Where provider = PostGIS: values of civis_geocode_rating indicate string distance from closest known location name. Ratings typically range from 0-100 but can be higher. A value of 0 indicates an exact match.
  • Where provider = Geocoder_CA: Similar to PostGIS ratings but the maximum value is 21.
• civis_latitude: Latitude
• civis_longitude: Longitude
• statefp20:​  State FIPS code used for the 2020 United States census. Use statefp with more recent Census Bureau data.
• countyfp20:​  County FIPS code used for the 2020 United States census. Use countyfp with more recent Census Bureau data.
• tractce20:  ​Census tract code used for the 2020 United States census. Use tractce with more recent Census Bureau data.
• blckgrpce20: ​ Block group number used for the 2020 United States census
• blockce20: Tabulation block number used for the 2020 United States census
• suffix1ce: Census block suffix 1
• zcta5ce20:​  5-digit Zip Code Tabulation Area code used for the 2020 United States census
• uace20: ​ Urban area code used for the 2020 United States census
• puma5ce20:​  Public use microdata area code used for the 2020 United States census
• statefp: State FIPS code 
• countyfp: County FIPS code
• blockce:​  Tabulation block number
• tractce: Census tract code 
• blkgrpce: Block group 
• cousubfp:  ​County subdivision FIPS code
• submcdfp:  ​Subminor civil division FIPS code
• estatefp: Estate FIPS code
• conctyfp:  ​Consolidated city FIPS code
• placefp:  ​Place FIPS code
• aiannhfp:​  American Indian/Alaska Native/Native Hawaiian area FIPS code
• aiannhce:​  American Indian/Alaska Native/Native Hawaiian area census code
• comptyp:​  Component type - American Indian/Alaska Native/Native Hawaiian area reservation/statistical area or off-reservation trust land Hawaiian home land indicator
• trsubfp:​  American Indian tribal subdivision FIPS code
• trsubce:​  American Indian tribal subdivision code
• anrcfp:  ​Alaska Native regional corporation FIPS code
• ttractce: ​ Tribal census tract code
• tblkgpce: ​ Tribal block group letter
• elsdlea:  ​Elementary school district local education agency code
• scsdlea:  ​Secondary school district local education agency code
• unsdlea:  ​Unified school district local education agency code
• sdadmlea: Administrative school district local education agency code
• cd119fp: the current congressional districts
• cd120fp: provisional and intra-congress changes (CA, MO, NC, OH, TX, UT) only
• cd_current_fp: currently aliased to cd119fp
  • cd_current_fp will always point to the current congressional districts based on the latest official Census TIGER data.
• cd_upcoming_fp: current congressional (cd119fp) districts including provisional changes (cd120fp).
  • cd_upcoming_fp will always reflect upcoming districts, incorporating any provisional intra-Congress redistricting changes where available, and falling back to current data otherwise.
• sldust:  ​State legislative district upper chamber code
• sldlst:​  State legislative district lower chamber code
• csafp:​  Combined statistical area code
• cbsafp: ​ Metropolitan statistical area/micropolitan statistical area code
• metdivfp:​  Metropolitan division code
• cnectafp:​  Combined New England city and town area
• nectafp​:  New England city and town area code
• nctadvfp: ​ New England city and town area division code

Note: countyfp and tractce differ from countyfp20 and tractce20 in Connecticut. See this Census Bureau page for more information. When joining to pre-2022 data, such as data from the 2020 United States census, use countyfp20 and tractce20. When joining to more recent data, such as from the latest American Community Survey, use countyfp and tractce.

Output Parsed Address Option

If you selected the Output parsed address​ option, there will also be these additional location columns:

• HouseNumber: The house number
• PreDirection: Compass abbreviation that appears before the main street name (e.g., the N in 123 N Main St)
• StreetName: The street name
• StreetType: The road designation, e.g. Street (ST), Avenue (AVE), or Boulevard (BLVD)
• PostDirection: Compass abbreviation that appears after the street name or street suffix (e.g., the NW in 456 Broad St NW)
• Apartment: Apartment/unit designation
• City: City
• State: State
• Postcode: Postal code

Please note that if any of these columns already exist in your table, the job will fail. This option is mainly useful to verify that the job is processing addresses correctly when your input table uses a single full_address column rather than pre-processed address columns.

How It Works

The Civis geocoder uses a PostGIS server to query TIGER/Line data. Note that these are not rooftop geocodes but are instead interpolated. TIGER/Line knows the first and last house number on a street, estimates where a given house number is within that range, and adds a few meters to the left/right of the road based on whether the house number is odd or even.

Joining to Census Bureau Data

The Geocoder tool makes it easier for you to join your person-level data to various statistics from the United States Census Bureau, which can help characterize the areas where people live. You can better understand, for example, how your organization’s people differ from the population at large.

One important note is that census data is aggregated rather than individual level, often at the level of census tracts (usually a few thousand people). As such, census data can help you understand the types of neighborhoods people live in (e.g., wealthy vs low-income) but not necessarily individual-level characteristics (e.g., a particular individual’s income).

Receiving and Joining Curated ACS Data from Civis

Civis provides a free dataset based on the Census Bureau’s 2020-2024 American Community Survey (ACS) to customers. 

Civis’s ACS profile dataset is a single table with one row per census tract, for ease of use. The columns are various aggregate statistics about the tract, including variables related to race/ethnicity, age, income and wealth, family status, etc. Some are raw values from the ACS (e.g., the population count in the tract) while some are derived values for ease of use (e.g., percentages instead of raw counts).

The dataset is keyed on three columns that uniquely identify a tract, with column names matching the output of the Geocoder tool: statefp, countyfp, and tractce. Note that the versions of these columns with the suffix “20” are older codes that are for use with older datasets such as the 2020 U.S. Census.

Below is an example SQL query joining the ACS profile data (census.acs_5yr_2024_tract_profile) to a hypothetical geocoder output for a donor dataset (donors.geocoded_sample). The query finds the average across the person-level geocoded_sample table of the percentages of tract population 25 years and over with a bachelor’s degree. The geocoded data is joined to the ACS profile table using the three FIPS code columns statefp, countyfp, and tractce.

SELECT
  AVG(c.pct_bachelors_or_higher)
FROM donors.geocoded_sample d
JOIN census.acs_5yr_2024_tract_profile c
  USING (statefp, countyfp, tractce)

You could also run a query such as the following to find a comparable number for the U.S. population.

SELECT
 SUM(pct_bachelors_or_higher * total_population) / SUM(total_population)
FROM census.acs_5yr_2024_tract_profile

If you are interested in using the ACS profile dataset, please reach out to Civis Support (support@civisanalytics.com).