Skip to content

Product: CPDB

Jump to bottom
Damon McCullough edited this page Jun 7, 2024 · 14 revisions

Capital Projects Database

The Capital Projects Database (CPDB), a data product produced by the New York City (NYC) Department of City Planning (DCP) Capital Planning division, captures key data points on potential, planned, and ongoing capital projects sponsored or managed by a capital agency in and around NYC.

Information reported in the Capital Commitment Plan published by the NYC Office of Management and Budget (OMB) three times per year is the foundation that CPDB is then built off of; therefore, only the capital projects that appear in the Capital Commitment Plan are reflected in CPDB. Other open data resources are also leveraged to map the capital projects.

CPDB supports the most comprehensive map of potential, planned, and ongoing capital projects taking place across NYC enabling Planners to better understand and communicate New York City’s capital project portfolio within and across particular agencies. This integrated but not exhaustive view provides a broad understanding of what projects are taking place within a certain area, and a starting point to discovering opportunities for strategic neighborhood planning.

Build outputs

Shapefiles have shorter column names and separate files for each geometry type.

Primary tables and files

cpdb_projects - .csv

Each row is a capital project.

cpdb_opendata_projects - .csv, .shp

Each row is a capital project.

This table has project geometries and additional columns.

Other tables tables and files

cpdb_adminbounds - .csv

Each row is an administrative boundary.

cpdb_budgets - .csv

Each row is a budget line for a project.

cpdb_commitments - .csv

Each row is a commitment for a project.

cpdb_dcpattributes - .shp

Each row is a capital project.

This table details the sources of geometries for each project.

cpdb_opendata_commitments - .csv

Each row is a commitment for a project.

cpdb_projects_combined - .csv

Each row is a capital project.

cpdb_projects_spending_byyear - .csv

Each row is a capital project .

This table is limited to projects which appear in CheckBookNYC data.

geospatial_check - .csv

Each row is all of the projects that are outisde of NYC.

This table has one row and the projects are in a single json value.

Source data

...

Data Dictionary

...

Details

Capital Commitment Plan phases are different than Capital Project funding phases.

  • Commitment Plan phases: Preliminary, Executive, Adopted
  • Project Funding phases: Allocations, Commitments, Liquidations

The Commitment Plan types are tied to the budgeting cycle. Allocations/commitments/liquidations can change for individual projects from plan to plan, however.

I. Overview
Dataset Name "Capital Projects Database"
Description The Capital Projects Database (CPDB) captures key data points on, including spatial data, potential, planned, and ongoing capital projects reported in the Capital Commitment Plan published by the NYC Office of Management and Budget (OMB)
Data format Webmap, Shapefile: Points & Polygons, GeoJSON: Points & Polygons, CSV
Projection WGS84
Date last updated March 2020
Capital Commitment Plan version January 2020

II. Limitations and Disclaimers

CPDB is only as good as the source data it extracts and aggregates; therefore, CPDB includes data on current and planned NYC capital projects reported in OMB's Capital Commitment Plan and it does not capture all historic, current, or future capital projects.

CPDB is not a project or financial management system. Data on project timeline may be incorrect and budgetary information may be incomplete since all monies committed to or spent on a project may not be captured. CPDB does capture planned commitments allocated to projects that may never come to fruition; these instances are most frequent for projects funded by discretionary funding sources, such as council member funding.

Currently, CPDB is the most comprehensive spatial data resource of current and planned City capital projects, but the spatial data are not 100% reliable, accurate, or exhaustive.

  • The map of capital projects is incomplete because many capital projects could not be spatially referenced due to vague descriptions not including the name of a site.

  • Some projects are mapped incorrectly. Some projects are geocoded to agency's headquarters rather than the site where the capital investment and construction is taking place. Other projects are mapped to the wrong site as a result of two places having the same name or similar names. Unfortunately, these records cannot be systematically verified and corrected.

Through user feedback we look to resolve these limitation over time.

For more detailed information on a specific capital project please reach out to the respective managing or sponsor agency.

