Add Schools_PreK_12 from WFRC data

[ZachBeck]

Summary

WFRC has taken the time to download, geocode, and QAQC new schools data from the State Office of Education. This data will replace the existing schools data in the SGID. Higher Ed locations will need to be exported from the existing schools data and moved into their own data set.

• Proposed Dataset Name: Schools_PreKto12
• Proposed ISO Category: Society
• Data Description: pre school and K-12 locations
• Source Agency/Entity: State Office of Education
• Contact: SOE
• Update Frequency: Yearly

Triage

• [ ] Data Team Triage Complete
• [x] Dev Team Triage Complete (@steveoh )
• [ ] Cadastre Team Triage Complete

We are introducing data

The data was or will be added on 2020/08/20 to the following areas

• [x] Internal SGID
• [x] SGID10
• [x] ArcGIS Online

The data is high quality

• [x] Sweeper checks have run and passed
• [x] The minimum requirements for metadata are populated
• [x] The data complies with our domain rules

The data should propagate automatically to

• [x] ArcGIS Online
• [x] SGID10
• [x] Open SGID
• [x] Open data
• [x] SGID.META.ChangeDetection

References to the data need to be manually added to

• [x] gis.utah.gov data pages (@gregbunce )
• [x] Stewardship doc
• [x] SGID.META.AGOLItems

We have verified the data is live

• [x] ArcGIS Online (@gregbunce on 08/25/2020)
• [x] Internal SGID (@ZachBeck on 08/20/2020)
• [x] SGID10 (@stdavis on 08/24/2020)
• [x] Open SGID (@gregbunce on 08/24/2020)
• [x] Open data (@gregbunce on 05/25/2020)
• [x] gis.utah.gov data pages (@gregbunce on 08/25/2020)
• [x] sgid-index (@ZachBeck 08/31/2020)
• [x] SGID.META.AGOLItems (@ZachBeck on 08/20/2020)
• [x] SGID.META.ChangeDetection (@ZachBeck on ``)
• [x] agol-validator items (@jacobdadams on ``)

Notification

• [ ] Blog post (@assigned)
• [x] Twitter (@steveoh)

[gregbunce]

i vote for the name Schools_PreK_12

[gregbunce]

@ZachBeck let me know how i can help

[steveoh]

My vote would be Schools_pre_through_12, Schools_preK_through_12, or Schools_k_through_12; Whichever makes more sense.

@ZachBeck let me know how i can help

I think the first thing we need to do is choose the name so we can start loading data.

[ZachBeck]

I'm trying to dream up some metadata right now. Is it ok if it's bare bones to start out with and then gets improved upon in the coming weeks?

[steveoh]

Is it ok if it's bare bones to start out with and then gets improved upon in the coming weeks?

Would bare bones still meet the minimum requirements?

[steveoh]

I removed the dates from the We have verified the data is live section since no one has verified the data is live since it isn't live anywhere yet.

[gregbunce]

I think we should do at least the minimum requirements. .. and i'm happy to lead that. do we have a good contact for the data, or is us and wfrc (@ZachBeck )? if we do, i can lead off with this form one of my goals this year to get solid metadata in for all of our tier-one datasets - and this is tier one-ish. I have time later today to get it rolling and then i should be able to close it out tomorrow.

[ZachBeck]

It will have at least the minimum requirements, but there will be room for improvement. For now the best contact is us until we wrangle in someone from the board of education. I'd like to get this loaded today and have it make it's way into the AGOL realm by next week. Bert/Nicole hooked us up by doing all the work so it would be nice to have a quick turn around.

[gregbunce]

oh, okay. The minimum requirements is good! nice. I'm still happy to improve on it by reaching out to wfrc and others (SOE?). it sounds like our contact there is Chuma Uzoh (Chuma.Uzoh@schools.utah.gov). I'll start a conversation with them today and also see if wfrc has anything to add. thanks, zach for keeping this going!

I'll have to hit you up for the connection creds, or hand off to you any updated text.

[steveoh]

It would be nice to document the process that WFRC (@bert-granberg and @nicolewfrc) went through with the data provided by the SOE. I think that is very important information to provide.

[bert-granberg]

Here's the overview: @nicproulx may want to review

• Download 2019-20 from SOE web pages (Schools List and School Enrollment)
• Use SOE schema for final product
• Geocode all SOE addresses using mapserv API
  • Manually place all unmatched / poor matches using Google search & maps
  • Note SOE address issues so AGRC can communicate these back to SOE data steward for enhancements
• Compare new geocoding results with 2016-17 AGRC Schools data using School district - school ID joins
  • Resolve disparities between locations where they are greater than 100 meters
  • Keep 2016-17 AGRC Schools manual placements where they are well-located (on buildings for example), notify AGRC where their placement was inaccurate
