Using the BIL Funds API

The Bipartisan Infrastructure Law (BIL) Funds API allows users to query by any field that is in the tables, such as program name or category. This Web API service, if invoked with a proper and valid field such as Category, should respond with information about funds for the field.

Jump to:

How to Access and Use the API
Example Queries
Overview of Data Sources
Appendix A: Additional API Information
Appendix B: Basic API Functionality
Appendix C: Data Dictionary
Appendix D: Manual Handling of Redirects

Objective

Our Objective is developing a comprehensive, interactive map that shows location and funding by program for the Infrastructure Investment and Jobs Act (IIJA), also known as the BIL. The map is refreshed daily from data that is pulled from various program sources into a central Redshift Funds Data Warehouse. The data is then prepared for the map by using Redshift optimized queries to merge the results of the various datasets. The resulting file is loaded into an S3 bucket to source the map. The process is repeated daily to get the most up-to-date information.

How to Access and Use the API

The API is publicly deployed to https://data.epa.gov/bilfunds/query

To access and use the API, ensure the user’s PC/laptop has an HTTP client available. A GraphQL API client such as Postman or Thunder Client may be easier to use, but any HTTP client, such as cURL, should be able to make a request.

Note: If using the Postman client, it is helpful to install the Postman Agent to allow downloading larger datasets.

How to Access the BIL Funds API

Example Queries

Two example queries are shown below.

Example 1:
The Example 1 query below calls the bil_funds_omb table in the public schema. It will return results for all the columns from the bil_funds_omb table and will only return the first 10 records.

query query {
public__bil_funds_omb (limit: 10) {
__all_columns__
}
}

The screenshot below shows the Example 1 query and its results in Postman:

Here is this API query in a cURL command:

Example 2:
The Example 2 query below is run against the bil_funds_omb table, searching for grants that have are from Wisconsin, have funds received greater than $1,000,000, and have a source of Superfund.

query query { public__bil_funds_omb (where: {state: {equals: "WI"}, funds_received_from_iija: {greaterThan: 1000000, cast: "float"}, source_of_data: {equals: "Superfund"}}) { __all_columns__ } }

Below is a cURL call to make the same request:

curl --location "https://data.epa.gov/bilfunds/query" ^ --header "Content-Type: application/json" ^ --data "{\"query\":\"query query {\r\n public__bil_funds_omb (where: {state: {equals: \\\"WI\\\"}, funds_received_from_iija: {greaterThan: 1000000, cast: \\\"float\\\"}, source_of_data: {equals: \\\"Superfund\\\"}}) {\r\n __all_columns__\r\n }\r\n}\",\"variables\":{}}"

Output Formats

Output can be in XML, CSV, Excel, JSON, HTML, PDF, or a format more closely aligned to GraphQL standards.

To get output in a XML format, send a request to https://data.epa.gov/bilfunds/query/xml
To get output in a CSV format, send a request to https://data.epa.gov/bilfunds/query/csv
To get output in an Excel format, send a request to https://data.epa.gov/bilfunds/query/excel
To get output in a JSON format, send a request to https://data.epa.gov/bilfunds/query/json
To get output in a HTML format, send a request to https://data.epa.gov/bilfunds/query/html
To get output in a PDF format, send a request to https://data.epa.gov/bilfunds/query/pdf
To get the data in a format that more closely aligns to GraphQL standards, send a request to https://data.epa.gov/bilfunds/query/graphql

Alternative Endpoint

An additional alternative endpoint is https://data.epa.gov/bilfunds/gateway-query. This endpoint is ideal when running a query from an application in an AWS VPC or for manually handling a redirect to the results file. The constraint of this endpoint is that a query must be completed within 29 seconds or it will fail.

Overview of Data Source Tables

The following table is updated daily with data for the API: public.bil_funds_omb

The data sources as of November 20, 2023 are:

Next Generation Grants System (NGGS)
Superfund Enterprise Management System (SEMS)
Datamart
Clean School Bus
USAspending
Great Lakes Restoration Initiative (GLRI)
Long Island Sound Program
Chesapeake Bay
Green Streets, Green Jobs, Green Towns (G3) Chesapeake Bay
National Estuary Program (NEP)
Superfund
Puget Sound
Geospatial - latitude/longitude
Geographic Initiative Area (GIA) layers – states, counties, school districts, NEP
Compass data for Superfund

Appendix A. Additional API Information

Who are the BIL Funds API users?

The API is available to the public.

What is basic API functionality?

Refer to Appendix B and DMAP API PDF.

What are some examples?

Refer to Example Queries section of this document.

Appendix B. Basic API Functionality

The attached DMAP API PDF covers topics that include quickstart, schema and table, selecting fields, filtering, dates, joins, subqueries, pagination, ordering results, grouping results, variables, multiple queries, returned data.

DMAP API Quickstart Guide (pdf) (569.89 KB)

Appendix C. Data Dictionary

Below are the BIL Funds API column names, with title and description. All fields are text. The fields noted as “(not used)” are in the template distributed by EPA’s Office of the Chief Financial Officer (OCFO) but are not being used currently in the map.