As a result of these limitations and inconsistencies, CPDB should not be used for quantitative analyses. CPDB should be used for planning coordination and information purposes only. If CPDB is used for any spatial analyses it is crucial to recognize that not all of the projects are spatially referenced, projects may be incorrectly spatially referenced, one project can span across many locations, and this database is not authoritatively comprehensive.

If you have any questions about or comments on these data please contact the NYC DCP Capital Planning team at [email protected].

Business owners

Business owners

The Capital Planning team at the NYC Department of City Planning is the business owner of the Capital Projects Database (CPDB).

As the business owners they are responsible for:

  • Participating in the QA / QC process by reviewing the standard analyses and confirming the reports look as expected
  • Determining what subset of projects w/o geometries should be manually reviewed to see if any projects are 'mappable.' Out of this subset any 'mappable' projects will have geometries created for them.
  • Leading decisions related to business requirements, which may in turn result in a change to or expansions of CPDB

Data Dictionary

Projects

?> The source data used to build CPDB determines what attributes are reported by the final product. Using FMS data from FISA provides the greatest level of detail and accuracy, and outputs the most attributes. Attributes marked with a * are exclusive to FISA source data.
The tables that compose CPDB are all related by the FMS ID.

Projects(cpdb_projects): Reports information from the Capital Commitment Plan at the project level. A project is a unique FMS ID.

maprojid

  • Longform Name: FMS ID
  • Description: Unique identifier that defines a discrete project. The maprojid is a concatenation of magency and projectid and it is the primary key.

magency

  • Longform Name: Managing Agency
  • Description: Three digit code of the distinct City agency managing the project.

projectid

  • Longform Name: Project ID
  • Description: Alphanumeric code created by the sponsor agency that identifies a distinct project. A Project ID must be unique within a managing agency.

description

  • Longform Name: Description
  • Description: Short description of the project as described by the sponsor agency. If one FMS ID had many descriptions the longest description is reported by CPDB.

ccnonexempt

  • Longform Name: $ City Cost (Non-Exempt) *
  • Description: Sum of City Cost (Non-Exempt) funding across all commitments associated with the project.

ccexempt

  • Longform Name: $ City Cost (Exempt) *
  • Description: funding across all commitments associated with the project.

citycost

  • Longform Name: $ City Cost
  • Description: Sum of City funding across all commitments associated with the project. (If FISA is the source data $ City Cost is the sum of $ City Cost (Exempt) and $ City Cost (Non-Exempt)).

nccstate

  • Longform Name: $ Non-City Cost State *
  • Description: Sum of State funding across all commitments associated with the project.

nccfederal

  • Longform Name: $ Non-City Cost Federal *
  • Description: Sum of Federal funding across all commitments associated with the project.

nccother

  • Longform Name: $ Non-City Cost Other *
  • Description: Sum of Other funding across all commitments associated with the project.

noncitycost

  • Longform Name: $ Non-City Cost
  • Description: Sum of Non-City funding across all commitments associated with the project. (If FISA is the source data $ Non-City Cost is the sum of $ Non-City Cost State, $ Non-City Cost Federal, and $ Non-City Cost Other).

nccother

  • Longform Name: $ Non-City Cost Other *
  • Description:

totalcost

  • Longform Name: $ Total Planned Commitments
  • Description: Sum of $ City Cost and $ Non-City Cost, which reports the total planned commitments for the project allocated in the Capital Commitment Plan.

magencyacro

  • Longform Name: Managing Agency Acronym
  • Description: Common acronym of the city agency managing the project. This value is derived from the three digit managing agency code.

magencyname

  • Longform Name: Managing Agency Name
  • Description: Common name for the city agency mananging the project. This value is derived from the three digit managing agency code.

ccpversion

  • Longform Name: Capital Commitment Plan version
  • Description: Reports the version of the Capital Commitment Plan which the record is based on.

Budgets

Budgets (cpdb_budgets): Reports information from the Capital Commitment Plan for projects at the budget level. One project can be funded by many budgets.

