MapPluto subset as a DB

[TangoYankee]

Description

Organize a subset of MapPluto into a normalized database with the structure shown below.

Acceptance Criteria

For at least this iteration, Application Engineering is responsible for the creation and maintenance of the database. The role of data engineering will be to generate csvs which mirror the table structures that AE can then COPY into the database. The borough and land use csvs are small enough that it was feasible to create them by hand. So, DE will only need to generate the remaining csvs for tax lots and zoning districts.

• [x] There exists a folder on DO spaces with the csvs related to the following tables
  • [x] tax_lot
  • [x] zoning district
  • [x] zoning district class
  • [x] colors populated with the zoning-district configuration in layers api
  • [x] zoning district to zoning district class (many to many relationship)

Note: Zoning districts are intentionally excluded from the tax lot table. They will be related to tax lots through spatial joins performed when an end user requests zoning information about a tax lot

Data sources

The majority of data comes from Map Pluto. However, geospatial data on zoning districts will need to be sourced from the zoning districts data set.

Data descriptions

The data fields are sourced and structured as follows

• tax_lot table

  • bbl: from the MapPluto BBL field, with duplicate BBLs removed from the dataset
  • borough_id: from the MapPluto BoroCode field
  • block: from the MapPluto block field
  • lot: from the MapPluto block field
  • address: from the MapPluto address field
  • land_use_id: from the MapPluto LandUse field, structured to match the values in the land use table in the pluto data dictionary
  • wgs84: tax lot coordinates in wggs84 (epsg:4326)
  • li_ft: tax lot coordinates in Long Island (FT) (epsg:2263)

• borough table (borough.csv for convenience)

  • id: from the value listed in borocode table within the pluto data dictionary
  • title: from the description listed in borough table within the pluto data dictionary
  • abbr: from the value listed in borough table within the pluto data dictionary

• land_use table (land_use_v1.csv for convenience)

  • id: from the value in the land use table within the pluto data dictionary
  • description: from the description in the land use table within the pluto data dictionary
  • color: land use from the existing zola layers
  • 1 is the octothorpe
  • 2-7 for rgb
  • 8-9 for opacity

• zoning_district table (sourced from zoning district data)

  • id: generated by the database
  • label: from the zonedist field of the Zoning District data
  • wgs84: zoning district coordinates in wggs84 (epsg:4326)
  • li_ft: zoning district coordinates in Long Island (FT) (epsg:2263)

• zoning_district_class table

  • id: From a list of each unique instance of a zoning code found through the zonedist split process (Each row should be a value like M1 or R7)
  • Similar to the Zone District Tilesets, it should exclude:
    • PARK
    • BALL FIELD
    • PUBLIC PLACE
    • PLAYGROUND
    • BPC
    • null
    • an empty string
  • category: The type of zoning district.
  • Enum of Residential, Commercial, or Manufacturing
  • description: zoning class descriptions from zola
    • There exists class descriptions, such as R6, and contextual descriptions, such as R6A. For all contextual districts, the underlying class description should be used. We do not currently support zoning contexts.
    • The exception is R3, which does not have its own description. For R3, use a modified description of R3-1 districts:

    • R3 districts are the lowest density districts that allow semi-detached one- and two-family residences, as well as detached homes

  • url: (nullable) The full url to the planning website page that explains the zoning district. There are odd exceptions and accounting for them may be too time consuming for this iteration. In these cases, it is fine to leave them as null.
  • color: zoning classes from the existing zola layer groups
  • 1 is the octothorpe
  • 2-7 for rgb
  • 8-9 for opacity

• zoning_district_zoning_district_class table

  • Structures a many to many relationship between the tables of its namesake.

[TangoYankee]

pinging @TylerMatteo for confirmation of details

[TangoYankee]

Will make zoning district PK a UUID

[TylerMatteo]

@TangoYankee Looks good!

Some feedback:

• Thoughts on making the zoning district class category a Postgres enum? MikroORM supports them. I can see an argument either way - having human-friendly strings like "Commercial" means we don't have to map the values to labels on the front end.
• Under Borough in Data descriptions, I think you meant "title", not "tile"

[TangoYankee]

@TylerMatteo Updated the ticket with zoning district id, zoning district class category, and borough title changes

@damonmcc A note on drawio svg v png; the first iteration of the ticket used svg. But, GitHub's rendering was ranging from awkward to unreadable. So, I swapped to png.

[TangoYankee]

@TylerMatteo If we want to account for colors of zoning districts and land use in the database, we could:

1) Break the zoning district category enum into a full table. Then, make a 'color' field on the created zoning_district_category table 2) Add a color field to the land use table

[TylerMatteo]

Based on our IRL discussions:

• Add 9-character length string columns to the land use and zoning district class tables
• Don't need a separate zoning district category table because the Zola behavior we're modeling after actually just picks the highest-numbered class within the category as the color for the category. Therefore, we can get colors for categories by SELECT DISTINCTing on the land use classes

[TangoYankee]

@NYCPlanning/data-engineering updating zoning_district_class to have a description field

[TylerMatteo]

TODO for @TangoYankee - add a field to the zoning district class ERD for the URL to link to the public website page for that class. We're going to make it nullable to account for cases where we can't build a real link.

[TangoYankee]

add a field to the zoning district class ERD for the URL to link to the public website page for that class. We're going to make it nullable to account for cases where we can't build a real link.

@NYCPlanning/data-engineering I recognize the description I gave for the url field is kinda lackluster but I'm not really sure how to explain it further. I am available for further clarification.