Open jfly opened 8 years ago
I don't remember a name, but I have used a Gem that has has all the names and codes for countries and states across the planet.
Here's a query (last run on Tue Mar 14 00:04:47 UTC 2017) for US competitions whose cityName
field doesn't end in ", STATE":
irb(main):001:0> states = ['Alabama', 'Alaska', 'Arizona', 'Arkansas', 'California', 'Colorado', 'Connecticut', 'Delaware', 'Florida', 'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi', 'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New Hampshire', 'New Jersey', 'New Mexico', 'New York', 'North Carolina', 'North Dakota', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania', 'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'Vermont', 'Virginia', 'Washington', 'West Virginia', 'Wisconsin', 'Wyoming']; Competition.visible.where(countryId: "USA").reject { |c| states.any? { |state| c.cityName.end_with?(", #{state}") } }.map(&:id)
Competition Load (8.2ms) SELECT `Competitions`.* FROM `Competitions` WHERE `Competitions`.`countryId` = 'USA'
=> ["AZCubingFall2016", "AZCubingSpring2017", "AZCubingWinter2017", "BigCubeBash2017", "CliftonWinter2017", "FMCUSA2014", "FMCUSA2015", "FMCUSA2016", "HowlandHighSpring2017", "RockvilleWinter2017"]
Competition Load (15.1ms) SELECT `Competitions`.* FROM `Competitions` WHERE `Competitions`.`showAtAll` = 1 AND `Competitions`.`countryId` = 'USA'
=> ["AZCubingFall2016", "AZCubingSpring2017", "AZCubingWinter2017", "BigCubeBash2017", "CliftonWinter2017", "CrossroadsCubingSpring2017", "CubingwiththeDuck2017", "FMCUSA2014", "FMCUSA2015", "FMCUSA2016", "LongmontSummer2017", "RockvilleWinter2017", "UPennCubeDayFall2017"]
Also worth mentioning that if Puerto Rico is removed as a country, then it needs to get handled here somehow.
@jfly As your cityvalidator has been merged, can this ticket be closed?
I'd like to keep this open until we've implemented validators for all the countries we want to add validators for.
Right now we use the cityName field to store
city, state
information in the US. Canada does the same thing withcity, province
.Adding a validation for this should be fairly straightforward. I fear that adding a new UI for it might just be annoying (both for us to implement, and for people to use).
I don't know where we're supposed to get a list of states/provinces, nor do I know which countries would want to use this feature. I'm also unsure if it would ultimately be better to make this a separate database field.
Update (2018-09-04): See email thread titled "Re: Standardising and Removing Duplicate City Names". Support for countries is being added one at a time. See status here: https://docs.google.com/spreadsheets/d/1-xOQjV2ar4jVoHgHj0Egmz6o6QRv82kkyFKd-lpo1GM/edit#gid=1687057286.