Design Country model/helper

[krzysztofrewak]

General idea; every country:

• should have unique slug, internal private uuid, name and code (ISO 3166 would be fine)
• countries should have aggregate breweries
• countries should have been seeded from static file (seed/resource/whatever)
• bonus point: what should we do with some autonomous/overseas regions like Aland Islands, Greenland or Martinique and exotic things like Transnistria?

@mlencki suggested:

Maybe, rather than creating separate countries table and storing country_ids we should store more general set of "geolocalization_features" for each brewery.
So each of brewery could have its own set of "geolocalization_features" stored in key, value manner (e.g. 'coordinates' => '...', city => 'Legnica', 'country' => 'pl', 'region' => 'Lower Silesian Voivodeship', 'continent' => 'Europe', 'address' => 'Ulica Dworcowa 9, 59-220 Legnica, Lower Silesian Voivodeship, Poland')
So selecting all breweries inside the country would be something like:
breweries->where('geolocalization_features.key', 'country')->where('geolocalization_features.value', 'pl')
This could be much more flexible so we could filter breweries not only by country but also by city, region or any other feature we can think of.
Also we could think about using some Geocoding API to fill these features automatically.
So e.g. if someone will add a new brewery {'name' => 'Browar Książęcy', 'coordinantes' => [16.168,51.212], 'address'...} it will fill the rest of "geolocalization_features" automatically.
This could be made in background in some queue or something. However, we should be able to edit these features manually in some admin panel.
Also, I don't see much benefit in storing all countries of the world in the database. How many times a year country changes its name or new one declares independence? I don't think there are lots of breweries in such places :)

Also:

with "geolocalization_features" approach we could store two records for single brewery:
{ id: 1, key: "country", value: "Moldova", brewery_id: 1}
{ id: 2, key: "country", value: "Transnistria", brewery_id: 1}

[HerringTheCoder]

I think GeoJSON standard matches the latter case pretty well (or maybe that's what @mlencki already had in his mind) https://geojson.org/
It's also officialy supported by MySQL along with some fancy db functions:
https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html

According to GeoJSON standard I`d imagine the location_features to look somewhat like this:

{
"type": "FeatureCollection",
"features":  [
 {
 "type": "Feature",
 "geometry" {
 "type": "Point",
 "coordinates": [56.5673, 54.3443]
 },
 "properties":{
 "country": "Moldavia",
"localized_name": "fabrică de bere"
 }
 },
 "type": "Feature"
 "geometry" {
 "type": "Point",
 "coordinates": [56.5673, 54.3443]
 },
 "properties":{
 "country": "Transnistria"
 "localized_name" : "пивоварня"
 }
}
]
}

Or as a single feature with an array of countries if country is the only thing to be stored.

In this approach a troublesome brewery could have multiple contexts for the same geographic collection, ability to put any spatial data in feature (Multilines, Polygons and such), while staying away from a bit hard to maintain GIS standard (binary format, requires raw SQL inserting or 3rd party packages/custom parsers).