• Remove any problematic records (no address, google can't find address, out of state, etc)
• Reconcile any duplicate school records (ex. one school has a record for middle and high school at same location)
• Note AGRC address point issues so that they can be communicated back to AGRC for enhancements
• Deliver suggested final product format and the 'working format product' that has corrections, process attributes, etc to AGRC

[nicolewfrc]

Bert started this process and I think I took it over somewhere after the geocoding steps described above.

• All manual matches / poor matches were reviewed
• All points where AGRC point was greater than 100 meters from geocoded point were reviewed
• SOE points were joined to AGRC points using the following key: LEANumber|SchoolNumber
• Opposite join also performed (AGRC points to SOE points)
  • These 2 joins resulted in 76 SOE points with no AGRC match and 36 AGRC points with no SOE match (65 additional AGRC points with no SOE match were Higher Ed and/or adult vocational institutions)
• All points with no match were manually reviewed.
  • Most SOE points without an AGRC match were new schools.
  • Most AGRC points with no SOE match were either closed, duplicate (K-6 and 7-12 where these were merged in SOE data), and/or had an incorrect SchoolNumber.
• Where AGRC points were correct, their geometry was preserved.
• Where new points were created, they were positioned / centered on the building.

Takehome Lesson: This should be a much simpler process next year - create a key field with the LEANumber|SchoolNumber, join them both ways, add what's new and remove what has closed. You may need to double check a few specific cases, see notes below on USDB schools and sylvan learning centers.

Here are some other notes about the process that I sent to Zach along with the data (corrections notes are in the dataset sent for internal use by AGRC and SOE):

• This data contains preschool and K-12 schools only. Higher Ed and adult vocational training are not included in the dataset.
• USDB Schools (Utah Schools for the Deaf and Blind) will likely have some changes in the next round of updates.
  • "Central Region Deaf" and "Blind Region 2" have merged with the Jean Massieu School for the Deaf. The points are now in the correct location. The school numbers and names may change and they may convert to only 2 separate records instead of 3.
  • "South Region Deaf" and "Blind South" have moved to a new location in Springville. The points are now in the corrected location. The school numbers and names may change.
• Sylvan Learning Centers will likely have changes - it appears that some of them have moved locations and changed names already this year.
• Minersville School had 2 records with the same name and same LEA Number and School Number. One record had K-6 enrollment, the other had 7-8 enrollment. I combined them into a single record with K-8 enrollment. SOE has combined all other records for elementary and high schools that share a building into one K-12 record.
• Where AGRC points were correct, their geometry was preserved. Where AGRC points were no longer correct (manual review), they were replaced with new points in the correct location. New schools were also added and their locations were manually reviewed.
• Corrections to the school's name and address are found in the Name_Corrected, Address_Corrected, City_Corrected and Zip_Corrected fields.
• The LocationNotes field has information on the new location or address. Some things may need to be fixed in the SOE data, some things may need to be fixed in AGRC's address points data:
  • "Address pt on bldg" means the new address listed is the address point that is located on or closest to the school building.
  • "Building needs address point" means that there is no address point on or near the school and a new address point is needed.
  • "Correct address point to..." means that the address point is incorrect, misspelled, etc. This was determined using road centerlines.
  • "Remove duplicate address point" means there are two points with the same address in different locations on or near the school property.
  • "Closed" means the school has been closed and should be removed from the data (or at least listed as closed in the DateClosed field)
  • "District Office" means that this location is a district office and I couldn't find any information on whether there is actually a school there.
  • There is a school in the data that is located in Colorado City, AZ. Probably can remove this.
• SOE_Fix field - if Y, the name, address, city and/or zip need to be corrected in the SOE database.
• AddressPt_Fix field - if Y, an address point needs to be corrected or added.
• There are some points where both the SOE data and the address point need to be corrected.

There are 1250 schools in this dataset (1242 in the ready to publish set, having removed the closed schools), matching the original SOE dataset of 1251 with the duplicate Minersville school removed.

Hope this helps, let me know if you have any questions.

[nicolewfrc]

FYI nicproulx isn't me. Good guess though!

[gregbunce]

wow, thanks for thorough details, @bert-granberg @nicolewfrc . are there any questions in this form that you feel you have good insight on? we use that form to update our metadata. if so, feel free to fill out those sections (and leave the others blank) and I'll get it updated. thanks again for all the work you've done on this.

[nicolewfrc]

are there any questions in this form that you feel you have good insight on?

I don't think I have much additional insight other than the notes I included above. @bert-granberg obtained the original data from SOE so he may have more info on the contact info, update schedule, etc. I would say that if all the suggested changes are made (school address corrections and new & corrected address points), the dataset should be very reliable/accurate and that all schools should geocode correctly to an address point that is on or very close to the school building.

[ZachBeck]

Loaded as SOCIETY.Schools_PreKto12 and a record added in META.AGOLItems.

[agrc-conductor]

conductor results

check	status
internal sgid	:+1:
sgid10	:+1:
meta table
- item id	:-1:
- item name	:+1:
open sgid	:+1:
open data	:+1:
stewardship
- Website URL	:-1:
- Endpoint	:-1:
- Description	:+1:
- Data Source	:+1:
- Data Type	:+1:

[gregbunce]

I'll work on getting the data page updated and then transfer all relevant metadata-related info to the db.

[gregbunce]

any word on when the data will propagate to agol/opendata?

[ZachBeck]

it's in agol now, but it still needs a description etc. Not sure how long it will take to get to open data

[gregbunce]

wow, great! i'm updating the data page and I wanted to add the open data link.

[gregbunce]

@ZachBeck I have a description for you, i'm just having bert take a look at it to see if he wants to add anything.

[gregbunce]

@jacobdadams i'm not sure if you're doing this part, but here are some tags for agol: Utah State Board of Education, USBE, School Enrollment, School Demographics, School Locations

let me know if you want me to add them to agol. i'm in there now. i just wasn't sure if you had a certain method for doing that.

[jacobdadams]

Auditor is running right now and should populate the description, etc from the metadata and share it out to the right Open Data group. I'll check it later this afternoon after Auditor finishes and see if everything's where it should be, and fix it if it's not.

On Mon, Aug 24, 2020 at 2:28 PM Greg Bunce notifications@github.com wrote:

@jacobdadams https://github.com/jacobdadams i'm not sure if you're doing this part, but here are some tags: Utah State Board of Education, USBE, School Enrollment, School Demographics, School Locations

let me know if you want me to add them to agol. i'm in there now. i just wasn't sure if you had a certain method for doing that.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/agrc/porter/issues/57#issuecomment-679348645, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJDGLXS735JNB574KO62WZLSCLENLANCNFSM4QGKMTWQ .

[gregbunce]

great, thanks, @jacobdadams. should I add those tags to the agol item page? also, i'll hand off some updated metadata to zach later today, so i guess agol/open data will get updated with metadata on the next auditor run. do you know when that is scheduled?

[jacobdadams]

Next full Auditor run probably won't be till next monday, but Scott pointed out it would be usefull to be able to run it on single items so I may implement that sooner. Or I'll just manually set all the different things tonight.

@gregbunce, let's hold off on changing AGOL until Auditor finishes (probably 3:00-4:00ish). I don't think it'll break anything, but I'm just curious to see what it grabs automatically as we've put stuff in at different times.

[jacobdadams]

OK, Auditor has run and it looks like it picked up a fair amount of information for this layer (and shared it to the Society Open Data group). Take a look at what's there and let me know if we need to do some manual updates or if this will suffice until the next Auditor run.

[gregbunce]

Thanks, @jacobdadams. I just updated the metadata in Internal to match the wording on the new data page for this layer.

Is it possible to get that updated in agol/opendata before Thursday at 1pm? We have a meeting with the schools folks and I wanted to "sell" the finished product to them during the meeting.

I know it's more work on your end. Thank you!

[jacobdadams]

Yeah, I should be able to update that this afternoon. It's not a huge task.

[gregbunce]

i'm curious how that the db metadata will translate to open data. there are a few bullets and hyperlinks and a few bold headings. we shall see.

[steveoh]

let me know if you want me to add them to agol. i'm in there now. i just wasn't sure if you had a certain method for doing that.

@jacobdadams do we edit items in agol or should we only edit in our source since our tools will most likely override any modifications? Are there items that are merged instead of overwritten? How does that all work?

[gregbunce]

good point, thanks for pointing that out. that's the way i understand it, too - only edit in metadata in internal db.

for clarity, I only edited internal db metadata and tags.

[jacobdadams]

i'm curious how that the db metadata will translate to open data. there are a few bullets and hyperlinks and a few bold headings. we shall see.

It looks like it came in just fine! Take a look.

@jacobdadams do we edit items in agol or should we only edit in our source since our tools will most likely override any modifications? Are there items that are merged instead of overwritten? How does that all work?

That's a good point. We should probably avoid editing the AGOL item pages for any SGID data. The only time it merges/edits instead of overwritting is when it adds the shelved/static notes for those items.

[gregbunce]

wow, yeah, much better than I thought. thanks for doing that so quickly! it looks great.

[gregbunce]

the data page and metadata have been updated to reflect the new layer/info. it looks there's just a few remaining verification checks, @ZachBeck @jacobdadams and then we can close this one out.

next up is deprecating the existing schools and adding the higher education layer. it sounds like zach has reached out on getting the higher ed data.

[steveoh]

Thanks for the update greg. You can create new issues for both of those and we can track progress there

[gregbunce]

i'll get an update at the data team meet today and then get that started.

[agrc-conductor]

conductor results for SOCIETY.Schools_PreKto12

check	status
internal sgid	:+1:
sgid10	:+1:
meta table
- item id	:+1:
- item name	:+1:
open sgid	:+1:
open data	:+1:
arcgis online	:+1:
stewardship
- Website URL	:no_entry:
- Endpoint	:no_entry:
- Description	:+1:
- Data Source	:+1:
- Data Type	:+1:

[steveoh]

@ZachBeck it appears the last two ⛔ are yours? Does that seem correct to you? Do you need anything from the rest of us to finish those up?

[gregbunce]

added website url to change log spreadsheet