Address Clean Up

[Stacy Scott]

Hey everyone,

Per the conversation I'm attaching some SQL queries that I use to clean up my addresses in Campus. I'm also including directions on how I assign longitude/latitude to our addresses. Please let me know if any of you have questions. If anyone is interested, I'm also willing to export the addresses I have set up in our system that you all could import into your own if you think you'd find that helpful. This could be all of the addresses or just the ones I've identified for your district/s. 

Task Manager

Main Menu > System Settings > System Processes > Task Scheduler

I have the job set up to run daily at 4:30 AM.

Files Attached:

Task manager - Residence District.txt - the query I use on Task Scheduler to update the student enrollments. You will need to update the districtID to be your district's information.

Address Clean Up

So once a month I go through and clean up our addresses. This includes fixing the formatting, getting rid of duplicates, and assigning district and school boundaries. I also have a script set up on Task Scheduler that runs daily to assign a student's home district on their enrollment record if it is not our district. I'll include that query as well.

• Clean up - Address 1 Format
• I run this one first. I go through and make sure the addresses all have the correct information in the correct fields
• Clean up - Address 2 Case
• I run this one second. I go through and make sure the addresses all have the correct case formatting. I have our district set up so if the address is in district it's all in caps. If it's out of district or not a physical address it's in proper case.
• Clean up - Address 3 County
• This is my third step. We have 2 counties in our district so based on what town the address is in I assign one of the 2 counties. If it's not in those counties I make sure the field is NULL.
• Clean up - Address 4 Duplicates
• This is where most of the time is spent. I have 2 queries. The first looks at the full address and the 2nd looks at just the first part of the address.
• I go into Campus to review the addresses and determine if they are actually duplicates or if they are different. If they're the same I get the households moved over to the one I want to keep and delete the duplicate.
• This isn't a query, but I do manually look up each address that doesn't have a district assigned on the KSDE arcgis site to identify the district it's in and get it assigned. 
• https://www.arcgis.com/home/webmap/viewer.html?webmap=453a26ce52704907af648b6568d6fb3d&extent=-101.421,36.9528,-94.6754,40.211
• On the above address you can search in the top right of the screen for the address you're looking for. Once found just click on the map near the address to see the district number.
• Clean up - Address 5 437 but no schools
• This one I use to pull up a list of addresses that I've assigned to our district. I then use our transportation system to determine which schools the address should be assigned to (rarely I have to ask them to help me figure it out). Then I add them to dbo.SchoolBoundary. On the SchoolBoundary I have ours set up so if they are in district they have a start date, no end date, and I mark the primary column as True.
• For the OLR we do add school boundaries for out of district addresses for approved families. For these addresses I enter the approved school with a start date, an end date of 02/01/YYYY where YYYY is the end year of the school year they're approved for. I also mark the primary column as False. This allows the family to complete the OLR, it gives us something in Campus we can look at for their approved schools, and helps us keep track of where people live that are requesting out of district attendance from.

Longitude/Latitude

The way I handle longitude/latitude is I use Google Sheets and SQL. I'll attach the SQL queries I use for this as well.

• In Google
• Create a Google sheet called "longitude latitude"
• In row 1 set up your headings. I recommend addressID, address, Latitude, Longitude
• Under the Extensions menu select Add-ons > Get Add-ons
• Search for Geocode by Awesome Table and install it
• In SQL Management Studio
• Create a new table. I called mine usr_longlat
• Set up the same headings you did in Google
• addressID - int
• address - varchar(100)
• latitude - decimal(9,6)
• longitude - decimal(9,6)
• Run the attached query (clean up - address 7 longitude latitude.txt)
• Copy and paste the results into your Google sheet. Remove any NULL values
• In the Google sheet click the Extensions menu and select Geocode by Awesome Sites > Start Geocoding
• Set the Address column to address
• Click Geocode!
• Note that there is a limit to the number of addresses that can be done in a day so you may need to split this process up into multiple days when you first do it.
• Copy and paste everything into your SQL table
• Run the attached query (Update - Longitude Latitude.txt) to update the Address table.
• You may need to comment out some fields or adjust the addressIDs listed to be ignored. We have some that are international or military base mailing addresses and I didn't want to deal with figuring them out.

If you have questions or want to see it in action I can set up a time with you to look at it. Or like we did today I'm willing to hijack a meeting ^_^

Thanks,
Stacy N. Scott
Auburn-Washburn USD 437
Network Specialist
785.339.4020

"Have you checked the wiki?" - http://wiki.usd437.net

[Durden Keathley]

Thanks Stacy!

--
You received this message because you are subscribed to the Google Groups "Kansas Infinite Campus User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to infinitecampu...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/infinitecampus/CAOmcg1Wq4SxF92KrTuByeX-J7urN9K7Xw62VyitXH9ZWX2BMzw%40mail.gmail.com.

--

Durden Keathley

District Data Coordinator

Technology Department

Parsons District Schools

Cell: 417-631-3554

Office: 620-423-8809 x710

*** This email was sent by a Staff Member of USD 503 Parsons District Schools