maprojid

  • Longform Name: FMS ID
  • Description: Unique identifier that defines a discrete project. The maprojid is a concatenation of magency and projectid and it is the foreign key.

magency

  • Longform Name: Managing Agency
  • Description: Three digit code of the distinct City agency managing the project.

projectid

  • Longform Name: Project ID
  • Description: Alphanumeric code created by the sponsor agency that identifies a distinct project. A Project ID must be unique within a managing agency.

budgetline

  • Longform Name: Budget Line
  • Description: Unique identifier of the budget used to fund the project.

projecttype

  • Longform Name: Project type
  • Description: Short description of the type of project based on the budget funding the project.

sagencyacro

  • Longform Name: Sponsor Agency Acronym
  • Description: Common acronym of the city agency sponsoring the project. This value is derived from the budget line.

sagencyname

  • Longform Name: Sponsor Agency Name
  • Description: Common name for the city agency sponsoring the project. This value is derived from the budget line.

ccnonexempt

  • Longform Name: $ City Cost (Non-Exempt) *
  • Description: Sum of City Cost (Non-Exempt) funding across all commitments associated with the project drawn from the specified budget line.

ccexempt

  • Longform Name: $ City Cost (Exempt) *
  • Description: Sum of City Cost (Exempt) funding across all commitments associated with the project drawn from the specified budget line.

citycost

  • Longform Name: $ City Cost
  • Description: Sum of City funding across all commitments associated with the project drawn from the specified budget line. (If FISA is the source data $ City Cost is the sum of $ City Cost (Exempt) and $ City Cost (Non-Exempt)).

nccstate

  • Longform Name: $ Non-City Cost State *
  • Description: Sum of State funding across all commitments associated with the project drawn from the specified budget line.

nccfederal

  • Longform Name: $ Non-City Cost Federal *
  • Description: Sum of Federal funding across all commitments associated with the project drawn from the specified budget line.

nccother

  • Longform Name: $ Non-City Cost Other *
  • Description: Sum of Other funding across all commitments associated with the project drawn from the specified budget line.

noncitycost

  • Longform Name: $ Non-City Cost
  • Description: Sum of Non-City funding across all commitments associated with the project drawn from the specified budget line. (If FISA is the source data $ Non-City Cost is the sum of $ Non-City Cost State, $ Non-City Cost Federal, and $ Non-City Cost Other).

totalcost

  • Longform Name: $ Total Planned Commitments
  • Description: Sum of $ City Cost and $ Non-City Cost, which reports the total planned commitments drawn from the specified budget line for the project allocated in the Capital Commitment Plan.

ccpversion

  • Longform Name: Capital Commitment Plan version
  • Description: Reports the version of the Capital Commitment Plan which the record is based on.

Commitments

Commitments (cpdb_commitments): Reports information from the Capital Commitment Plan at the commitment level. Many commitments can fund one project.

maprojid

  • Longform Name: FMS ID
  • Description: Unique identifier that defines a discrete project. The maprojid is a concatenation of magency and projectid and it is the foreign key.

magency

  • Longform Name: Managing Agency
  • Description: Three digit code of the distinct City agency managing the project.

projectid

  • Longform Name: Project ID
  • Description: Alphanumeric code created by the sponsor agency that identifies a distinct project. A Project ID must be unique within a managing agency.

budgetline

  • Longform Name: Budget Line
  • Description: Unique identifier of the budget used to fund the project.

plancommdate

  • Longform Name: Planned Commit Date
  • Description: Month and year of when the planned commitment is projected to be committed.

commitmentdescription

  • Longform Name: Commitment Description
  • Description: Short description of what the planned commtiment will specifically fund. Not all commitments have descriptions.

commitmentcode

  • Longform Name: Commitment Code
  • Description: Four letter or digit code indicating what part of the project the associated funding is for, such as design, construction, or contingency.

typc

  • Longform Name: Ten Year Plan Categroy *
  • Description: Three leter code indicating what ten year plan category the planned commitment supports.

typcname

  • Longform Name: Ten Year Plan Categroy Name *
  • Description: Ten year plan category the planned commitment supports.

