Add City, Zip, FIPS, Lat & Lng to Agencies table

[josh-chamberlain]

Goal

We will have to manually go through and add the relevant information to each agency. Once this is all done, it will give front-end devs a way to accurately place a pin for every agency in the US!

This issue can be closed once all agencies have the appropriate data—until then, we will need a lot of contributions.

Process:

• Reference our dolt docs for information / context
• Fork and clone the datasets repo
• Navigate into the cloned directory and start a dolt sql-server (docs here)
• Use a GUI tool like DBeaver or TablePlus for ease of data entry and connect to your localhost server
• Pick a state, I started with Alaska (AK).
• Copy the name of the agency, google their name to get the address of the main office/district
• Copy the address into Google Maps. Copy the city and 5 digit ZIP code into the table
• Right click on the map of the agency to display the coordinates. Copy those into lat, lng (google copies both fyi)
• Use the coordinates here to get the right county_fips
• Repeat for the rest of the agencies in that state, then commit and create a PR in DoltHub!

Update 5/22

There is now a tool here (thank you @ncpierson) which scrapes Geonames for this info.

[florabtw]

I've started attempting an automated way to gather this data using Geonames. You can see my repository here: https://github.com/ncpierson/pdad-agency-locator

I estimate about 30-50% coverage using exact name matching, but geonames data only overs fips, lat, and lng but not address or zip.

First pull request, for CA, is here: https://www.dolthub.com/repositories/pdap/datasets/pulls/31

For what it's worth, I considered using Google's APIs, but it would cost a little over $300 for the 15k requests.

[EricTurner3]

Fantastic work @ncpierson ! I was hoping someone could find a geolocation service, I knew that Google would be expensive. But I'm stoked you already were able to pull a state's worth of data! I merged that PR into our master dolt branch. Good news is, even if there are some discrepancies, someone who is local and actually knows the PD can always make a PR and correct it!

[josh-chamberlain]

@ncpierson just FYI that we have a data bounty going on! Going to close this issue as that's now where people should go to submit data.