Column Name	Title	Description
award_or_rebate_date	Award Or Rebate Date	Date of Award (for award or rebate program)
cfda_code	CFDA Code	Catalog of Federal Domestic Assistance number. The CFDA provides a full listing of all Federal assistance programs available and is mandated by the Federal Program Information Act (Public Law 95-220 as amended).
federal_award_identification_number_fain	Federal Award Identification Number Fain	This is taken directly from grantApplication.xml's FederalAwardIdentifier field of SF424 section.(same as grant_family)
funds_received_from_iija	Funds Received From Iija	Funds received from Infrastructure Investment and Jobs Act (not used)
grant_family	Grant Family	The first 8 bytes of the grant number. The initial award and all associated subsequent amendments are considered the grant family.
grant_number	Grant Number	The primary identifier for a grant action in IGMS. The required format is 8 bytes, a dash, and an amendment designator. If this action is the first award of a grant, the amendment designator will be 0. An amendment action will have an amendment designator other than 0, ranging from 1 to 9, upper case A through Z, then lower case a through z.
grant_status	Grant Status	Identifies the current workflow state of a document. It may be an active workflow task such as 'Publish Opportunity' or indicate completion with 'Funding Opportunity Completed'. It may be an active workflow task such as 'Draft CN', 'FCO Signature' or indicate completion with 'Commitment Notice Completed'.
iija_project_id	Iija Project Id	Infrastructure Investment and Jobs Act ID (not used)
award_region	Award Region	The EPA Region awarding the grant. This is the Region of the Grants Office that will award the grant. This selection governs the names that will be displayed in the Grants Specialist and Grant Coordinator pick lists.
recipient_name	Recipient Name	Name of the Applicant organization.
reporting_cycle	Reporting Cycle	Reporting cycle (not used)
pop_address1	Pop Address1	Street address1 of a place of performance.
pop_address2	Pop Address2	Street address2 of a place of performance.
pop_census_block_group_2020	Pop Census Block Group 2020	Census block group (2020) of a place of performance.
pop_city	Pop City	City of a place of performance.
pop_congressional_district	Pop Congressional District	Congressional district of a place of performance.
pop_county	Pop County	County of a place of performance.
pop_feature_id	Pop Feature Id	GIS Data layer ID for Esri map.
pop_feature_name	Pop Feature Name	GIS Data layer Name for Esri map.
pop_geo_method	Pop Geo Method	A known geography sub type for tribes, Geo Init., Watersheds, etc.
pop_latitude	Pop Latitude	Latitude of a place of performance.
pop_longitude	Pop Longitude	Longitude of a place of performance.
pop_name	Pop Name	Name given by user for a place of performance.
pop_primary_ind	Pop Primary Ind	Options: True, False for Primary Place of work performed.
pop_search_type	Pop Search Type	A known geography type. (Example: state, nation, city, etc.)
pop_state	Pop State	State of a place of performance.
pop_status	Pop Status	Status of a place of performance. (Example: Active/Deleted)
pop_zip	Pop Zip	Zipcode of a place of performance.
program_code	Program Code	Identifies a specific EPA Program. The Program Code associates each grant family with an EPA Program.
project_id	Project Id	Unique project ID (not used)
applicant_type	Applicant Type	Information taken directly from grantApplication.xml's ApplicantTypeCode1 field of SF424 section.
county	County	The County associated with the Recipient's address.
state	State	The Recipient Organization's State.
zip_code	Zip Code	The Recipient Organization's Zip Code.
project_title	Project Title	The title of this project as provided by the applicant in their Grants.gov application.
proj_beg	Proj Beg	The start date of the grant's Project Period.
proj_end	Proj End	Identifies the end date of the grant's Project Period. Equivalent to noneditable grants.gov field: PROJECT_END_DATE.
project_desc	Project Desc	The project description of the grant family selected for the programmatic activity report.
contract_program_name	Contract Program Name	Non-grant program name (not used)
disadvantaged_community	Disadvantaged Community	Whether the program targets disadvantaged communities (not used)
total_award_planned	Total Award Planned	Total award planned (not used)
tribal_recipient	Tribal Recipient	Tribal recipient name (not used)
Site_ID	Site Id	From COMPASS, denotes site specific info for an award location.
website_URL	Website Url	URL containing more detailed info about the specific funded activity
obligation_amount	Obligation Amount	Obligation amount from COMPASS (not used)
award_end_date	Award End Date	Award end date (not used)
Strategic_Goal_Met	Strategic Goal Met	Identifies the primary strategic goal of the funding: Climate, EJ, Compliance, Clean Air, Clean Water, Communities, Chemical Safety
Category	Category	Identifies a specific benefit from this list: Clean Air, Clean Water, Healthy Communities, Clean Land, Cross-cutting Environmental
ef_federal_2	Ef Federal 2	Award amount from EPA Data Mart
available_funding_amt	Available Funding Amt	Available funding amount (not used)
RPIO	RPIO	Region or Office

Appendix D. Manual Handling of Redirects (e.g., Salesforce Apex Access)

The API does redirects to return the data. After receiving a response from the API, check the HTTP status code. If it is a 302, there will be a HTTP Header called “Location” that will contain the address to request to retrieve data.

The basic flow of steps is as follows:

Make a request to the API.
1. If the request is cached, you will be redirected to step 3 to get the cached result file, otherwise the query will be processed in step 2.
If the request is not cached, you will be redirected to an address that will perform the processing of the query. You will get a redirect response that will go to the results file (step 3).
Get the results file.

If you would prefer a simpler process, please see the Alternative Endpoint section of this document on the alternate “gateway-query” endpoint, which will always go directly from step 1 to step 3.

The API will always return results via cached files.