ccnonexempt

  • Longform Name: $ City Cost (Non-Exempt) *
  • Description: Amount City Cost (Non-Exempt) funding associated with the commitment.

ccexempt

  • Longform Name: $ City Cost (Exempt) *
  • Description: Amount of City Cost (Exempt) funding associated with the commitment.

citycost

  • Longform Name: $ City Cost
  • Description: Amount of City funding associated with the commitment. (If FISA is the source data $ City Cost is the sum of $ City Cost (Exempt) and $ City Cost (Non-Exempt)).

nccstate

  • Longform Name: $ Non-City Cost State *
  • Description: Amount of State funding associated with the commitment.

nccfederal

  • Longform Name: $ Non-City Cost Federal *
  • Description: Amount of Federal funding associated with the commitment.

nccother

  • Longform Name: $ Non-City Cost Other *
  • Description: Amount of Other funding associated with the commitment.

noncitycost

  • Longform Name: $ Non-City Cost
  • Description: Amount of Non-City funding associated with the commitment. (If FISA is the source data $ Non-City Cost is the sum of $ Non-City Cost State, $ Non-City Cost Federal, and $ Non-City Cost Other).

totalcost

  • Longform Name: $ Total Planned Commitments
  • Description: Sum of $ City Cost and $ Non-City Cost, which reports the total planned commitments associated with the commitment for the project allocated in the Capital Commitment Plan.

ccpversion

  • Longform Name: Capital Commitment Plan version
  • Description: Reports the version of the Capital Commitment Plan which the record is based on.

DCP Attributes

DCP Attributes (cpdb_dcpattributes): Reports information generated by DPC, including spatial data, at the project level. These data are published as two tables _pts and _poly where data are separated based on the geometry type.

maprojid

  • Longform Name: FMS ID
  • Description: Unique identifier that defines a discrete project. The maprojid is a concatenation of magency and projectid and it is the primary key.

magency

  • Longform Name: Managing Agency
  • Description: Three digit code of the distinct City agency managing the project.

projectid

  • Longform Name: Project ID
  • Description: Alphanumeric code created by the sponsor agency that identifies a distinct project. A Project ID must be unique within a managing agency.

magencyacro

  • Longform Name: Managing Agency Acronym
  • Description: Common acronym of the city agency managing the project. This value is derived from the three digit managing agency code.

description

  • Longform Name: Description
  • Description: Short description of the project as described by the sponsor agency. If one FMS ID had many descriptions the longest description is reported by CPDB.

typecategory

  • Longform Name: Category
  • Description: Classification given by DCP based on keywords found in the short description describing if a projects is Fixed Asset, Lump Sum, or ITT, Vehicles, and Equipment.

geomsource

  • Longform Name: Geometry Source
  • Description: Description of where the geometry associated with the project came from.

dataname

  • Longform Name: Data Name
  • Description: Name of the dataset where the geometry associated with the project came from.

datasource

  • Longform Name: Data Source
  • Description: Acronym of the agency that supplied the dataset from which the geometry associated with the project came from.

datadate

  • Longform Name: Data Date
  • Description: Date the geometry was updated.

ccpversion

  • Longform Name: Capital Commitment Plan version
  • Description: Reports the version of the Capital Commitment Plan which the record is associated with.

geom

  • Longform Name: Geometry
  • Description: Spatial data information.

Project Combined

Project Combined (cpdb_projects_combined): Master flat file used to power the Capital Projects Explorer that aggregates and reports data at the project level.

maprojid

  • Longform Name: FMS ID
  • Description: Unique identifier that defines a discrete project. The maprojid is a concatenation of magency and projectid and it is the primary key.

magency

  • Longform Name: Managing Agency
  • Description: Three digit code of the distinct City agency managing the project.

projectid

  • Longform Name: Project ID
  • Description: Alphanumeric code created by the sponsor agency that identifies a distinct project. A Project ID must be unique within a managing agency.

magencyacro

  • Longform Name: Managing Agency Acronym
  • Description: Common acronym of the city agency managing the project. This value is derived from the three digit managing agency code.

