Product: CPDB

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 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 outside of NYC.

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

Source data

See recipe.yml for the current input datasets.

Data Dictionary

See the data dictionary and the Capital Planning Glossary.

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.

See the Capital Planning Glossary for these terms and the budget cycle.

I. Overview

Overview table, last updated March 2020. Its Carto download links no longer respond.


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 CapitalPlanning_DL@planning.nyc.gov.

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

Data dictionary for an older schema. Fields like totalcost aren't in current outputs, so use the data dictionary linked above.

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

Input datasets are listed in recipe.yml, and most are ingested with templates in ingest_templates/. The steps below come from the archived db-cpdb and db-data-library repos.

Capital Commitment Plan steps from the archived repos. The fisa_capitalcommitments ingest template now reads the .asc file directly.

Capital Commitment Plan

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
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)
Download the file named AICP_OREQ_CAPPLN_PJCP.asc
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

Save the file as AICP_OREQ_CAPPLN_PJCP.csv
Send the .csv via email and cc Data Engineering staff
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 list from the archived repos. Some names have changed, see recipe.yml.

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

Build the table
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.

Map each project

Each project gets the geometry of the first source below that has one for it. Every source is checked for every project, and int__project_geom_candidates keeps all of them, ranked.

---
config:
  layout: dagre
  look: classic
  flowchart:
    wrappingWidth: 400
---
flowchart TD
    project(["Project in the<br/>Capital Commitment Plan"])

    subgraph candidates [Find a geometry, first match wins]
        av{"Agency verified?<br/>dcp_cpdb_agencyverified"}
        ad{"Agency dataset<br/>with the FMS ID?"}
        dcp{"DCP curated?"}
        sm{"Description names<br/>a park or facility?"}
    end

    subgraph sources [Geometry sources, in priority order within each box]
        avsrc["<b>Agency verified</b><br/>geocoded address, or the<br/>bridge, park, bin, or bbl<br/>the agency gave in 2017"]
        adsrc["<b>Agency datasets</b><br/>1. DDC public buildings<br/>2. DDC infrastructure<br/>3. EDC ferry<br/>4. EDC capital projects<br/>5. DPR capital projects<br/>6. DOT streets<br/>7. DOT intersections<br/>8. DOT bridges"]
        dcpsrc["<b>DCP curated</b><br/>1. manual geometries, 2020<br/>2. bin map<br/>3. geojson<br/>4. sprints"]
        smsrc["<b>String matches</b><br/>1. DPR park ID, like Q123 (DPR)<br/>2. DPR park name (DPR)<br/>3. DPR park name, close spelling (DPR)<br/>4. facility name (14 agencies)"]
    end

    none["No geometry"]

    subgraph removal [Remove known bad geometries]
        bad{"In cpdb_badgeoms and not<br/>from DPR, DOT, or DDC?"}
        never{"Agency said it<br/>can never be mapped?"}
        future{"Agency said it can be mapped<br/>in future, and not from<br/>DPR, DOT, DDC, or EDC?"}
    end

    removed["Geometry removed"]
    clean["Clean up: buffer lines 15 m<br/>into polygons, make multi,<br/>fix inverted longitudes"]

    subgraph outputs [cpdb_dcpattributes]
        pts["Points<br/>cpdb_projects_pts"]
        poly["Polygons<br/>cpdb_projects_poly"]
        unmapped["Unmapped<br/>cpdb_projects only"]
    end

    project --> av
    av -->|yes| avsrc
    av -->|no| ad
    ad -->|yes| adsrc
    ad -->|no| dcp
    dcp -->|yes| dcpsrc
    dcp -->|no| sm
    sm -->|yes| smsrc
    sm -->|no| none

    avsrc --> bad
    adsrc --> bad
    dcpsrc --> bad
    smsrc --> bad

    bad -->|yes| removed
    bad -->|no| never
    never -->|yes| removed
    never -->|no| future
    future -->|yes| removed
    future -->|no| clean

    clean -->|"points"| pts
    clean -->|"lines, polygons"| poly
    removed --> unmapped
    none --> unmapped

Within a box, the numbered sources are checked in order, and the first with a geometry wins. Agency verified beats everything, including the agency datasets. In 26exec, the order between boxes decided 1,623 projects and the order inside a box decided 35.
An agency dataset matches when it carries the project's FMS ID. DDC IDs get DDC's agency code (850) added.
DCP curated geometries were assigned by hand or by one-off scripts. They're kept in the repo as seeds and a static SQL dump (sprints), not refreshed from a source. The bin map takes building centroids from the current footprints.
String matches compare the project description to DPR's Parks Properties and DCP's Facilities Database, and only run for some agencies. DPR park names exclude generic names like PARK and TRIANGLE, and only count BRIDGE PARK when nothing else matches. A close spelling is a description within 3 edits of a park name. When parks share a name, the lowest gispropnum wins. Facility names have to be unique citywide and at least 3 words long.
cpdb_badgeoms is DCP's list of geometries known to be wrong. The agency answers come from the 2017 agency-verified data.
int__project_geom records which source won, every other source that matched, and why a geometry was removed. cpdb_mapping_summary_by_source.csv counts projects at each step.

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. The Carto host from the 2020 overview no longer responds, so these steps may be stale.

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):

