EPIC: FacDB

[TangoYankee]

Description

Integrate FacDB into data flow and the zoning database

Spreadsheet of fields for upcoming FacDB features in cp map: CPE_facilities_Data Fields

[pratishta]

After discussing with Horatio, here are some questions

1. In the above spreadsheet, what does 'Future' mean under Notes?
2. We notice this is on the the police precincts and school districts columns. Aside from the export, are we expecting to use those for anything else? Trying to determine if we want a separate table for each including their number and geom, or if they can be isolated descriptors of a facility.
3. Same question as ^2 for NTA and CT. Additionally, in the fields spreadsheet, it says 'No' for Address/Admin Bound Search, but in the Nav Filter spreadsheet, these columns are included for Administrative Boundary Available Options. Are NTA and CT going to be used as a way to filter facilities like Community Districts, and City Council Districts?
4. I would like a definition or a scope of our agency table in zoning-api. We have both city agencies as well as other entities like community boards and judicial districts. In FacDB's operating entity column, we have NGOs, non-profit groups, and other orgs that don't necessarily mean they're agencies. Additionally, they don't all have abbreviations or initials, which we currently use as our index in the agency table. We want to know which entities to add to this table as an agency and which entities require their own table.
5. The new column to be added 'Last updated' does this refer to FacDB or the individual facility information that comes from the source data?
6. The designs show that we display the source data for an individual facility in the details panel. FacDB currently includes in its DATASOURCE column, an abbreviation of the data source. Where should we expect to find the full name and it's last updated information?

@TangoYankee

[TangoYankee]

1.Liz should be able to answer questions about the spreadsheet

2.& 3.For police precincts, school districts, NTAs and CTs: They're separate entities from facilities. At this stage, we should try to model them as separate entities, agnostic to planned features (features can and will change)

4.Data Engineering would have insight into FacDB agencies. They're also aware that the Agency spreadsheet that we currently use will need to be expanded to accomodate FacDB. For adding columns to the agency table and/or adding new tables to accomodate different types of agencies, I would like you and Horatio to attempt to define that scope

5.Liz and/or Max should be able to specify which date to use

6.Data engineering should be able to specify the full datasource name

[pratishta]

First draft of data model for FacDB:

Notes:

• Data Engineering will be providing the data source file, presumably can consume from DO
• The following table sources are available in Open Data / DCP resources and presumably can also be put in DO for data-flow to use: NTA2010, NTA2020, CT2010, CT2020, school districts, police precincts

[horatiorosa]

We also have a couple of questions @TangoYankee @TylerMatteo:

• Should we use point or multipoint for the facility geometry column? Facdb seems to provide POINT geometry
• Is Geosearch going to use our facdb or use Pluto (ie: will the drop down suggestions only have Facbd addresses)?

[TangoYankee]

Is Geosearch going to use our facdb or use Pluto (ie: will the drop down suggestions only have Facbd addresses)?

Geosearch uses a few sources to populate the dropdown with addresses. However, we are not matching based on FacDB address. The addresses are too inconsistent across the datasets (PAD, PLUTO, FacDB, budget requests, capital projects, etc) for us to reliably match based on the literal address. Consequently, we are only using the address for display.

When Geosearch returns an address, it also returns a point. We will compare this point from geosearch (plus a user-defined radius) to the spatial column of facilities, capital projects, and budget requests. This is how we will find matches to an geosearch address.

[TangoYankee]

Should we use point or multipoint for the facility geometry column? Facdb seems to provide POINT geometry

The data will guide you! If all the features are points, then the column may be a point type. If a single feature is a multipoint, then all features must be promoted to multipoints.

[TangoYankee]

@NYCPlanning/data-engineering

We are assuming the following columns are a found with a simple spatial intersect with a facility. Is this a valid assumption for all of these columns? (I'm thinking of how zipcodes are actually routes and can't simply be found using a spatial intersect. I want to make sure nothing like that is happening here)

• city council district
• community district
• census tract
• borough
• neighborhood tabulation area
• police precinct
• school district

[damonmcc]

We are assuming the following columns are a found with a simple spatial intersect with a facility. Is this a valid assumption for all of these columns? (I'm thinking of how zipcodes are actually routes and can't simply be found using a spatial intersect. I want to make sure nothing like that is happening here)