description

  • Longform Name: Description
  • Description: Short description of the project as described by the sponsor agency. If one FMS ID had many descriptions the longest description is reported by CPDB.

typecategory

  • Longform Name: Category
  • Description: Classification given by DCP based on keywords found in the short description describing if a projects is Fixed Asset, Lump Sum, or ITT, Vehicles, and Equipment.

geomsource

  • Longform Name: Geometry Source
  • Description: Description of where the geometry associated with the project came from.

dataname

  • Longform Name: Data Name
  • Description: Name of the dataset where the geometry associated with the project came from.

datasource

  • Longform Name: Data Source
  • Description: Acronym of the agency that supplied the dataset from which the geometry associated with the project came from.

datadate

  • Longform Name: Data Date
  • Description: Date the geometry was updated.

ccpversion

  • Longform Name: Capital Commitment Plan version
  • Description: Reports the version of the Capital Commitment Plan which the record is associated with.

geom

  • Longform Name: Geometry
  • Description: Spatial data information.

Dataloading instructions

See the 01_dataloading.sh script and the db-data-library repo for more details.

Capital Commitment Plan

  1. When OMB releases the latest Capital Commitment Plan, email FISA requesting that they upload the latest Capital Commitment Plan data from FISA to their FTP server
  2. On a computer connected to DCP's intranet, use the program WinSCP to connect to FISA's FTP server (see Data Engineering team for setup and credentials)
  3. Download the file named AICP_OREQ_CAPPLN_PJCP.asc
  4. Convert the file to a .csv using Excel
  • Using 'Import data from text' (typically under the data tab) function, import the .asc file with the following parameters:
    • The data does not have headers
    • Delimiters: Custom/Other |
    • Column data format: General / newer versions of excel use Data Type Detection
  • Newer versions of excel may have slightly different steps or prompt you to Load the data into excel. Load it in and you should see it in excel with numbered columns
  1. Save the file as AICP_OREQ_CAPPLN_PJCP.csv
  2. Send the .csv via email and cc Data Engineering staff
  3. Archive AICP_OREQ_CAPPLN_PJCP.csv using the data library process

DDC Capital Projects

Email DDC asking them to provide the latest spatial data for their capital projects. They will send two shapefiles: 1. containing point level data, 2. containing line shapefile. Load the 2 files into data library.

DOT Capital Projects

Email DOT asking them to provide their latest data for their capital projects on bridges. They will send over one shapefile. Load the file into data library.

EDC Capital Projects

Email EDC asking them to provide their latest data for their capital projects. They will send over one shapefile. Load the file into data library. Data from this file get appended onto the existing data.

NOTE: The data library template for this dataset expects a .zip with 3 files in it (.shp, .shx, .dbf). The process of unzipping, renaming, deleting, and zipping these files sometimes corrupts them and causes a path not found error.

Other inputs

Other input datasets that need to be updated, that are not delivered via email include:

Spatial boundaries

Come from Geosupport update

  • dcp_stateassemblydistricts
  • dcp_ct2020
  • dcp_congressionaldistricts
  • dcp_cdboundaries
  • dcp_statesenatedistricts
  • dcp_municipalcourtdistricts
  • dcp_school_districts
  • dcp_trafficanalysiszones
  • dcp_councildistricts
  • nypd_policeprecincts
  • fdny_firecompanies

Building and lot-level info

  • dcp_mappluto
  • dcp_facilities
  • doitt_buildingfootprints

Projects

  • cpdb_capital_spending
  • dot_projects_intersections
  • dot_projects_streets
  • dpr_capitalprojects
  • dpr_parksproperties
  • dcp_cpdb_agencyverified (does not get updated)

Overview of Build Process

Building CPDB

Creating table views

Create the table views for the three different reporting levels: Commitment, Project, Budget

Creating the DCP attributes table