cpdb_projects
cpdb_commitments
cpdb_budgets
cpdb_dcpattributes_pts (the .shp.zip extension file)
cpdb_dcpattributes_poly (the .shp.zip extension file)
cpdb_projects_combined
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

Capital spending comes from Checkbook NYC, archived as nycoc_checkbook.

Previous description

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 output/analysis/

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

Making spatial data, from the archived repo. Paths like capitalprojects_build/ no longer exist.

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

geojson.io See Amanda Doyle to determine the best way to incorporate data from this tool into CPDB
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'
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 or May (Executive), and September (Adopted).

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

Versions are now named by the calendar year of release and plan, like 26exec. See the version labels in the Capital Planning Glossary.

Previous naming convention

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_attributes 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.

Archived product README from db-cpdb

db-cpdb

Instructions

All the relevant commands for running CPDB is wrapped in the cli bash script ./cpdb.sh, please read the file for more details.
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
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.
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

cpdb_capital_spending - updated with action
dot_projects_intersections https://data.cityofnewyork.us/Transportation/Street-and-Highway-Capital-Reconstruction-Projects/97nd-ff3i
dot_projects_streets https://data.cityofnewyork.us/Transportation/Street-and-Highway-Capital-Reconstruction-Projects/jvk9-k4re
dpr_capitalprojects https://www.nycgovparks.org/bigapps
dpr_parksproperties https://data.cityofnewyork.us/Recreation/Parks-Properties/enfh-gkve
dcp_cpdb_agencyverified (does not get updated)

Building and lot-level info

dcp_mappluto_wi https://www1.nyc.gov/site/planning/data-maps/open-data/dwn-pluto-mappluto.page
dcp_facilities https://www1.nyc.gov/site/planning/data-maps/open-data/dwn-selfac.page
doitt_buildingfootprints https://data.cityofnewyork.us/Housing-Development/Building-Footprints/nqwf-w8eh

Spatial boundaries

Come from Geosupport update

dcp_stateassemblydistricts https://www1.nyc.gov/assets/planning/download/pdf/data-maps/open-data/nyad_metadata.pdf?ver=21d
dcp_ct2020 https://www1.nyc.gov/site/planning/data-maps/open-data/districts-download-metadata.page
dcp_congressionaldistricts https://www1.nyc.gov/site/planning/data-maps/open-data/districts-download-metadata.page
dcp_cdboundaries https://www1.nyc.gov/site/planning/data-maps/open-data/districts-download-metadata.page
dcp_statesenatedistricts https://www1.nyc.gov/site/planning/data-maps/open-data/districts-download-metadata.page
dcp_municipalcourtdistricts https://www1.nyc.gov/site/planning/data-maps/open-data/districts-download-metadata.page
dcp_school_districts https://www1.nyc.gov/site/planning/data-maps/open-data/districts-download-metadata.page
dcp_councildistricts https://www1.nyc.gov/assets/planning/download/pdf/data-maps/open-data/nycc_metadata.pdf

Other

dcp_trafficanalysiszones - this dataset is almost never updated
nypd_policeprecincts https://data.cityofnewyork.us/Public-Safety/Police-Precincts/78dh-3ptz
fdny_firecompanies https://data.cityofnewyork.us/Public-Safety/Fire-Companies/iiv7-jaj9

Product: CPDB

Capital Projects Database

Build outputs

Primary tables and files

cpdb_projects - .csv

cpdb_opendata_projects - .csv, .shp

Other tables and files

cpdb_adminbounds - .csv

cpdb_budgets - .csv

cpdb_commitments - .csv

cpdb_dcpattributes - .shp

cpdb_opendata_commitments - .csv

cpdb_projects_combined - .csv

cpdb_projects_spending_byyear - .csv

geospatial_check - .csv

Source data

Data Dictionary

Details

I. Overview

II. Limitations and Disclaimers

Business owners

Business owners

Data Dictionary

Projects

maprojid

magency

projectid

description

ccnonexempt

ccexempt

citycost

nccstate

nccfederal

nccother

noncitycost

nccother

totalcost

magencyacro

magencyname

ccpversion

Budgets

maprojid

magency

projectid

budgetline

projecttype

sagencyacro

sagencyname

ccnonexempt

ccexempt

citycost

nccstate

nccfederal

nccother

noncitycost

totalcost

ccpversion

Commitments

maprojid

magency

projectid

budgetline

plancommdate

commitmentdescription

commitmentcode

typc

typcname

ccnonexempt

ccexempt

citycost

nccstate

nccfederal

nccother

noncitycost

totalcost

ccpversion

DCP Attributes

maprojid

magency

projectid

`cpdb_projects` - `.csv`

`cpdb_opendata_projects` - `.csv`, `.shp`

`cpdb_adminbounds` - `.csv`

`cpdb_budgets` - `.csv`

`cpdb_commitments` - `.csv`

`cpdb_dcpattributes` - `.shp`

`cpdb_opendata_commitments` - `.csv`

`cpdb_projects_combined` - `.csv`

`cpdb_projects_spending_byyear` - `.csv`

`geospatial_check` - `.csv`