2016 Michigan Teams by County

This is the most up-to-date 2016 team numbers by county. It also shows where the district events were. The nice thing about this map is it is linked to the official FiM master team spreadsheet. It will be a lot easier to update the map in the future.

Out of curiosity, is this sort of data available going back a few years? I’d be interested in seeing if an animated graphic could be made that would show the growth of teams over time by area as this does.

Unfortunately the map keeps changing, and it doesn’t look like there is a 2015 map (but http://frclinks.frclinks.com/t/MI-USA/2015 has location info). But here are:

I made a Michigan team map using the data from the First website that shows which events teams go to without the state championship. Some of the team location data doesn’t seem to be correct (They are in a lake).

The team in Lake Michigan looks to be this one from Beaver Island.

Great work, Ed!

Looks like Kalamazoo Central HS would be a good location for a new district sometime soon, based solely on geographic distribution of teams. 6002 ZooBOTix started there this past season, but I don’t see any CD accounts with that team number. Anyone here know someone on that team?

One in Lake Huron is from Mackinac Island, and the other does seem to have a slightly incorrect location (they belong a touch further north).

I am curious exactly where you get the team location data from.

The firstinspires.org team and event search page returns lots of values about the team including the lat and lon. http://es01.usfirst.org/teams/_search

I’ll see if I can find my Michigan team data set
EDIT: Here http://pastebin.com/HVeY9KJh

Is there a way you can share sheet for other regions to use? I would love to do the same thing with Maryland :slight_smile:

It is something we have in spreadsheet forms but have never really put into a map.

There is no magic spreadsheet that you can program to create that map. It is just hours of hard work. Here is basically what you need to do.

  1. You need a list of all zip codes in your area of interest (state/district/region)
  2. You need the corresponding city for each of the zip codes. Some have more than one zip code.
  3. You need a table to show which county each city belongs to.
  4. Then you get all the teams in your area of interest.
  5. Use vlookup function in Excel to find the county for each team.
  6. Use pivot table to tabulate how many teams in each county.
  7. Find a blank map that shows the counties in your area of interest.
  8. Find a map with the name of each county.
  9. Then manually create a text box for each county, put it inside the county on the blank map and link the value from the pivot data to it. This is the time consuming and tedious part.

The good thing is once you create the link. Every time you update the team lists in the future, you only have to refresh the pivot table and you will have a new map. Michigan used to create this map manually in the past by counting. Good thing we are all just crazy volunteers.

I did one for California too which is my new home.

Thanks Ed! I have a spreadsheet that does that. Was wondering if anyone broke the code to have the text boxes auto update. It is the tedious part.

I might be able to do something like this. PM me your spreadsheet and details and Ill see what I can do.