This is where DCP adds value to the information derived from the Capital Commitment Plan

  1. Build the table

  2. Categorize each project into one of three categories based on keywords found in the project description

    • ITT, Vehicles, and Equipment: projects that fund assets not tied to a fixed location, such as ITT, vehicles, or other equipment assets
    • Lump Sum: project lines that may be drawn down upon to create / fund new discrete projects
    • Fixed Asset: projects associated with one or many discrete locations
    • Unknown: if a project cannot be classified into one of the three categories above based on keywords in the project's description then the type category is left blank

Projects are categorized in the order above, meaning that if a keyword is found in a project's description that classifies that project as 'ITT, Vehicles, and Equipment' it cannot later be classified as 'Lump Sum' or 'Fixed Asset,' even if the project's description has other keywords that could classify it into 'Lump Sum' or 'Fixed Asset.' Example project descriptions and their classifications:

Description Type category
Urban Resource Institute: DV Shelter Vans ITT, Vehicles, and Equipment
DATA ANALYTICS SYSTEM ITT, Vehicles, and Equipment
SURVEYS FOR SEWERS IN BRONX Lump Sum
SCA26 Lump Sum
MTC - Renovation of Friedman Theater Fixed Asset
Storm Swr ext 119 ave b/t 192 & 195 st, QNS. Fixed Asset

The primary purpose of these classifications is to help prioritize any manual mapping efforts. Reports should not be made off of these classifications. The list of keywords used to classify projects could be refined.

  1. Append geometries in the following order

  • Geometries that were created manually

    • By bbl (attributes/sprints.sql)
    • By geojson files (attributes/json2sql.py)
    • By bin (attributes/geom_from_id_bin_map.sql)

  • Spatial data from agencies These may overwrite existing geometries

    • DOT
    • DPR
    • EDC
    • DDC
    • Summer 2017 agency verified data

  • Fuzzy string matching

    • We used DCP's Facilities Database and DPR's Parks Properties as base datasets to match project descriptions with either the facility or park name, and then assign the geometry from the reference dataset to the Capital project. None of these geometries can overwrite any existing geometries.

  • Clean geometries

    • Transform lines to polygons and make all geoms multi

  • Remove faulty geometries

    • DPC identified a geometry as incorrect and added it to this list: 'capitalprojects_build/cpdb_geomsremove.csv'
    • An agency indicated that the project cannot be mapped now or ever

Create administrative boundaries relational table

A series of spatial joins are run generating the 'cpdb_adminbounds' table.

Export data

Exports two shapefiles and five csvs

Loading data onto Carto

When you're ready to update the data driving the Capital Projects Explorer load the following tables into the Carto production server (download via DigitalOcean):

  1. cpdb_projects
  2. cpdb_commitments
  3. cpdb_budgets
  4. cpdb_dcpattributes_pts (the .shp.zip extension file)
  5. cpdb_dcpattributes_poly (the .shp.zip extension file)
  6. cpdb_projects_combined
  7. cpdb_adminbounds

Once the latest cpdb output is uploaded to Carto, rename the files with the suffix corresponding to the release cycle (i.e. _22executive). Next copy the script from db-cpdb/sql/cpdb_projects_combined.sql and run the script directly in carto (best practice is to run this via cpdb_projects - making sure to update the table names with the appropriate suffix for this release). Save the output of the script as a new table named cpdb_projects_combined (overwriting the existing table) - to save the script press the 3 dots on the upper left hand corner and press create dataset from query. Additional steps will be needed to grant appropriate accounts with the proper read/write access to these files (speak with @amandadoyle or @mbh329 for clarification).

Inform digital services of updated data so that they can update the data within the Capital Projects Explorer.

Spending data

Each night at midnight the latest Capital spending data is pulled from Checkbook NYC

Analysis

5 analyses that used by the business owners for reporting as well as QA/QC.

The analyses are outputted into 'analysis/output/'

