search

NYCPlanning / db-facilities

🏭 🏢 🏬 🏣 🏤 🏥 🏦 🏨 🏪 🏫 🏩
https://nycplanning.github.io/db-facilities
0 stars 0 forks source link

Incorporate new MOEO data #349

Closed AmandaDoyle closed 3 years ago

AmandaDoyle commented 3 years ago

Incorporate latest MOE data, which was emailed by Martha, but it scheduled to go on Open Data. Pull from Open Data if it's available. Schema is significantly different so the workflow of bringing in the MOE data will need to be refactored.

AmandaDoyle commented 3 years ago

MOEO tables on Open Data Verified Locations for NYC City-Funded Social Service Contracts - Sites Verified Locations for NYC City-Funded Social Service Contracts - Contracts Verified Locations for NYC City-Funded Social Service Contracts - Providers Verified Locations for NYC City-Funded Social Service Contracts - Programs

New tables need to be mapped to FacDB schema.
Additionally, we can use these new datasets to test out any new geocoding workflows.

mgraber commented 3 years ago

Overview

New data does not have address separated out into house number and street name. Use site_address_1 and site_boro for geocoding.

Join site table with program and provider table to get program_name and provider_name.

Field mapping for moeo_socialservicesitelocations:

Existing name Input name Table Used in FacDB
organization_id 0
service_id 0
location_id 0
address_id 0
service_location_id site_id (?) Site 0
agency_id 0
contract_id contract_id Site 0
verify_id 0
file_source 0
agency_name agency_name Site 1
ages_served 0
contract_number contract_number Contracts 0
contract_amount amount Contracts 0
contract_start start_date Contracts 0
contract_end end_date Contracts 0
fiscal_year fiscal_year Contracts 0
provider_name provider_name Providers 1
ein provider_ein Providers 0
program_name program_name Programs 1
source_agency_id 0
bbl bbl Site
bin bin Site
cd community_board Site
council council_district Site
latitude latitude Site
longitude longitude Site
nta nta Site
tract census_tract Site
site_catchment catchment_zone Site 0
site_address_1 address_1 Site 1
site_address_2 address_2 Site 0
site_boro borough Site 1 (Geocoding input)
site_budget site_budget Site 0
site_city city Site 0
site_name site-name Site 0
site_phone 0
alternate_site_phone 0
site_state state Site 0
site_country 0
site_url 0
site_zip postcode Site 0
verification_date 0
active 0
status 1
notes notes Site 0
site_intersection_street_1 0
site_intersection_street_2 0
street_number 1 (Geocoding input)
street_name 1 (Geocoding input)
suppress_address 0
schedule 0
grades_served 0
population_served 0

Join keys

Sites:

Contracts:

Providers:

Programs:

mgraber commented 3 years ago

https://github.com/NYCPlanning/db-data-library/pull/160