2022 Q2 Facilites DB Dataloading

[SashaWeinstein]

FacDB Source Data Updates

Like most of our data products, source data must be updated in data library before FacDB is run. As there are are many source datasets with varied update processes, this issue template should be opened to track progress towards updating all source data

All source data is to be uploaded as .sql files

Scraped by data library

• [x] bpl_libraries Source: Scraped from BPL website Source url: https://www.bklynlibrary.org/locations/json

• [x] nypl_libraries Source: Scrape from NYPL website Source url: https://www.nypl.org/locations/list

• [x] uscourts_courts Source: Court locator for NY state Source url: http://www.uscourts.gov/court-locator/city/New%20York/state/NY

Source data from OpenData

To see if a dataset needs to be uploaded, check date last updated in open data against version in data library

• [x] dca_operatingbusinesses https://data.cityofnewyork.us/Business/Legally-Operating-Businesses/w7w3-xahh

• [x] dcp_colp https://www1.nyc.gov/site/planning/data-maps/open-data.page#city_facilities

• [x] dcla_culturalinstitutions https://data.cityofnewyork.us/Recreation/DCLA-Cultural-Organizations/u35m-9t32

• [x] dfta_contracts https://data.cityofnewyork.us/Social-Services/DFTA-Contracts/6j6t-3ixh

• [x] doe_busroutesgarages https://data.cityofnewyork.us/Transportation/Routes/8yac-vygm

• [x] sca_enrollment_capacity https://data.cityofnewyork.us/Education/Enrollment-Capacity-And-Utilization-Reports-Target/8b9a-pywy

• [x] dohmh_daycare https://data.cityofnewyork.us/Health/DOHMH-Childcare-Center-Inspections/dsg6-ifza

• [x] dpr_parksproperties https://nycopendata.socrata.com/Recreation/Parks-Properties/enfh-gkve NOTE: DPR open data table URLs are not consistent. Be sure to double-check before running from the recipes app.

• [x] dsny_garages https://data.cityofnewyork.us/Environment/DSNY-Garages/xw3j-2yxf

• [x] dsny_specialwastedrop https://data.cityofnewyork.us/Environment/DSNY-Special-Waste-Drop-off-Sites/242c-ru4i

• [x] dsny_textiledrop https://data.cityofnewyork.us/Environment/Textile-Drop-Off-Locations-in-NYC/qnjm-wvu5

• [x] dsny_leafdrop https://data.cityofnewyork.us/Environment/Leaf-Drop-Off-Locations-in-NYC/8i9k-4gi5

• [x] dsny_fooddrop https://data.cityofnewyork.us/Environment/Food-Scrap-Drop-Off-Locations-in-NYC/if26-z6xq

• [x] dsny_electronicsdrop https://data.cityofnewyork.us/Environment/Electronics-Drop-Off-Locations-in-NYC/wshr-5vic

• [x] dycd_afterschoolprograms https://data.cityofnewyork.us/Education/DYCD-after-school-programs/mbd7-jfnc

• [x] fdny_firehouses https://data.cityofnewyork.us/Public-Safety/FDNY-Firehouse-Listing/hc8x-tcnd

• [x] nycha_communitycenters https://data.cityofnewyork.us/Social-Services/Directory-of-NYCHA-Community-Facilities/crns-fw6u

• [x] hhc_hospitals https://data.cityofnewyork.us/Health/Health-and-Hospitals-Corporation-HHC-Facilities/f7b6-v6v3

• [x] hra_jobcenters https://data.cityofnewyork.us/Business/Directory-Of-Job-Centers/9d9t-bmk7

• [x] hra_medicaid https://data.cityofnewyork.us/City-Government/Medicaid-Offices/ibs4-k445

• [x] hra_snapcenters https://data.cityofnewyork.us/Social-Services/Directory-of-SNAP-Centers/tc6u-8rnp

• [x] nycha_policeservice https://data.cityofnewyork.us/Housing-Development/NYCHA-PSA-Police-Service-Areas-/72wx-vdjr

• [x] nysdec_solidwaste https://data.ny.gov/Energy-Environment/Solid-Waste-Management-Facilities/2fni-raj8

• [x] nysdoh_healthfacilities https://health.data.ny.gov/Health/Health-Facility-General-Information/vn5v-hh5r

• [x] nysdoh_nursinghomes https://health.data.ny.gov/Health/Nursing-Home-Weekly-Bed-Census-Last-Submission/izta-vnpq

• [x] nysomh_mentalhealth https://data.ny.gov/Human-Services/Local-Mental-Health-Programs/6nvr-tbv8

• [x] nysopwdd_providers https://data.ny.gov/Human-Services/Directory-of-Developmental-Disabilities-Service-Pr/ieqx-cqyk

• [x] nysparks_historicplaces https://data.ny.gov/Recreation/National-Register-of-Historic-Places/iisn-hnyv

• [x] nysparks_parks https://data.ny.gov/Recreation/State-Park-Facility-Points/9uuk-x7vh

