I want to rewrite it using Textual and SQLite. # City data Need to be able to encode: - Portland, OR, US, lat, long - Find Kraków via "Krakow" - Geoname ID - Proper name - Alternative names - First-order administrative division - Country - Lat - Long ## Tables See [[Travelbrag DB schema.canvas|Travelbrag DB schema]] ## Geonames data http://download.geonames.org/export/dump/ ### `allCountries` Probably want to use `cities*.zip` files to cut out all non-city data. - **geonameid** - integer - id of record in geonames database - **name** - name of geographical point - (utf8) varchar(200) - asciiname - name of geographical point in plain ascii characters - varchar(200) - **alternatenames** - alternate names, ascii names automatically transliterated, convenience attribute from alternatename table - comma-separated - varchar(10000) - **latitude** - latitude in decimal degrees - (wgs84) - **longitude** - longitude in decimal degrees - (wgs84) - **feature class** - see http://www.geonames.org/export/codes.html - char(1) - Only care about `P` (populated areas) - feature code - see http://www.geonames.org/export/codes.html - varchar(10) - **country code** - ISO-3166 2-letter country code - 2 characters - cc2 - alternate country codes - comma separated, ISO-3166 2-letter country code - 200 characters - **admin1 code** - fipscode (subject to change to iso code), see exceptions - see file admin1Codes.txt for display names of this code - varchar(20) - Can be `00` which is a placeholder for `NULL` - admin2 code - code for the second administrative division, a county in the US - see file admin2Codes.txt - varchar(80) - admin3 code - code for third level administrative division - varchar(20) - admin4 code - code for fourth level administrative division - varchar(20) - population - bigint - (8 byte int) - elevation - in meters - integer - dem - digital elevation model - srtm3 or gtopo30 - average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters - integer - srtm processed by cgiar/ciat. - timezone - the iana timezone id - see file timeZone.txt - varchar(40) - **modification date** - date of last modification - yyyy-MM-dd format ### `countryInfo.txt` - **ISO** - ISO3 - ISO-Numeric - fips - **Country** - Capital - Area(in sq km) - Population - **Continent** - tld - CurrencyCode - CurrencyName - Phone - Postal Code Format - Postal Code Regex - Languages - **geonameid** - neighbours - EquivalentFipsCode ### `admin1CodesASCII.txt` - **code** - Formatted as `".".join([ISO-3166 alpha-2, admin1 code]) - **name** - name ascii - **geonameid** # Trips data [[Travelbrag DB schema.canvas|Travelbrag DB schema]] ## Trip - Title - Start date - Starting place - End date - Ending place - Comment ## Leg of trip - Place - Arrived - Left - Trip ## Household members - Name ## Traveler - Household member - Trip # Example data - [Canto Bight](https://starwars.fandom.com/wiki/Canto_Bight), [Cantonica](https://starwars.fandom.com/wiki/Cantonica) - [Cloud City](https://starwars.fandom.com/wiki/Cloud_City), [Bespin](https://starwars.fandom.com/wiki/Bespin) - [Imperial City](https://starwars.fandom.com/wiki/Imperial_City), [Coruscant](https://starwars.fandom.com/wiki/Coruscant), Coruscant - Galactic City - Republic City - [Lessu City](https://starwars.fandom.com/wiki/Lessu_City), [Ryloth](https://starwars.fandom.com/wiki/Ryloth) - Lessu - [Mos Eisley](https://starwars.fandom.com/wiki/Mos_Eisley), [Great Mesra Plateau](https://starwars.fandom.com/wiki/Great_Mesra_Plateau), [Tatooine](https://starwars.fandom.com/wiki/Tatooine) - [Republic City](https://starwars.fandom.com/wiki/Republic_City), [Hosnian Prime](https://starwars.fandom.com/wiki/Hosnian_Prime) - [Tipoca City](https://starwars.fandom.com/wiki/Tipoca_City), [Kamino](https://starwars.fandom.com/wiki/Kamino) - [Theed](https://starwars.fandom.com/wiki/Theed), [Naboo](https://starwars.fandom.com/wiki/Naboo)