TLDR: It seems safe to assume that all of those columns are either from running the source records through geosupport or by a simple spatial intersect with the facility

from a quick look at one of the most relevant scripts pipelines.py and _create_facdb_spatial.sql, what we do is:

1. geocode the records from each source using geosupport
2. if successful, use the boundary geosupport results
3. if not, do a spatial intersect with the facility and each boundary type

there seems to be a boundarysource column that could have a value of geosupport or spatial join but that column doesn't make it all the way to the outputs. and unfortunately those aren't the only scripts involved since I can see in _create_facdb.sql that some columns created in _create_facdb_spatial.sql are filled in where null using special tables like facdb_boro

[TangoYankee]

Questions for our meeting tomorrow

What are the keys for the following tables?

• ct_2010 + ct_2020
• facilities_options_cascade

Have we explored other ways to model the following tables? Could they be the same table, with a column denoting version?

• ct_2010 + ct_2020
• nta_2010 + nta_2020

May we use full names for these abbreviations?

• ct -> census_tract
• nta -> neighborhood_tabulation_area
• puma -> public_use_microdata_area

May we use singular for the following table names?

• facilities -> facility
• school_districts -> school_district

May we remove the geometry type from these facility columns?

• li_ft_m_pnt -> li_ft
• mercator_m_pnt -> mercator

Should the following tables have a column for their label in the mercator projection?

• ct_2010 + ct_2020
• nta_2010 + nta_2020
• police_precinct
• school_district
• facility

May we remove these columns from their tables, as they are spatial?

• facility
  • borough
  • ct2010
  • ct2020
  • community_district
  • nta2010
  • nta2020
  • school_district
  • police_precinct
• ct_2010 + ct_2020
  • borough
  • community_district
  • nta
  • puma (breaking puma into its own table)
• nta_2010 + nta_2020
  • borough
  • community_district

Could we make the following table?

• public_use_microdata_area (it's available in DO edm-publishing-datasets)

Have we explored using this foreign key?

• facility.bbl -> tax_lot.bbl

How will we handle entities with null abbreviations or abbreviations like "Non-public"?

May we add a check constraint to overseeing levels?

• It looks like "City", "County", "State", "Federal", or null.

May we add a check constraint to capacity type?

• It looks like "beds", "seats", "routes", or null

These columns becoming NaN when going from "shp" to "csv" through "shpjs". How will we handle that?

• capacity
• boro_code
• bin
• bbl
• community_district
• council
• latitude
• longitude
• xcoord
• ycoord
• police_precinct

Possible fix actions: use the csv file and create the geometries from the lat/lon. Or, use gdal to import the shapefiles

What is going with facility options?

• Should they be offset? Talking with Natasha to answer that question
• Could we add domain to the options, even if we're not currently placing it in the category dropdown?

Which of the columns could we enforce a "not null" constraint?

How should we handle null facility geometries

• It seems 778 facilities have null geometries
• Of the these, it seems all the spatial columns (except borough) are also null

May we try to consolidate agencies? (this will require consulting with DE)

• The overseeing agencies seem to be a superset of the existing agencies list
• For context, the existing agencies list is already a superset of capital projects and budget requests
• It looks like there are 11 agencies with null levels, including a few city level ones

[pratishta]

What are the keys for the following tables?

• ct_2010 + ct_2020

Using the metadata from here, it's possible to use OBJECTID or CTLabel. I would favor CTLabel because 1) It seems that OpenData doesn't have the OBJECTID and 2) it is descriptive and we can use it as a composite key with the borough code.

• facilities_options_cascade

Are you asking about the primary key?

Have we explored other ways to model the following tables? Could they be the same table, with a column denoting version?

• ct_2010 + ct_2020
• nta_2010 + nta_2020

Not really, but that also sounds reasonable. What I was thinking initially is that it feels redundant to add the same NTA if it hasn't changed between 2010 and 2020.

May we use full names for these abbreviations?

• ct -> census_tract
• nta -> neighborhood_tabulation_area
• puma -> public_use_microdata_area

Yes.

May we use singular for the following table names?

• facilities -> facility
• school_districts -> school_district

May we remove the geometry type from these facility columns?

• li_ft_m_pnt -> li_ft
• mercator_m_pnt -> mercator

Yes. Does the m stand for multi?

