[Sprint 4c]: Geocoding School Names 🌎 - Zander, Hansini & Steven

[danglorioso]

💻 Summary

For this sprint, you’ll implement geocoding school names (matching school names to a geographic location via longitude and latitude coordinates). This is going to be essential for showing accurate heatmap data once the heatmap is constructed. You will be assigning longitude and latitude coordinates for existing schools in the database as well as automatically populating longitude and latitude for when new schools are uploaded.

Goals of this sprint:

• Assign existing schools their corresponding longitude and latitude coordinates.
• Create logic for assigning newly-uploaded schools their coordinates.
• Add error handling (renaming the school in the CSV) for when a school cannot be found in the existing dataset.

⚙️ Setup

Before starting:

1. Pull the latest changes from main.
2. Create a new branch for this sprint.
3. Install dependencies.
4. Run the dev server and verify the site builds.
5. Confirm everything runs.
   If you experience problems, check your .env is up to date!

🧭 Implementation

Step 1: Find the school dataset in public/MA_schools_long_lat.csv

• Originally, we wanted to use an existing public API for finding school coordinates by searching via school name. Unfortunately, the options that we tried either did not function well enough for our uses or were unreasonably involved (thanks GCP 😐).
• So, we manually ingested a dataset into the public folder of our project that you will use to search for a school’s coordinates based on their name.

Step 2: Label existing data with coordinates

• In the first part of this sprint, you will have to go to the database and add the longitude and latitude coordinates for each existing school. Make sure that you document the order ([long, lat] or [lat, long]) in the code and in your PR bc it will save everyone a ton of time.

Step 3: Add coordinate-association logic to upload flow

• Next, you will add logic to the upload flow that handles when a new school is added. When this occurs, you should query the CSV file to find the corresponding school and store its coordinates in the database, along with the uploaded school’s information. IF YOU QUERY THE CSV AND CANNOT FIND THE SCHOOL, PLEASE DO A MANUAL SEARCH ON THE CSV FILE AND CHANGE THE NAME IN THE CSV TO MATCH THE ONE BEING UPLOADED.
• To determine if a school already exists in the database, you can query the db using the schoolId column in the uploaded spreadsheet. If results are returned, then you know the school is already in the database. If not, then you will have to associate the school with its coordinates.
  • Paula has stated that the school names in the uploaded data are standardized (participants select from a dropdown), so the school name in the provided data stands. Once we update the CSV to match the uploaded school name, we (in theory) will never have to change them again. Let us know if you need more clarification.

🙌 Acceptance Criteria

• Upon upload, non-pre-existing schools are assigned an address from the MA schools dataset
• Schools with similar names in different towns are tested and can be distinguished
• All school names in the CSV match their counterparts in the spreadsheets from Paula.
• Tell Dan & Shayne the order of you longitude and latitude pairings: [lat, long] or [long, lat]
• Code runs locally without errors and builds successfully
• Merge via PR

⁉️ Questions

If you get stuck or have questions:

• DM Dan or Shayne on Slack
• Come to office hours or ask during a hack night
• Ask questions during team meetings – we’re here to help!

📝 Resources

• Figma:
• Tailwind CSS
• shadcn/ui
• npm package manager