Share the following tables with CPDB's business owners:

  • cpdb_summarystats_magency.csv
    • The number of projects and total planned commitments that are mapped in each type category by managing agency
  • cpdb_summarystats_sagency.csv
    • The number of projects and total planned commitments that are mapped in each type category by sponsor agency
  • projects_by_communitydistrict_commitments.csv
    • Projects and each of the community districts that the project intersects, along with the apportioned commitments associated with the part of the project within that community district.
  • projects_by_communitydistrict_spending.csv
    • Projects and each of the community districts that the project intersects, along with the apportioned commitment and spending associated with the part of the project within that community district.
  • projects_by_dprcdboundaries_spending_2014.csv
    • Projects and each of the community districts that the project intersects, along with the apportioned commitment and spending associated with the part of the project within that community district. Only includes check written beginning in 2014.
  • agency_validated_geoms_summary_table.csv
    • Summary stats of the Summer 2017 exercise, which required agencies to validate existing spatial data and make new spatial data where possible.

Making spatial data

If it's determined that more spatial data should be generated manually for 'mappable' projects within CPDB geometries can be generated by:

  1. geojson.io See Amanda Doyle to determine the best way to incorporate data from this tool into CPDB

  2. simple-geom-editor Right now, this tool can only be used to create point or polygon geometries for single site projects

    • Copy the FMS ID (maprojid) for the project you want to map
    • Paste the FMS ID (maprojid) into the small box at the top of the page
    • Use the tools to draw the desired geometry
    • A text sting will appear in the large box
    • Copy the text string (which is an echo command) and paste the command into the terminal to generate a new .json file within the folder 'capitalprojects_build/attributes/geometries'

  3. id_bin_map If the project is taking place within a single building and you know the bin of the building, then add the FMS ID (maprojid) and the associated bin to capitalprojects_build/attributes/id_bin_map.csv

Release schedule

The Capital Projects Database is updated each time a new Capital Commitment Plan is release by OMB. OMB releases a new Capital Commitment Plan 3x per year each January (Preliminary), April (Executive), and September (Adopted).

The publication of the Capital Commitment Plan triggers all of the processes outlined above.

To know that version is published on Carto or other locations the naming convention of a published table from CPDB is as follows: cpdb_tablename_FYbudgetversion (e.g. cpdb_projects_18adoptedv1)

If new geometries are added or other changes are made to the dcp_agttributes table absent of changing the foundational data, which comes from the Capital Commitment Plan, then the version of the table will be updated (e.g. cpdb_dcpattributes_pts_18adoptedv2) to reflect that DCP has improved the data published.

Caution

This is the archived product README.

db-cpdb

capital spending Build/Run CPDB

Instructions

  1. All the relevant commands for running CPDB is wrapped in the cli bash script ./cpdb.sh, please read the file for more details.

  2. The capital spending scraping process should be done right after we load fisa_capitalcommitments to data library. A separate bash script will import fisa_capitalcommitments to bigquery and we will create export the capital spending table cpdb_capital_spending via a bigquery command (see bash/06_spending.sh for more details)

    Note that this process is triggered via workflow dispatch

  3. Make sure to edit the version.env file to reflect the current fiscal year.

    The fiscal year begins on July 1st of one calendar year and ends on June 30th of the following calendar year (Mayor's Office of Management and Budget: When does the City's fiscal year begin and end?). The output files will be stored in subfolders named after branches.

  4. Since CPDB is still a private database. You can generate a pre-signed sharable link using the ./cpdb.sh share command. Run ./cpdb.sh share --help to see instructions.

Note: the url will only be valid for 7 days.

Update source data

Read more about who to reach out to and how to update a specific dataset here

Received from agency partner and manually loaded into data libraries

  • Capital Commitment Plan: fisa_capitalcommitments
  • ddc_capitalprojects_infrastructure
  • ddc_capitalprojects_publicbuildings
  • dot_projects_bridges
  • edc_capitalprojects - new data needs to be appended onto existing source dataset
  • edc_capitalprojects_ferry - new data is often not received and this dataset is not updated with each release because nothing has changed.

Automatically loaded into data libraries

Before initiating a build of cpdb, verify the latest version in DigitalOcean matches the version in NYC OpenData

Projects

Building and lot-level info

Spatial boundaries

Come from Geosupport update

Other

Getting started

Code/Infrastructure

Data Products

Project Management/Developer Practices

Clone this wiki locally