Should the following tables have a column for their label in the mercator projection?

• ct_2010 + ct_2020
• nta_2010 + nta_2020
• police_precinct
• school_district
• facility

I've been feeling wary of adding labels if I don't see us using them. I haven't seen designs for ct, nta, precinct, and school districts but if they're meant to be like how we have community districts and city council districts, it makes sense to have a label. For facility, 1) I don't see a label for them in the designs 2) if they're points (and not multi point?) I assume we would use the facility geom itself if we did need a label.

May we remove these columns from their tables, as they are spatial?

• facility

  • borough
  • ct2010
  • ct2020
  • community_district
  • nta2010
  • nta2020
  • school_district
  • police_precinct

• ct_2010 + ct_2020

  • borough
  • community_district
  • nta
  • puma (breaking puma into its own table)

• nta_2010 + nta_2020

  • borough
  • community_district

I am not sure what you mean. These columns are meant to contain the primary keys / ids of the spatial data from their own tables. Apologies for not being explicit earlier, but the column names would have _id. Do you still want to get rid of them?

Could we make the following table?

• public_use_microdata_area (it's available in DO edm-publishing-datasets)

Yes.

Have we explored using this foreign key?

• facility.bbl -> tax_lot.bbl

Not really, but that makes sense to me.

How will we handle entities with null abbreviations or abbreviations like "Non-public"?

Not sure, we will have to come up with something that wouldn't conflict with existing and potentially future entities.

May we add a check constraint to overseeing levels?

• It looks like "City", "County", "State", "Federal", or null.

Yes.

May we add a check constraint to capacity type?

• It looks like "beds", "seats", "routes", or null

Yes.

These columns becoming NaN when going from "shp" to "csv" through "shpjs". How will we handle that?

• capacity
• boro_code
• bin
• bbl
• community_district
• council
• latitude
• longitude
• xcoord
• ycoord
• police_precinct

Possible fix actions: use the csv file and create the geometries from the lat/lon. Or, use gdal to import the shapefiles

Don't know what you mean by this but excited to find out.

What is going with facility options?

• Should they be offset? Talking with Natasha to answer that question
• Could we add domain to the options, even if we're not currently placing it in the category dropdown?

Not sure what you mean by this, also might have gotten some clarification since you posted this feedback.

Which of the columns could we enforce a "not null" constraint?

Honestly, for facilities so far the actual identifying info (id, name) and everything else we haven't had a chance to explore their values.

How should we handle null facility geometries

• It seems 778 facilities have null geometries
• Of the these, it seems all the spatial columns (except borough) are also null

Either we can leave them null or add a column like 'location_specified' if we want to make it easier for API implementation

May we try to consolidate agencies? (this will require consulting with DE)

• The overseeing agencies seem to be a superset of the existing agencies list
• For context, the existing agencies list is already a superset of capital projects and budget requests
• It looks like there are 11 agencies with null levels, including a few city level ones

My impression is that we will be updating the agencies list with the ones in facilities that aren't already there. Then we can import the facilities to check against the updated agencies table.

[horatiorosa]

keys:
we could make out own by prepending the census tract (or nta) with the year, ie: 2010____ ?

other ways to model...:
might be worthwhile to have separate tables for nta's and ct's as some do change from 2010 to 2020.
( For id maybe generate id's from 1 to ... ?)

consolidation:
In general, facility.overagency is a superset of agency, with the exception of 37. Most of those are federal and state agences, with a scattering of others with no overagency name:

Amtrak	Amtrak
Brooklyn Bridge Park Corporation	BBPC
NYC Board of Elections	BOENY
County Clerk	CNTYC
Federal Bureau of Prisons	FBOP
Hudson River Park Trust	HRPT
NYC Office of the Actuary	NYCACT
NYC City Council	NYCC
NYC Civil Service Commission	NYCCCSC
NYC Center for Economic Opportunity	NYCCEO
NYC Department of Consumer Affairs	NYCDCA
NYC Department of Information Technology and Telecommunications	NYCDOITT
NYC Office of Emergency Management	NYCEM
NYC Housing Authority	NYCHA
	NYCNYS
NYC Office of the Medical Examiner	NYCOCME
NYC Office of Labor Relations	NYCOLR
NYC Office of the Mayor	NYCOOM
	NYCOTHP
NYS Unified Court System	NYCOURTS
	NYCRGB
NYC Tax Commission	NYCTAXCM
NYC-Unknown	NYC-Unknown
NYS Department of Environmental Conservation	NYSDEC
NYS Department of Corrections and Community Supervision	NYSDOCCS
NYS Department of Health	NYSDOH
NYS Department of Transportation	NYSDOT
NYS Office of Addiction Services and Supports	NYSOASAS
NYS Office of Mental Health	NYSOMH
The New York State Office of Parks, Recreation and Historic Preservation	NYSOPRHP
NYS Office for People With Developmental Disabilities	NYSOPWDD
Port Authority of New York and New Jersey	PANYNJ
Roosevelt Island Operating Corporation	RIOC
Trust for Governors Island	TGI
US Courts	USCOURTS
US Department of Transportation	USDOT
National Park Service	USNPS

[pratishta]

To summarize take aways from our meeting last week:

• We will use one table for 2010 and 2020 data for NTAs and CTs. So census_tract and neighborhood_tabulation_area
• Add a table for public_use_microdata_area
• Remove spatial columns from facility in favor of using spatial intersect to do spatial filtering
• Come up with abbreviation / initials for "Non-public" entities
• Remove geometry type (m) in facility geom columns
• Add check constraint to overseeing levels
• Add check constraint to capacity type
• Reconsider facility options cascade. After thinking about it, I do think @TylerMatteo's suggestion of having a column for subcategories in each filter option makes more sense. This is because these filters are only pertinent to themselves in the context of facilities. (CBBRs options include agencies so making a table with agencies and its associated cbbrs needs feels kinda random)
• Do we have a final answer from Natasha on what we will be calling these options? Do we have an answer for when they will get renamed to what's required in the designs? @TangoYankee

Notes

• There are about 600 unique FACTYPE/type_detail. Creating a table for each with its own id... I'm not sure what makes sense here
• I would like to get a confirmation or discussion around label columns:

I've been feeling wary of adding labels if I don't see us using them. I haven't seen designs for ct, nta, precinct, and school districts but if they're meant to be like how we have community districts and city council districts, it makes sense to have a label. For facility, 1) I don't see a label for them in the designs 2) if they're points (and not multi point?) I assume we would use the facility geom itself if we did need a label.

Out of scope but notable:

• We do not use or maintain the following tables, but they are effectively in 'production' in our API documentation: tax_lot, zoning_district, zoning_district_class, zoning_district_zoning_district_class, land_use. Is that correct? I'd also like to note that we do have to maintain them to some extent because data-flow does that automatically (for BUILD=all and BUILD=boroughs)
• We have a spatial column in community_board_budget_request with the columns community_district_id and borough_id. Even if this is meant to represent a community board, that is neither reflected in the name of the column nor the ERD when we see how the tables are related.

[TangoYankee]

@pratishta thank you for writing this up

For label columns:

• I do expect census tracts, neighborhood tabulation areas, police precincts, and school districts would be like other admin boundaries like city council districts. To your point, we don't actually have designs for these.
• The only spatial column we really need is "li_ft" and that is for spatial joins. (long island state plane is the working projection for NYC).
• For mercator_fill and mercator_label, it's fine to either add them now or wait until we actually have designs for them.
• The sources for these data are split between: "edm-publishing/datasets" & "edm-recipes/raw" (it would be prudent to confirm with DE/GIS that it's okay for us to pull from these sources)
• For facilities, I did make a mistake. You are correct; they are points. A separate "label" column would be redundant.

For the names of options:

• Natasha clarified that "category" is the only word that actually appears in the designs. "Sub-group" and "Type" do not actually appear in the application. Instead, they are unnamed dropdowns. Of course, this could change and they could be given names.
• As the frontend is concerned with how these data are presented to the user; the frontend should be the application layer that changes the name to its preference. The database and API should use names that track more closely to the data source

A couple notes:

We will use one table for 2010 and 2020 data for NTAs and CTs.

• Yes and making the key a composite of the "label" and "year" might be useful

Come up with abbreviation / initials for "Non-public" entities

• If this turns out to be too complicated, surrogate keys should also be fine

Remove geometry type (m) in facility geom columns

• Being pedantic: 'm_pnt' would be the full geometry type

And finally:
"Hand waves generally at trying to consolidate ageny table"