Travelbrag - Open Source by Brett Cannon

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" ## Tables See [[Travelbrag DB schema.canvas|Travelbrag DB schema]] ## Geonames ### Gazetteer 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** ### Web service https://www.geonames.org/export/web-services.html https://www.geonames.org/export/geonames-search.html #### Examples ##### Searching ```shell http http://api.geonames.org/search type==json name_startsWith=="montreal" country==ca featureClass==P lang==en searchlang==en username==<username> ``` ```json { "totalResultsCount": 5, "geonames": [ { "adminCode1": "10", "lng": "-73.58781", "geonameId": 6077243, "toponymName": "Montréal", "countryId": "6251999", "fcl": "P", "population": 1762949, "countryCode": "CA", "name": "Montreal", "fclName": "city, village,...", "adminCodes1": { "ISO3166_2": "QC" }, "countryName": "Canada", "fcodeName": "seat of a second-order administrative division", "adminName1": "Quebec", "lat": "45.50884", "fcode": "PPLA2" }, { "adminCode1": "10", "lng": "-73.5075", "geonameId": 6077251, "toponymName": "Montréal-Est", "countryId": "6251999", "fcl": "P", "population": 3850, "countryCode": "CA", "name": "Montreal East", "fclName": "city, village,...", "adminCodes1": { "ISO3166_2": "QC" }, "countryName": "Canada", "fcodeName": "populated place", "adminName1": "Quebec", "lat": "45.63202", "fcode": "PPL" }, { "adminCode1": "10", "lng": "-73.64918", "geonameId": 6077265, "toponymName": "Montréal-Ouest", "countryId": "6251999", "fcl": "P", "population": 5184, "countryCode": "CA", "name": "Montreal West", "fclName": "city, village,...", "adminCodes1": { "ISO3166_2": "QC" }, "countryName": "Canada", "fcodeName": "populated place", "adminName1": "Quebec", "lat": "45.45286", "fcode": "PPL" }, { "adminCode1": "11", "lng": "-105.76766", "geonameId": 6077260, "toponymName": "Montreal Lake", "countryId": "6251999", "fcl": "P", "population": 0, "countryCode": "CA", "name": "Montreal Lake", "fclName": "city, village,...", "adminCodes1": { "ISO3166_2": "SK" }, "countryName": "Canada", "fcodeName": "populated locality", "adminName1": "Saskatchewan", "lat": "54.0501", "fcode": "PPLL" }, { "adminCode1": "08", "lng": "-84.64992", "geonameId": 6077271, "toponymName": "Montreal River", "countryId": "6251999", "fcl": "P", "population": 0, "countryCode": "CA", "name": "Montreal River", "fclName": "city, village,...", "adminCodes1": { "ISO3166_2": "ON" }, "countryName": "Canada", "fcodeName": "populated place", "adminName1": "Ontario", "lat": "47.23337", "fcode": "PPL" } ] } ``` ##### Data style ```shell https http://secure.geonames.org/search type==json username==<username> name_startsWith=="montreal west" country==ca featureClass==P lang==en searchlang==en style==<SHORT|MEDIUM|LONG|FULL> ``` ###### Short ```json { "totalResultsCount": 1, "geonames": [ { "lng": "-73.64918", "geonameId": 6077265, "countryCode": "CA", "name": "Montreal West", "toponymName": "Montréal-Ouest", "lat": "45.45286", "fcl": "P", "fcode": "PPL" } ] } ``` ###### Medium ```json { "totalResultsCount": 1, "geonames": [ { "adminCode1": "10", "lng": "-73.64918", "geonameId": 6077265, "toponymName": "Montréal-Ouest", "countryId": "6251999", "fcl": "P", "population": 5184, "countryCode": "CA", "name": "Montreal West", "fclName": "city, village,...", "adminCodes1": { "ISO3166_2": "QC" }, "countryName": "Canada", "fcodeName": "populated place", "adminName1": "Quebec", "lat": "45.45286", "fcode": "PPL" } ] } ``` ###### Long Same as MEDIUM? ```json { "totalResultsCount": 1, "geonames": [ { "adminCode1": "10", "lng": "-73.64918", "geonameId": 6077265, "toponymName": "Montréal-Ouest", "countryId": "6251999", "fcl": "P", "population": 5184, "countryCode": "CA", "name": "Montreal West", "fclName": "city, village,...", "adminCodes1": { "ISO3166_2": "QC" }, "countryName": "Canada", "fcodeName": "populated place", "adminName1": "Quebec", "lat": "45.45286", "fcode": "PPL" } ] } ``` ###### Full ```json { "totalResultsCount": 1, "geonames": [ { "timezone": { "gmtOffset": -5, "timeZoneId": "America/Toronto", "dstOffset": -4 }, "bbox": { "east": -73.6320293899361, "south": 45.44669264143924, "north": 45.45953533015124, "west": -73.66856578500955, "accuracyLevel": 10 }, "asciiName": "Montreal-Ouest", "astergdem": 47, "countryId": "6251999", "fcl": "P", "srtm3": 48, "score": 30.619850158691406, "adminId2": "6077246", "adminId3": "8674075", "countryCode": "CA", "adminCodes1": { "ISO3166_2": "QC" }, "adminId1": "6115047", "lat": "45.45286", "fcode": "PPL", "continentCode": "NA", "adminCode2": "06", "adminCode3": "66047", "adminCode1": "10", "lng": "-73.64918", "geonameId": 6077265, "toponymName": "Montréal-Ouest", "population": 5184, "adminName5": "", "adminName4": "", "adminName3": "Montreal West", "alternateNames": [ { "name": "Montreal West", "lang": "en" }, { "name": "https://sv.wikipedia.org/wiki/Montr%C3%A9al-Ouest_(ort)", "lang": "link" }, { "name": "CA8MT", "lang": "unlc" }, { "name": "Q22368804", "lang": "wkdt" } ], "adminName2": "Montréal", "name": "Montreal West", "fclName": "city, village,...", "countryName": "Canada", "fcodeName": "populated place", "adminName1": "Quebec" } ] } ``` # Trips data [[Travelbrag DB schema.canvas|Travelbrag DB schema]] ## Trip - Title - Start date - End date - 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)