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.
The screenshot below shows the Example 1 query and its results in Postman:
Here is this API query in a cURL command:
curl --location "https://data.epa.gov/bilfunds/query" ^
--header "Content-Type: application/json" ^
--data "{\"query\":\"query query {\r\n public__bil_funds_omb (limit: 10) {\r\n __all_columns__\r\n }\r\n}\",\"variables\":{}}"
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)
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.
- 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.