School to Retailer (Euclidean) Matrix and Long Form

[AmandaGIN]

Next step after issue #7 Generate a table of retailers by store, within 1/2 mile buffer

Generate Near Table Input Features: P:\proj_p_s\Stanford_PRC\BOE_2017\data\Schools\CSCD_2016_publicSchools_K12 (poly) Near Features: P:\proj_p_s\Stanford_PRC\BOE_2017\data\BOE2017_2018_01_17 Output: P:\proj_p_s\Stanford_PRC\BOE_2017\data\Schools\SchoolsWithNearStores Search Radius: 0.5 miles Uncheck all 3 boxes (location, angel, find only closest feature) Maximum number of closest matches: default Method: PLANAR

[AmandaGIN]

In output table, code the starting place IN_FID back to the unique school code:

• [x] Add FieldCDS_uniq text, 100

• [x] Join the IN_FID to the CSCD_2016_publicSchools_K12 FID

• [x] copy over CDS code into the SchoolsWithNearStores table

[AmandaGIN]

In output table, code the destination NEAR_FID back to the retailer license code:

• [x] Add field LicenseNum double

• [x] Join the NEAR_FID to BOE2017_2018_01_17 FID

• [x] copy over the License number into the SchoolsWithNearStores table Convert the calculate the meters to miles

[AmandaGIN]

I also brought in the DBA field and the 'closest school' field from the earlier analysis `NEARESTECU

!!!! ALERT !!!! This caused a mistake/error later - when the NEARESTECU was used for the distance to school - it should have instead used the NEAR_DIST converted from meters to miles.

[AmandaGIN]

Concate: rank, license, dba, miles

Bring CDS and concate results into Google Sheet https://drive.google.com/open?id=1DhQNnrRJf8RbqO4UETFIVOFD6c6vlwpxkFGdyW5czdA Use google sheet (unique, filter, pivot) to generate unique rows for schools with each corresponding retailer(s) in columns.

Downloaded to csv file and Renamed csv to txt P:\proj_p_s\Stanford_PRC\BOE_2017\data\Schools\GoogleSheet_SchoolsToStoreMatrix.txt

Imported in Excel (set CDS_Uniq to text and , deliminator)

Asking Nina if she wants all store attributes. If so, if she wants them in 1 cell or broken out (text to columns? try another .txt extension and then open again with a ; converter?)

[AmandaGIN]

Nina wants a flat file w/ one row for each retailer (the school repeats). Created file with both. P:\proj_p_s\Stanford_PRC\BOE_2017\data\Schools\Schools_to_Retailers_MalfMile.xlsx

[AmandaGIN]

Data Checks:

• spot checked a few schools, found buffer looked at total count and specs (dba, licenseno) to be sure they matched.
• Arcmap, did a Spatial Join of the buffers of schools to the retailers. One-to-many. It produced and output file of each school buffer repeating for each retailer. Total count: 70,793 Removed schools with 0 retailers in the 1/2 mile Final Count: 69,158

Final county in Excel: 69,171

The difference (13 more retailers in Excel file; less than 0.1%) is because the Generate Near Table method used is slightly more accurate than the spatial join test.

[AmandaGIN]

Nina wants all schools listed. If there are no retailers they are listed with a rank of 0.

• [x] Take input BOE list - query for missing and add.

[AmandaGIN]

Noted Near Table assigns same rank if the store is same distance - also included the total number and highlighted notes in metadata sheet.

[AmandaGIN]

Sent to client: P:\proj_p_s\Stanford_PRC\BOE_2017\data\Schools\Schools_to_Retailers_HalfMileEuclidean.xlsx https://www.dropbox.com/s/lh7elzi6kequ8lw/Schools_to_Retailers_HalfMileEuclidean.xlsx?dl=0

[AmandaGIN]

3/1/18 AR discovered and error in the data:

The miles reported in the initial file are incorrect. Column C on the sheet 'School to Retailers - Long Form' shows the miles TO THE NEAREST SCHOOL, that may, or may not be the school in column A. This error also affects the values of distance on the 'School to Retailer - Matrix' tab. That last of the four reported variables is incorrect (MILES). Again, it is the distance to the nearest school which may not be the school in column A.

Called and discussed with Nina and found it did not impact any of the uses of the file they have done to date.

Readme at: P:\proj_p_s\Stanford_PRC\BOE_2017\data\Schools\Distance_miles_error\aaaREAD_ME.txt

[AmandaGIN]

Run Generate Near to determine closest 1 (regardless of how far) P:\proj_p_s\Stanford_PRC\BOE_2017\data\Schools\Schools_Closest1store.dbf

In output table, code the starting place IN_FID back to the unique school code:

• [x] Add FieldCDS_uniqtext, 100
• [x] Join the IN_FID to the CSCD_2016_publicSchools_K12 FID
• [x] copy over CDS code into the SchoolsWithNearStores table

In output table, code the destination NEAR_FID back to the retailer license code:

• [x] Add field LicenseNumdouble
• [x] Join the NEAR_FID to BOE2017_2018_01_17 FID
• [x] copy over the License number into the SchoolsWithNearStores table

Convert to miles MileClose = NEAR_DIST*0.000621371

[AmandaGIN]

Final workbook: P:\proj_p_s\Stanford_PRC\BOE_2017\data\Schools\Schools_to_Retailers_HalfMileNetworkAndEuclidean.xlsx Dropbox: https://www.dropbox.com/s/jmv7cuvu74guhcy/Schools_to_Retailers_HalfMileNetworkAndEuclidean.xlsx?dl=0