• [x] qpl_libraries https://data.cityofnewyork.us/Education/Queens-Library-Branches/kh3d-xhq7 (this Queens library also does not exists)

• [x] sbs_workforce1 https://data.cityofnewyork.us/dataset/Center-Service-Locations/6smc-7mk6

• [x] moeo_socialservicesitelocations https://data.cityofnewyork.us/City-Government/Verified-Locations-for-NYC-City-Funded-Social-Serv/2bvn-ky2h

• [x] #528 Head to url >> api >> copy url from geojson

• [x] usdot_ports https://hub.arcgis.com/datasets/usdot::ports Head to url >> api >> copy url from geojson

• [x] #527

Manually check data for updates

These don't report date updated as neatly as the open datasets, have to look at data itself

• [x] fbop_corrections https://www.bop.gov/locations/list.jsp When searching by state, there should be 5 NY prisons, 3 of which are in NYC (Brooklyn/New York)

• [x] nycdoc_corrections https://www1.nyc.gov/site/doc/about/facilities-locations.page Source: NYCDOC locations directory

• [x] nycourts_courts http://www.nycourts.gov/courts/nyc/criminal/generalinfo.shtml#BRONX_COUNTY

• [x] nysdoccs_corrections https://doccs.ny.gov/find-facility Hand check for 1 facility in queens, 1 facility in Manhattan, 0 in the other 3 boros. Only look at the correctional facility locations, not the offices.

Manual download

• [x] #529 This dataset is updated for CEQR

• [x] foodbankny_foodbanks http://www.foodbanknyc.org/get-help/ Go to the expanded view of the google maps. Click “Download KML” under the options (three dots). Instead of “Entire Map,” select “Food Bank For NYC Open Sites.” Select. “Keep data up to date with network link KML (only usable online).“ Go to https://mygeodata.cloud/converter/kmz-to-csv to convert the kmz to csv, then use recipe app to load in the csv

• [x] nysed_activeinstitutions https://eservices.nysed.gov/sedreports/list?id=1 Active Institutions with GIS coordinates and OITS Accuracy Code - Select by County__ CSV

  • [x] nysed_nonpublicenrollment http://www.p12.nysed.gov/irs/statistics/nonpublic/ Nonpublic Enrollment by Grade

• [x] nysoasas_programs https://webapps.oasas.ny.gov/providerDirectory/index.cfm?search_type=2 Download all treatment providers Modify download URL to contain today’s date: https://webapps.oasas.ny.gov/providerDirectory/download/Treatment_Providers_OASAS_Directory_Search_13-Nov-20.csv

• [x] usnps_parks https://irma.nps.gov/DataStore/Reference/Profile/2225713 NOTE: the final number in the URL (2225713) is not always stable. If the data is missing, search through the home.

Will receive via email or FTP

• [x] dot_bridgehouses
• [x] dot_ferryterminals
• [x] dot_mannedfacilities
• [x] dot_publicparking
• [x] dot_pedplazas

Unresolved process

Still waiting to figure out best way to upload these data

• [ ] doe_universalprek Source url: https://maps.nyc.gov/prek/data/pka/pka.csv

Last step

• [x] dcp_pops Source: Download from POPs app, available on DCP Commons. Be sure to only take the public version. Be sure to do this source last, as the OpenData release of POPs needs to be in sync

[AmandaDoyle]

hra_centers as a source was replaced with the following:

• hra_jobcenters
• hra_medicaid
• hra_snapcenters See these two issues (#365 and #372 for context) We'll need to make sure that these datasets are updated in data libraries in Digital Ocean. You can use the templates in Digital Ocean for the source. Also, update documentation to reflect this change.

[AmandaDoyle]

doe_universalprek is still a data source and there are 1,597 records in FacDB where this is the datasource. SELECT * from facdb.facdb where datasource LIKE 'doe_universalprek'; The old path to get these data has been deprecated. Next step - can what's available on OpenData be used? Is it the same that's what currently in data libraries? What's different between the two datasets, if anything?

[mbh329]

Ill take a look at the hra data and update it in data-library, run it through facdb

[AmandaDoyle]

doe_lcgms is the source data for 1841 records in FacDB. SELECT * from facdb.facdb where datasource LIKE 'doe_lcgms'; Here is what is in data libraries. Based on the yml we don't get this data from OpenData, but from here. Next step is to confirm that what you download from here is what we have in data libraries and if so update the data in libraries.

[td928]

taken a look at doe_universalprek

some quick feedbacks. the Open Data version is pretty different from what we had last year. But in terms of how facdb uses this source it seems that the most crucial fields are still the available. The number of records in our last version is about 1500+ and the open data version is 1800+.

Another relevant enhancement from the Open Data verison is inclusion of lat long for each school. My thinking is we can turn this pairs into a wkb_geometry in the sql step which was null in the previous version. We can also switch from using 1B function to geocode with address to BIN or BBL which is also available on open data.

I can open up an issue and start working on this if no one already has a start. @AmandaDoyle @SashaWeinstein @mbh329

[mbh329]

@td928 I have not started working on it, so feel free to work on the doe_universalprek