In this 3-part post, I'm going to show how you can take CIA World Factbook data and use it for your own purposes on Microsoft Azure. In Part 1 we retrieved the public-domain data and loaded it into a Cosmos DB using Azure Durable Functions. In Part 2 we created an API using Azure Functions and Cosmos DB, and a web site that uses the API. Today in Part 3, we'll add analytics to the web site in the form of charts. To implement charts we'll be using Cosmos DB, Azure Functions, Azure API Management, and Google Charts.
When we started this project, one of the justifications for storing the data in Cosmos DB instead of blob storage was search, which was implemented in Part 2. The other was analytics, so that we could query the data in various ways in order to support charts and reporting. That's our focus today.
What We're Building
Here's what we'll end up with after today's activities. You can visit http://world-factbook.davidpallmann.com/ to see it in action.
Our actitivies today will include the following:
- Figuring out our Cosmos DB chart queries
- Writing new Azure Functions for retrieving chart data
- Mapping the chart functions to new API operations
- Adding a chart section to the web site
- Add more country data to the web site (including national anthem audio)
- Making usability improvements to the web site
Let's get to work!
Cosmos DB Chart Queries
You'll recall from Part 1 that we are retrieving the World Factbook data, which is public-domain, and storing it in Cosmos DB in a Country collection. Below is a JSON country record.{
"name": "Afghanistan",
"introduction": {
"background": "Ahmad Shah DURRANI unified the Pashtun tribes and founded Afghanistan in 1747. The country served as a buffer between the British and Russian Empires until it won independence from notional British control in 1919. A brief experiment in democracy ended in a 1973 coup and a 1978 communist countercoup. The Soviet Union invaded in 1979 to support the tottering Afghan communist regime, touching off a long and destructive war. The USSR withdrew in 1989 under relentless pressure by internationally supported anti-communist mujahidin rebels. A series of subsequent civil wars saw Kabul finally fall in 1996 to the Taliban, a hardline Pakistani-sponsored movement that emerged in 1994 to end the country's civil war and anarchy. Following the 11 September 2001 terrorist attacks, a US, Allied, and anti-Taliban Northern Alliance military action toppled the Taliban for sheltering Usama BIN LADIN.\nA UN-sponsored Bonn Conference in 2001 established a process for political reconstruction that included the adoption of a new constitution, a presidential election in 2004, and National Assembly elections in 2005. In December 2004, Hamid KARZAI became the first democratically elected president of Afghanistan, and the National Assembly was inaugurated the following December. KARZAI was reelected in August 2009 for a second term. The 2014 presidential election was the country's first to include a runoff, which featured the top two vote-getters from the first round, Abdullah ABDULLAH and Ashraf GHANI. Throughout the summer of 2014, their campaigns disputed the results and traded accusations of fraud, leading to a US-led diplomatic intervention that included a full vote audit as well as political negotiations between the two camps. In September 2014, GHANI and ABDULLAH agreed to form the Government of National Unity, with GHANI inaugurated as president and ABDULLAH elevated to the newly-created position of chief executive officer. The day after the inauguration, the GHANI administration signed the US-Afghan Bilateral Security Agreement and NATO Status of Forces Agreement, which provide the legal basis for the post-2014 international military presence in Afghanistan. The next presidential election is scheduled for April 2019.\nThe Taliban remains a serious challenge for the Afghan Government in almost every province. The Taliban still considers itself the rightful government of Afghanistan, and it remains a capable and confident insurgent force fighting for the withdrawal of foreign military forces from Afghanistan, establishment of sharia law, and rewriting of the Afghan constitution.\nAFGHANISTAN SUMMARY: PDF"
},
"geography": {
"location": "Southern Asia, north and west of Pakistan, east of Iran",
"geographic_coordinates": {
"latitude": {
"degrees": 33,
"minutes": 0,
"hemisphere": "N"
},
"longitude": {
"degrees": 65,
"minutes": 0,
"hemisphere": "E"
}
},
"map_references": "Asia",
"area": {
"total": {
"value": 652230,
"units": "sq km"
},
"land": {
"value": 652230,
"units": "sq km"
},
"water": {
"value": 0,
"units": "sq km"
},
"global_rank": 42,
"comparative": "almost six times the size of Virginia; slightly smaller than Texas"
},
"land_boundaries": {
"total": {
"value": 5987,
"units": "km"
},
"border_countries": [
{
"country": "China",
"border_length": {
"value": 91,
"units": "km"
}
},
{
"country": "Iran",
"border_length": {
"value": 921,
"units": "km"
}
},
{
"country": "Pakistan",
"border_length": {
"value": 2670,
"units": "km"
}
},
{
"country": "Tajikistan",
"border_length": {
"value": 1357,
"units": "km"
}
},
{
"country": "Turkmenistan",
"border_length": {
"value": 804,
"units": "km"
}
},
{
"country": "Uzbekistan",
"border_length": {
"value": 144,
"units": "km"
}
}
]
},
"coastline": {
"value": 0,
"units": "km",
"note": "landlocked"
},
"climate": "arid to semiarid; cold winters and hot summers",
"terrain": "mostly rugged mountains; plains in north and southwest",
"elevation": {
"mean_elevation": {
"value": 1884,
"units": "m",
"note": "258 m"
},
"lowest_point": "Amu Darya",
"7492_highest_point": "Noshak"
},
"natural_resources": {
"resources": [
"natural gas",
"petroleum",
"coal",
"copper",
"chromite",
"talc",
"barites",
"sulfur",
"lead",
"zinc",
"iron ore",
"salt",
"precious",
"semiprecious stones",
"arable land"
]
},
"land_use": {
"by_sector": {
"agricultural_land_total": {
"value": 58.1,
"units": "%"
},
"arable_land": {
"value": 11.8,
"units": "%",
"note": "/",
"date": "2016"
},
"permanent_crops": {
"value": 0.3,
"units": "%",
"note": "/",
"date": "2016"
},
"permanent_pasture": {
"value": 46,
"units": "%",
"date": "2016"
},
"forest": {
"value": 2.07,
"units": "%"
},
"other": {
"value": 39,
"units": "%",
"date": "2016"
}
},
"date": "2016"
},
"irrigated_land": {
"value": 32080,
"units": "sq km",
"date": "2012"
},
"population_distribution": "populations tend to cluster in the foothills and periphery of the rugged Hindu Kush range; smaller groups are found in many of the country's interior valleys; in general, the east is more densely settled, while the south is sparsely populated",
"natural_hazards": [
{
"description": "damaging earthquakes occur in Hindu Kush mountains",
"type": "hazard"
},
{
"description": "flooding",
"type": "hazard"
},
{
"description": "droughts",
"type": "hazard"
}
],
"environment": {
"current_issues": [
"limited natural freshwater resources",
"inadequate supplies of potable water",
"soil degradation",
"overgrazing",
"deforestation (much of the remaining forests are being cut down for fuel and building materials)",
"desertification",
"air and water pollution in overcrowded urban areas"
],
"international_agreements": {
"party_to": [
"Biodiversity",
"Climate Change",
"Desertification",
"Endangered Species",
"Environmental Modification",
"Marine Dumping",
"Ozone Layer Protection"
],
"signed_but_not_ratified": [
"Hazardous Wastes",
"Law of the Sea",
"Marine Life Conservation"
]
}
}
},
"people": {
"population": {
"total": 34940837,
"global_rank": 39,
"date": "2018-07-01"
},
"nationality": {
"noun": "Afghan(s)",
"adjective": "Afghan"
},
"ethnic_groups": {
"ethnicity": [
{
"name": "Pashtun"
},
{
"name": "Tajik"
},
{
"name": "Hazara"
},
{
"name": "Uzbek"
},
{
"name": "other",
"note": "includes smaller numbers of Baloch, Turkmen, Nuristani, Pamiri, Arab, Gujar, Brahui, Qizilbash, Aimaq, Pashai, and Kyrghyz"
}
],
"note": "current statistical data on the sensitive subject of ethnicity in Afghanistan are not available, and ethnicity data from small samples of respondents to opinion polls are not a reliable alternative; Afghanistan's 2004 constitution recognizes 14 ethnic groups: Pashtun, Tajik, Hazara, Uzbek, Baloch, Turkmen, Nuristani, Pamiri, Arab, Gujar, Brahui, Qizilbash, Aimaq, and Pashai",
"date": "2015"
},
"languages": {
"language": [
{
"name": "Afghan Persian or Dari",
"percent": 80,
"note": "official; Dari functions as the lingua franca"
},
{
"name": "Pashto",
"percent": 47,
"note": "official"
},
{
"name": "Uzbek",
"percent": 11
},
{
"name": "English",
"percent": 5
},
{
"name": "Turkmen",
"percent": 2
},
{
"name": "Urdu",
"percent": 2
},
{
"name": "Pashayi",
"percent": 1
},
{
"name": "Nuristani",
"percent": 1
},
{
"name": "Arabic",
"percent": 1
},
{
"name": "Balochi"
},
{
"name": "Shughni"
},
{
"name": "Pamiri"
},
{
"name": "Hindi"
},
{
"name": "Russian"
},
{
"name": "German"
},
{
"name": "French"
}
],
"note": "data represent most widely spoken languages; shares sum to more than 100% because there is much bilingualism in the country and because respondents were allowed to select more than one language\nthe Turkic languages Uzbek and Turkmen, as well as Balochi, Pashayi, Nuristani, and Pamiri are the third official languages in areas where the majority speaks them",
"date": "2017"
},
"religions": {
"religion": [
{
"name": "Muslim",
"percent": 99.7,
"breakdown": [
{
"name": "Sunni",
"percent": 84.7
},
{
"name": "Shia",
"percent": 10
}
]
},
{
"name": "other",
"percent": 0.3
}
],
"date": "2009"
},
"age_structure": {
"0_to_14": {
"percent": 40.92,
"males": 7263716,
"females": 7033427
},
"15_to_24": {
"percent": 21.85,
"males": 3883693,
"females": 3749760
},
"25_to_54": {
"percent": 30.68,
"males": 5456305,
"females": 5263332
},
"55_to_64": {
"percent": 3.95,
"males": 679766,
"females": 699308
},
"65_and_over": {
"percent": 2.61,
"males": 420445,
"females": 491085
},
"date": "2018"
},
"dependency_ratios": {
"ratios": {
"total_dependency_ratio": {
"value": 88.8,
"units": "%"
},
"youth_dependency_ratio": {
"value": 84.1,
"units": "%"
},
"elderly_dependency_ratio": {
"value": 4.7,
"units": "%"
},
"potential_support_ratio": {
"value": 21.2,
"units": "%"
}
},
"date": "2015"
},
"median_age": {
"total": {
"value": 19,
"units": "years"
},
"male": {
"value": 19,
"units": "years"
},
"female": {
"value": 19.1,
"units": "years"
},
"global_rank": 203,
"date": "2018"
},
"population_growth_rate": {
"growth_rate": 2.37,
"global_rank": 29,
"date": "2018"
},
"birth_rate": {
"births_per_1000_population": 37.5,
"global_rank": 12,
"date": "2018"
},
"death_rate": {
"deaths_per_1000_population": 13.2,
"global_rank": 9,
"date": "2018"
},
"net_migration_rate": {
"migrants_per_1000_population": -0.9,
"global_rank": 134,
"date": "2017"
},
"population_distribution": "populations tend to cluster in the foothills and periphery of the rugged Hindu Kush range; smaller groups are found in many of the country's interior valleys; in general, the east is more densely settled, while the south is sparsely populated",
"urbanization": {
"urban_population": {
"value": 25.5,
"units": "%",
"date": "2018"
},
"rate_of_urbanization": {
"value": 3.37,
"units": "%"
}
},
"major_urban_areas": {
"places": [
{
"place": "Kabul",
"population": 4011999.9999999995,
"is_capital": true
}
],
"date": "2018"
},
"sex_ratio": {
"by_age": {
"at_birth": {
"value": 1.04,
"units": "males/female"
},
"0_to_14_years": {
"value": 1.03,
"units": "males/female"
},
"15_to_24_years": {
"value": 1.04,
"units": "males/female"
},
"25_to_54_years": {
"value": 1.04,
"units": "males/female"
},
"55_to_64_years": {
"value": 0.97,
"units": "males/female"
},
"65_years_and_over": {
"value": 0.86,
"units": "males/female"
}
},
"total_population": {
"value": 1.03,
"units": "males/female"
},
"date": "2017"
},
"mothers_mean_age_at_first_birth": {
"age": 19.9,
"date": "2015"
},
"maternal_mortality_rate": {
"deaths_per_100k_live_births": 396,
"global_rank": 28,
"date": "2015"
},
"infant_mortality_rate": {
"total": {
"value": 108.5,
"units": "deaths_per_1000_live_births"
},
"male": {
"value": 115.7,
"units": "deaths_per_1000_live_births"
},
"female": {
"value": 100.9,
"units": "deaths_per_1000_live_births"
},
"global_rank": 1,
"date": "2018"
},
"life_expectancy_at_birth": {
"total_population": {
"value": 52.1,
"units": "years"
},
"male": {
"value": 50.6,
"units": "years"
},
"female": {
"value": 53.6,
"units": "years"
},
"global_rank": 223,
"date": "2018"
},
"total_fertility_rate": {
"children_born_per_woman": 5.02,
"global_rank": 11,
"date": "2018"
},
"contraceptive_prevalence_rate": {
"value": 22.5,
"units": "%",
"date": "2015"
},
"health_expenditures": {
"percent_of_gdp": 8.2,
"global_rank": 52,
"date": "2014"
},
"physicians_density": {
"physicians_per_1000_population": 0.3,
"date": "2016"
},
"hospital_bed_density": {
"beds_per_1000_population": 0.5,
"date": "2014"
},
"drinking_water_source": {
"improved": {
"urban": {
"value": 78.2,
"units": "percent of population"
},
"rural": {
"value": 47,
"units": "percent of population"
},
"total": {
"value": 55.3,
"units": "percent of population"
}
},
"unimproved": {
"urban": {
"value": 21.8,
"units": "percent of population"
},
"rural": {
"value": 53,
"units": "percent of population"
},
"total": {
"value": 44.7,
"units": "percent of population"
}
},
"date": "2015"
},
"sanitation_facility_access": {
"improved": {
"urban": {
"value": 45.1,
"units": "percent of population"
},
"rural": {
"value": 27,
"units": "percent of population"
},
"total": {
"value": 31.9,
"units": "percent of population"
}
},
"unimproved": {
"urban": {
"value": 54.9,
"units": "percent of population"
},
"rural": {
"value": 73,
"units": "percent of population"
},
"total": {
"value": 68.1,
"units": "percent of population"
}
},
"date": "2015"
},
"hiv_aids": {
"adult_prevalence_rate": {
"percent_of_adults": 0.1,
"date": "2016"
},
"people_living_with_hiv_aids": {
"total": 7500,
"global_rank": 109,
"date": "2016"
},
"deaths": {
"total": 500,
"date": "2016"
}
},
"major_infectious_diseases": {
"degree_of_risk": "intermediate",
"food_or_waterborne_diseases": [
"bacterial diarrhea",
"hepatitis A",
"typhoid fever"
],
"vectorborne_diseases": [
"malaria"
],
"date": "2016"
},
"adult_obesity": {
"percent_of_adults": 5.5,
"global_rank": 176,
"date": "2016"
},
"underweight_children": {
"percent_of_children_under_the_age_of_five": 25,
"global_rank": 16,
"date": "2013"
},
"education_expenditures": {
"percent_of_gdp": 3.2,
"global_rank": 138,
"date": "2015"
},
"literacy": {
"definition": "age 15 and over can read and write",
"total_population": {
"value": 38.2,
"units": "%"
},
"male": {
"value": 52,
"units": "%"
},
"female": {
"value": 24.2,
"units": "%"
},
"date": "2015"
},
"school_life_expectancy": {
"total": {
"value": 11,
"units": "years"
},
"male": {
"value": 13,
"units": "years"
},
"female": {
"value": 8,
"units": "years"
},
"date": "2014"
}
},
"government": {
"country_name": {
"conventional_long_form": "Islamic Republic of Afghanistan",
"conventional_short_form": "Afghanistan",
"local_long_form": "Jamhuri-ye Islami-ye Afghanistan",
"local_short_form": "Afghanistan",
"former": "Republic of Afghanistan",
"etymology": "the name \"Afghan\" originally referred to the Pashtun people (today it is understood to include all the country's ethnic groups), while the suffix \"-stan\" means \"place of\" or \"country\"; so Afghanistan literally means the \"Land of the Afghans\""
},
"government_type": "presidential Islamic republic",
"capital": {
"name": "Kabul",
"geographic_coordinates": {
"latitude": {
"degrees": 34,
"minutes": 31,
"hemisphere": "N"
},
"longitude": {
"degrees": 69,
"minutes": 11,
"hemisphere": "E"
}
},
"time_difference": {
"timezone": 4.5,
"note": "9.5 hours ahead of Washington, DC, during Standard Time"
},
"daylight_saving_time": "does not observe daylight savings time",
"etymology": "named for the Kabul River, but the river's name is of unknown origin"
},
"independence": {
"date": "1919-08-19",
"note": "from UK control over Afghan foreign affairs"
},
"national_holidays": [
{
"name": "Independence Day",
"day": "19 August",
"original_year": "1919"
}
],
"constitution": {
"history": "several previous; latest drafted 14 December 2003 - 4 January 2004, signed 16 January 2004, ratified 26 January 2004 (2017)",
"amendments": "proposed by a commission formed by presidential decree followed by the convention of a Grand Council (Loya Jirga) decreed by the president; passage requires at least two-thirds majority vote of the Loya Jirga membership and endorsement by the president (2017)"
},
"legal_system": "mixed legal system of civil, customary, and Islamic law",
"international_law_organization_participation": [
"has not submitted an ICJ jurisdiction declaration",
"accepts ICCt jurisdiction"
],
"citizenship": {
"citizenship_by_birth": "no",
"citizenship_by_descent_only": "at least one parent must have been born in - and continuously lived in - Afghanistan",
"dual_citizenship_recognized": "no",
"residency_requirement_for_naturalization": "5 years"
},
"suffrage": {
"age": 18,
"universal": true,
"compulsory": false
},
"executive_branch": {
"chief_of_state": "President of the Islamic Republic of Afghanistan Ashraf GHANI Ahmadzai (since 29 September 2014); CEO Abdullah ABDULLAH, Dr. (since 29 September 2014); First Vice President Abdul Rashid DOSTAM (since 29 September 2014); Second Vice President Sarwar DANESH (since 29 September 2014); First Deputy CEO Khyal Mohammad KHAN; Second Deputy CEO Mohammad MOHAQQEQ; note - the president is both chief of state and head of government",
"head_of_government": "President of the Islamic Republic of Afghanistan Ashraf GHANI Ahmadzai (since 29 September 2014); CEO Abdullah ABDULLAH, Dr. (since 29 September 2014); First Vice President Abdul Rashid DOSTAM (since 29 September 2014); Second Vice President Sarwar DANESH (since 29 September 2014); First Deputy CEO Khyal Mohammad KHAN; Second Deputy CEO Mohammad MOHAQQEQ",
"cabinet": "Cabinet consists of 25 ministers appointed by the president, approved by the National Assembly",
"elections_appointments": "president directly elected by absolute majority popular vote in 2 rounds if needed for a 5-year term (eligible for a second term); election last held in 2 rounds on 5 April and 14 June 2014 (next originally scheduled for April 2019 has been postponed by several months)",
"election_results": "Ashraf GHANI elected president in the second round; percent of vote in first round - Abdullah ABDULLAH (National Coalition of Afghanistan) 45%, Ashraf GHANI (independent) 31.6%, Zalmai RASSOUL 11.4%, other 12%; percent of vote in second round - Ashraf GHANI 56.4%, Abdullah ABDULLAH 43.6%"
},
"legislative_branch": {
"description": "bicameral National Assembly consists of: Meshrano Jirga or House of Elders (102 seats; 34 members indirectly elected by district councils to serve 3-year terms, 34 indirectly elected by provincial councils to serve 4-year terms, and 34 nominated by the president of which 17 must be women, 2 must represent the disabled, and 2 must be Kuchi nomads; members nominated by the president serve 5-year terms)\nWolesi Jirga or House of People (249 seats; members directly elected in multi-seat constituencies by proportional representation vote to serve 5-year terms)",
"elections": "Meshrano Jirga - last held 10 January 2015 (next to be held in 2018)\nWolesi Jirga - last held on 20 October 2018) (next tobe held in 2023)",
"election_results": "Meshrano Jirga - percent of vote by party - NA; seats by party - NA; composition - men 84, women 18, percent of women 17.6%\nWolesi Jirga - percent of vote by party NA; seats by party - NA; composition - NA",
"note": "the constitution allows the government to convene a constitutional Loya Jirga (Grand Council) on issues of independence, national sovereignty, and territorial integrity; it consists of members of the National Assembly and chairpersons of the provincial and district councils; a Loya Jirga can amend provisions of the constitution and prosecute the president; no constitutional Loya Jirga has ever been held, and district councils have never been elected; the president appointed 34 members of the Meshrano Jirga that the district councils should have indirectly elected"
},
"judicial_branch": {
"highest_courts": "Supreme Court or Stera Mahkama (consists of the supreme court chief and 8 justices organized into criminal, public security, civil, and commercial divisions or dewans)",
"judge_selection_and_term_of_office": "court chief and justices appointed by the president with the approval of the Wolesi Jirga; court chief and justices serve single 10-year terms",
"subordinate_courts": "Appeals Courts; Primary Courts; Special Courts for issues including narcotics, security, property, family, and juveniles"
},
"political_parties_and_leaders": {
"note": "the Ministry of Justice licensed 57 political parties as of September 2016"
},
"international_organization_participation": [
{
"organization": "ADB"
},
{
"organization": "CICA"
},
{
"organization": "CP"
},
{
"organization": "ECO"
},
{
"organization": "EITI ",
"note": "candidate country"
},
{
"organization": "FAO"
},
{
"organization": "G-77"
},
{
"organization": "IAEA"
},
{
"organization": "IBRD"
},
{
"organization": "ICAO"
},
{
"organization": "ICC ",
"note": "NGOs"
},
{
"organization": "ICCt"
},
{
"organization": "ICRM"
},
{
"organization": "IDA"
},
{
"organization": "IDB"
},
{
"organization": "IFAD"
},
{
"organization": "IFC"
},
{
"organization": "IFRCS"
},
{
"organization": "ILO"
},
{
"organization": "IMF"
},
{
"organization": "Interpol"
},
{
"organization": "IOC"
},
{
"organization": "IOM"
},
{
"organization": "IPU"
},
{
"organization": "ISO ",
"note": "correspondent"
},
{
"organization": "ITSO"
},
{
"organization": "ITU"
},
{
"organization": "ITUC ",
"note": "NGOs"
},
{
"organization": "MIGA"
},
{
"organization": "NAM"
},
{
"organization": "OIC"
},
{
"organization": "OPCW"
},
{
"organization": "OSCE ",
"note": "partner"
},
{
"organization": "SAARC"
},
{
"organization": "SACEP"
},
{
"organization": "SCO ",
"note": "dialogue member"
},
{
"organization": "UN"
},
{
"organization": "UNAMA"
},
{
"organization": "UNCTAD"
},
{
"organization": "UNESCO"
},
{
"organization": "UNHCR"
},
{
"organization": "UNIDO"
},
{
"organization": "UNWTO"
},
{
"organization": "UPU"
},
{
"organization": "WCO"
},
{
"organization": "WFTU ",
"note": "NGOs"
},
{
"organization": "WHO"
},
{
"organization": "WIPO"
},
{
"organization": "WMO"
},
{
"organization": "WTO"
}
],
"diplomatic_representation": {
"in_united_states": {
"chief_of_mission": "Ambassador Roya RAHMANI (since 24 November 2018)",
"chancery": "2341 Wyoming Avenue NW, Washington, DC 20008",
"telephone": "[1] (202) 483-6410",
"fax": "[1] (202) 483-6488",
"consulates_general": "Los Angeles, New York, Washington, DC"
},
"from_united_states": {
"chief_of_mission": "Ambassador John BASS (since December 2017)",
"embassy": "Bibi Mahru, Kabul",
"mailing_address": "U.S. Embassy Kabul, APO, AE 09806",
"telephone": "[00 93] 0700 108 001",
"fax": "[00 93] 0700 108 564"
}
},
"flag_description": {
"description": "three equal vertical bands of black (hoist side), red, and green, with the national emblem in white centered on the red band and slightly overlapping the other 2 bands; the center of the emblem features a mosque with pulpit and flags on either side, below the mosque are numerals for the solar year 1298 (1919 in the Gregorian calendar, the year of Afghan independence from the UK); this central image is circled by a border consisting of sheaves of wheat on the left and right, in the upper-center is an Arabic inscription of the Shahada (Muslim creed) below which are rays of the rising sun over the Takbir (Arabic expression meaning \"God is great\"), and at bottom center is a scroll bearing the name Afghanistan; black signifies the past, red is for the blood shed for independence, and green can represent either hope for the future, agricultural prosperity, or Islam",
"note": "Afghanistan had more changes to its national flag in the 20th century - 19 by one count - than any other country; the colors black, red, and green appeared on most of them"
},
"national_symbol": {
"symbols": [
{
"symbol": "lion"
}
],
"colors": [
{
"color": "red"
},
{
"color": "green"
},
{
"color": "black"
}
]
},
"national_anthem": {
"name": "\"Milli Surood\" (National Anthem)",
"lyrics_music": "Abdul Bari JAHANI/Babrak WASA",
"note": "adopted 2006; the 2004 constitution of the post-Taliban government mandated that a new national anthem should be written containing the phrase \"Allahu Akbar\" (God is Greatest) and mentioning the names of Afghanistan's ethnic groups",
"audio_url": "https://www.cia.gov/library/publications/the-world-factbook/attachments/audios/original/AF.mp3?1538604748"
}
},
"economy": {
"overview": "Despite improvements in life expectancy, incomes, and literacy since 2001, Afghanistan is extremely poor, landlocked, and highly dependent on foreign aid. Much of the population continues to suffer from shortages of housing, clean water, electricity, medical care, and jobs. Corruption, insecurity, weak governance, lack of infrastructure, and the Afghan Government's difficulty in extending rule of law to all parts of the country pose challenges to future economic growth. Afghanistan's living standards are among the lowest in the world. Since 2014, the economy has slowed, in large part because of the withdrawal of nearly 100,000 foreign troops that had artificially inflated the country’s economic growth.\nThe international community remains committed to Afghanistan's development, pledging over $83 billion at ten donors' conferences between 2003 and 2016. In October 2016, the donors at the Brussels conference pledged an additional $3.8 billion in development aid annually from 2017 to 2020. Even with this help, Government of Afghanistan still faces number of challenges, including low revenue collection, anemic job creation, high levels of corruption, weak government capacity, and poor public infrastructure.\nIn 2017 Afghanistan's growth rate was only marginally above that of the 2014-2016 average. The drawdown of international security forces that started in 2012 has negatively affected economic growth, as a substantial portion of commerce, especially in the services sector, has catered to the ongoing international troop presence in the country. Afghan President Ashraf GHANI Ahmadzai is dedicated to instituting economic reforms to include improving revenue collection and fighting corruption. The government has implemented reforms to the budget process and in some other areas. However, many other reforms will take time to implement and Afghanistan will remain dependent on international donor support over the next several years.",
"gdp": {
"purchasing_power_parity": {
"annual_values": [
{
"value": 69450000000,
"units": "USD",
"date": "2017"
},
{
"value": 67650000000.000008,
"units": "USD",
"date": "2016"
},
{
"value": 66209999999.999992,
"units": "USD",
"date": "2015"
}
],
"global_rank": 101,
"note": "data are in 2017 dollars"
},
"official_exchange_rate": {
"USD": 20240000000,
"date": "2017"
},
"real_growth_rate": {
"annual_values": [
{
"value": 2.7,
"units": "%",
"date": "2017"
},
{
"value": 2.2,
"units": "%",
"date": "2016"
},
{
"value": 1,
"units": "%",
"date": "2015"
}
],
"global_rank": 124
},
"per_capita_purchasing_power_parity": {
"annual_values": [
{
"value": 2000,
"units": "USD",
"date": "2017"
},
{
"value": 2000,
"units": "USD",
"date": "2016"
},
{
"value": 2000,
"units": "USD",
"date": "2015"
}
],
"global_rank": 209,
"note": "data are in 2017 dollars"
},
"composition": {
"by_end_use": {
"end_uses": {
"household_consumption": {
"value": 81.6,
"units": "%"
},
"government_consumption": {
"value": 12,
"units": "%"
},
"investment_in_fixed_capital": {
"value": 17.2,
"units": "%"
},
"investment_in_inventories": {
"value": 30,
"units": "%"
},
"exports_of_goods_and_services": {
"value": 6.7,
"units": "%"
},
"imports_of_goods_and_services": {
"value": -47.6,
"units": "%"
}
},
"date": "2016"
},
"by_sector_of_origin": {
"sectors": {
"agriculture": {
"value": 23,
"units": "%"
},
"industry": {
"value": 21.1,
"units": "%"
},
"services": {
"value": 55.9,
"units": "%"
}
},
"note": "data exclude opium production",
"date": "2016"
}
}
},
"gross_national_saving": {
"annual_values": [
{
"value": 22.7,
"units": "percent_of_gdp",
"date": "2017"
},
{
"value": 25.8,
"units": "percent_of_gdp",
"date": "2016"
},
{
"value": 21.4,
"units": "percent_of_gdp",
"date": "2015"
}
],
"global_rank": 78
},
"agriculture_products": {
"products": [
"opium",
"wheat",
"fruits",
"nuts",
"wool",
"mutton",
"sheepskins",
"lambskins",
"poppies"
]
},
"industries": {
"industries": [
"small-scale production of bricks",
"textiles",
"soap",
"furniture",
"shoes",
"fertilizer",
"apparel",
"food products",
"non-alcoholic beverages",
"mineral water",
"cement",
"handwoven carpets",
"natural gas",
"coal",
"copper"
]
},
"industrial_production_growth_rate": {
"annual_percentage_increase": -1.9,
"global_rank": 181,
"date": "2016"
},
"labor_force": {
"total_size": {
"total_people": 8478000,
"global_rank": 61,
"date": "2017"
},
"by_occupation": {
"occupation": {
"agriculture": {
"value": 44.3,
"units": "%"
},
"industry": {
"value": 18.1,
"units": "%"
},
"services": {
"value": 37.6,
"units": "%"
}
},
"date": "2017"
}
},
"unemployment_rate": {
"annual_values": [
{
"value": 23.9,
"units": "%",
"date": "2017"
},
{
"value": 22.6,
"units": "%",
"date": "2016"
}
],
"global_rank": 194
},
"population_below_poverty_line": {
"value": 54.5,
"units": "%",
"date": "2017"
},
"household_income_by_percentage_share": {
"lowest_ten_percent": {
"value": 3.8,
"units": "%"
},
"highest_ten_percent": {
"value": 24,
"units": "%"
},
"date": "2008"
},
"distribution_of_family_income": {
"annual_values": [
{
"value": 29.4,
"units": "gini_index",
"date": "2008"
}
],
"global_rank": 136
},
"budget": {
"revenues": {
"value": 2276000000,
"units": "USD"
},
"expenditures": {
"value": 5328000000,
"units": "USD"
},
"date": "2017"
},
"taxes_and_other_revenues": {
"percent_of_gdp": 11.2,
"global_rank": 210,
"date": "2017"
},
"budget_surplus_or_deficit": {
"percent_of_gdp": -15.1,
"global_rank": 217,
"date": "2017"
},
"public_debt": {
"annual_values": [
{
"value": 7,
"units": "percent_of_gdp",
"date": "2017"
},
{
"value": 7.8,
"units": "percent_of_gdp",
"date": "2016"
}
],
"global_rank": 202
},
"fiscal_year": {
"start": "21 December",
"end": "20 December"
},
"inflation_rate": {
"annual_values": [
{
"value": 5,
"units": "%",
"date": "2017"
},
{
"value": 4.4,
"units": "%",
"date": "2016"
}
],
"global_rank": 171
},
"commercial_bank_prime_lending_rate": {
"annual_values": [
{
"value": 15,
"units": "%",
"date": "2016-12-31"
},
{
"value": 15,
"units": "%",
"date": "2015-12-31"
}
],
"global_rank": 37
},
"stock_of_narrow_money": {
"annual_values": [
{
"value": 6644000000,
"units": "USD",
"date": "2014-12-31"
},
{
"value": 6192000000,
"units": "USD",
"date": "2013-12-31"
}
],
"global_rank": 94
},
"stock_of_broad_money": {
"annual_values": [
{
"value": 6945000000,
"units": "USD",
"date": "2014-12-31"
},
{
"value": 6544000000,
"units": "USD",
"date": "2013-12-31"
}
],
"global_rank": 95
},
"stock_of_domestic_credit": {
"annual_values": [
{
"value": -240600000,
"units": "USD",
"date": "2016-12-31"
}
],
"global_rank": 192
},
"market_value_of_publicly_traded_shares": {
"note": "NA"
},
"current_account_balance": {
"annual_values": [
{
"value": 1014000000,
"units": "USD",
"date": "2017"
},
{
"value": 1409000000,
"units": "USD",
"date": "2016"
}
],
"global_rank": 48
},
"exports": {
"total_value": {
"annual_values": [
{
"value": 784000000,
"units": "USD",
"date": "2017"
},
{
"value": 614200000,
"units": "USD",
"date": "2016"
}
],
"global_rank": 171,
"note": "not including illicit exports or reexports"
},
"commodities": {
"by_commodity": [
"opium",
"fruits",
"nuts",
"handwoven carpets",
"wool",
"cotton",
"hides",
"pelts",
"precious",
"semi-precious gems",
"medical herbs"
]
},
"partners": {
"by_country": [
{
"name": "India",
"percent": 56.5
},
{
"name": "Pakistan",
"percent": 29.6
}
],
"date": "2017"
}
},
"imports": {
"total_value": {
"annual_values": [
{
"value": 7616000000,
"units": "USD",
"date": "2017"
},
{
"value": 6160000000,
"units": "USD",
"date": "2016"
}
],
"global_rank": 114
},
"commodities": {
"by_commodity": [
"machinery",
"other capital goods",
"food",
"textiles",
"petroleum products"
]
},
"partners": {
"by_country": [
{
"name": "China",
"percent": 21
},
{
"name": "Iran",
"percent": 20.5
},
{
"name": "Pakistan",
"percent": 11.8
},
{
"name": "Kazakhstan",
"percent": 11
},
{
"name": "Uzbekistan",
"percent": 6.8
},
{
"name": "Malaysia",
"percent": 5.3
}
],
"date": "2017"
}
},
"reserves_of_foreign_exchange_and_gold": {
"annual_values": [
{
"value": 7187000000,
"units": "USD",
"date": "2017-12-31"
},
{
"value": 6901000000,
"units": "USD",
"date": "2015-12-31"
}
],
"global_rank": 85
},
"external_debt": {
"annual_values": [
{
"value": 2840000000,
"units": "USD"
}
],
"global_rank": 144
},
"exchange_rates": {
"annual_values": [
{
"value": 7.87,
"units": "USD",
"date": "2017"
},
{
"value": 68.03,
"units": "USD",
"date": "2016"
},
{
"value": 67.87,
"units": "USD",
"date": "2015"
},
{
"value": 61.14,
"units": "USD",
"date": "2014"
},
{
"value": 57.25,
"units": "USD",
"date": "2013"
}
],
"note": "afghanis (AFA) per US dollar"
}
},
"energy": {
"electricity": {
"access": {
"population_without_electricity": {
"value": 18999254,
"units": "people"
},
"total_electrification": {
"value": 43,
"units": "%"
},
"urban_electrification": {
"value": 83,
"units": "%"
},
"rural_electrification": {
"value": 32,
"units": "%"
},
"date": "2012"
},
"production": {
"kWh": 1211000000,
"global_rank": 146,
"date": "2016"
},
"consumption": {
"kWh": 5526000000,
"global_rank": 119,
"date": "2016"
},
"exports": {
"kWh": 0,
"global_rank": 96,
"date": "2016"
},
"imports": {
"kWh": 4400000000,
"global_rank": 42,
"date": "2016"
},
"installed_generating_capacity": {
"kW": 634100,
"global_rank": 138,
"date": "2016"
},
"by_source": {
"fossil_fuels": {
"percent": 45,
"global_rank": 159,
"date": "2016"
},
"nuclear_fuels": {
"percent": 0,
"global_rank": 32,
"date": "2017"
},
"hydroelectric_plants": {
"percent": 52,
"global_rank": 34,
"date": "2017"
},
"other_renewable_sources": {
"percent": 4,
"global_rank": 111,
"date": "2017"
}
}
},
"crude_oil": {
"production": {
"bbl_per_day": 0,
"global_rank": 101,
"date": "2017"
},
"exports": {
"bbl_per_day": 0,
"global_rank": 82,
"date": "2015"
},
"imports": {
"bbl_per_day": 0,
"global_rank": 84,
"date": "2015"
},
"proved_reserves": {
"bbl": 0,
"global_rank": 99,
"date": "2018-01-01"
}
},
"refined_petroleum_products": {
"production": {
"bbl_per_day": 0,
"global_rank": 110,
"date": "2015"
},
"consumption": {
"bbl_per_day": 35000,
"global_rank": 117,
"date": "2016"
},
"exports": {
"bbl_per_day": 0,
"global_rank": 124,
"date": "2015"
},
"imports": {
"bbl_per_day": 34210,
"global_rank": 97,
"date": "2015"
}
},
"natural_gas": {
"production": {
"cubic_metres": 164200000,
"global_rank": 79,
"date": "2017"
},
"consumption": {
"cubic_metres": 164200000,
"global_rank": 108,
"date": "2017"
},
"exports": {
"cubic_metres": 0,
"global_rank": 57,
"date": "2017"
},
"imports": {
"cubic_metres": 0,
"global_rank": 81,
"date": "2017"
},
"proved_reserves": {
"cubic_metres": 49550000000,
"global_rank": 62,
"date": "2018-01-01"
}
},
"carbon_dioxide_emissions_from_consumption_of_energy": {
"megatonnes": 9067000,
"global_rank": 111,
"date": "2017"
}
},
"communications": {
"telephones": {
"fixed_lines": {
"total_subscriptions": 118769,
"subscriptions_per_one_hundred_inhabitants": 1,
"global_rank": 138,
"date": "2017"
},
"mobile_cellular": {
"total_subscriptions": 23929713,
"subscriptions_per_one_hundred_inhabitants": 70,
"global_rank": 52,
"date": "2017"
},
"system": {
"general_assessment": "progress has been made on Afghanistan's first limited fixed-line telephone service and nationwide optical fibre backbone; aided by the presence of multiple providers, mobile-cellular telephone service continues to improve swiftly; the Afghan Ministry of Communications and Information claims that more than 90% of the population live in areas with access to mobile-cellular services (2017)",
"domestic": "less than 1 per 100 for fixed-line teledensity; 70 per 100 for mobile-cellular; an increasing number of Afghans utilize mobile-cellular phone networks (2017)",
"international": "country code - 93; multiple VSAT's provide international and domestic voice and data connectivity (2016)"
}
},
"broadcast_media": "state-owned broadcaster, Radio Television Afghanistan (RTA), operates a series of radio and television stations in Kabul and the provinces; an estimated 150 private radio stations, 50 TV stations, and about a dozen international broadcasters are available (2018)",
"internet": {
"country_code": ".af",
"users": {
"total": 3531770,
"percent_of_population": 10.6,
"global_rank": 92,
"date": "2016-07-01"
}
}
},
"transportation": {
"air_transport": {
"national_system": {
"number_of_registered_air_carriers": 4,
"inventory_of_registered_aircraft_operated_by_air_carriers": 20,
"annual_passenger_traffic_on_registered_air_carriers": 1929907,
"annual_freight_traffic_on_registered_air_carriers": 33102038,
"date": "2015"
},
"civil_aircraft_registration_country_code_prefix": {
"prefix": "YA",
"date": "2016"
},
"airports": {
"total": {
"airports": 43,
"global_rank": 99,
"date": "2016"
},
"paved": {
"total": 25,
"over_3047_metres": 4,
"2438_to_3047_metres": 4,
"1524_to_2437_metres": 14,
"914_to_1523_metres": 2,
"under_914_metres": 1,
"date": "2017"
},
"unpaved": {
"total": 18,
"2438_to_3047_metres": 1,
"1524_to_2437_metres": 8,
"914_to_1523_metres": 4,
"under_914_metres": 5,
"date": "2016"
}
},
"heliports": {
"total": 9,
"date": "2013"
}
},
"pipelines": {
"by_type": [
{
"type": "gas",
"length": 466,
"units": "km"
}
],
"date": "2013"
},
"waterways": {
"value": 1200,
"units": "km",
"note": "chiefly Amu Darya, which handles vessels up to 500 DWT",
"global_rank": 58,
"date": "2011"
},
"ports_and_terminals": {
"river_ports": [
"Kheyrabad",
"Shir Khan"
]
}
},
"military_and_security": {
"expenditures": {
"annual_values": [
{
"value": 0.89,
"units": "percent_of_gdp",
"date": "2016"
},
{
"value": 0.99,
"units": "percent_of_gdp",
"date": "2015"
},
{
"value": 1.33,
"units": "percent_of_gdp",
"date": "2014"
},
{
"value": 1.06,
"units": "percent_of_gdp",
"date": "2013"
},
{
"value": 1.14,
"units": "percent_of_gdp",
"date": "2012"
}
],
"global_rank": 125
},
"branches": {
"by_name": [
"Afghan National Defense",
"Security Forces (ANDSF) comprised of military",
"police",
"other security elements",
"Afghan National Army (includes Afghan Air Force)",
"Afghan National Police",
"Afghan Local Police",
"the National Directorate of Security"
],
"date": "2017"
},
"service_age_and_obligation": {
"years_of_age": 18,
"note": "18 is the legal minimum age for voluntary military service; no conscription",
"date": "2017"
}
},
"terrorism": {
"home_based": "al-Qa'ida (AQ): aim(s): eject Western influence from the Islamic world, unite the worldwide Muslim community, overthrow governments perceived as un-Islamic, and ultimately, establish a pan-Islamic caliphate under a strict Salafi Muslim interpretation of sharia\narea(s) of operation: maintains established networks and a longtime operational presence in Afghanistan, especially in the south, northwest, and northeast near the Afghanistan-Pakistan border (April 2018)\nIslamic Jihad Union (IJU): aim(s): drive NATO forces out of Afghanistan and destabilize the country; overthrow the Government of Uzbekistan\narea(s) of operation: conducts attacks in collaboration with other extremist groups, including the Taliban, against NATO and Afghan forces across the country, especially in the northern and eastern Paktika, Paktia, and Nangarhar provinces (April 2018)\nIslamic Movement of Uzbekistan (IMU): aim(s): enhance its networks and secure territory in Afghanistan to establish a secure presence from which it can pursue its historic goal of establishing an Islamic state in the Fergana Valley, a fertile valley spread across eastern Uzbekistan, southern Kyrgyzstan, and northern Tajikistan\narea(s) of operation: operates mostly in the north along the Afghanistan-Pakistan border, with its heaviest presence in Badakhshan Province, where IMU has operated paramilitary training camps and bases\nnote: the IMU is fractured and mostly supports ISIS-K although some members have continued working with the Taliban (April 2018)\nIslamic State of Iraq and ash-Sham-Khorasan (ISIS-K): aim(s): establish an Islamic caliphate in Afghanistan, Pakistan, and parts of Central Asia; counter Westerners and Shia Muslims\narea(s) of operation: stronghold in Nangarhar Province near the Afghanistan-Pakistan border and operating in Kunar, Laghman, Jowzjan provinces with pockets of support throughout Afghanistan\nnote: recruits from among the local population, Central Asian extremists in Afghanistan, and other militant groups, such as Tehrik-e Taliban Pakistan, the Afghan Taliban, and the Islamic Movement of Uzbekistan (April 2018)\nTehrik-e-Taliban Pakistan (TTP): aim(s): drive foreign troops from Afghanistan; remove Pakistani forces from Pakistan's Federally Administered Tribal Areas (FATA) and, ultimately, overthrow the Pakistan Government to implement TTP's strict interpretation of sharia\narea(s) of operation: headquartered in several eastern Afghanistan provinces near the Afghanistan-Pakistan border; operates primarily along the northeastern Afghanistan-Pakistan border, especially in Kunar and Paktika provinces, where TTP has established sanctuaries (April 2018)",
"foreign_based": "al-Qa'ida in the Indian Subcontinent (AQIS): aim(s): establish an Islamic caliphate in the Indian subcontinent\narea(s) of operation: heaviest presence is in Afghanistan, especially in the eastern and southern regions, where most of the Afghan-based leaders are located\nnote: targets primarily Afghan military and security personnel and US interests (April 2018)\nHaqqani Taliban Network (HQN): aim(s): expel US and Coalition forces and replace the Afghan Government with an Islamic state operating according to a strict Salafi Muslim interpretation of sharia under the Afghan Taliban\narea(s) of operation: stages attacks from Kurram and North Waziristan Agency in Pakistan's Federally Administered Tribal Areas (FATA) across from Afghanistan's southeastern border; operational throughout the country, especially in Kabul and Paktiya and Khost provinces\nnote: plays a leading role in planning and executing high-profile attacks against Afghan personnel, NATO's Resolute Support Mission, US and Coalition Forces, and other US and Western interests (April 2018)\nHarakat ul-Jihad-i-Islami (HUJI): aim(s): implement sharia in Afghanistan; enhance its networks and drive foreign troops from Afghanistan\narea(s) of operation: operations throughout Afghanistan, targeting primarily Afghan Government personnel and Coalition forces (April 2018)\nHarakat ul-Mujahidin (HUM): aim(s): enhance its networks and paramilitary training in Afghanistan and, ultimately, incorporate Kashmir into Pakistan; establish an Islamic state in Kashmir\narea(s) of operation: maintains paramilitary training camps in eastern Afghanistan (April 2018)\nJaish-e-Mohammed (JEM): aim(s): participate in the insurgency against Afghan and international forces to support a Taliban return to power in Afghanistan and annex the Indian state of Jammu and Kashmir to Pakistan\narea(s) of operation: historically operated in Afghanistan's eastern provinces (April 2018)\nJaysh al Adl: aim(s): enhance its operational networks and capabilities for staging cross-border attacks into Pakistan and Iran\narea(s) of operation: operational in the greater Balochistan area, where fighters stage attacks targeting Shia Muslims in Iran and Pakistan\nnote: formerly known as Jundallah (April 2018)\nLashkar i Jhangvi (LJ): aim(s): enhance its networks and paramilitary training in Afghanistan; exterminate Shia Muslims, rid the Afghanistan-Pakistan region of Western influence\narea(s) of operation: headquartered in the east; operates paramilitary training camps near the Afghanistan-Pakistan border across from the central area of Pakistan's Federally Administered Tribal Areas (FATA) region; operatives conduct operations inside Afghanistan (April 2018)\nLashkar-e Tayyiba (LT): aim(s): annex the Indian state of Jammu and Kashmir to Pakistan and foment Islamic insurgency in India; attack Western, Indian, and Afghan interests in Afghanistan; support the Taliban's return to power; enhance its recruitment networks and paramilitary training in Afghanistan, and, ultimately, install Islamic rule throughout South Asia\narea(s) of operation: targets Coalition forces and Western interests throughout the country; maintains several facilities, such as paramilitary training camps, medical clinics serving locals, and schools for youths; targets Pashtun youth for recruitmentAdministered Tribal Areas (FATA) region; operatives conduct operations inside Afghanistan (April 2018)"
},
"transnational_issues": {
"disputes": [
"Afghan, Coalition, and Pakistan military meet periodically to clarify the alignment of the boundary on the ground and on maps and since 2014 have met to discuss collaboration on the Taliban insurgency and counterterrorism efforts",
"Afghan and Iranian commissioners have discussed boundary monument densification and resurvey",
"Iran protests Afghanistan's restricting flow of dammed Helmand River tributaries during drought",
"Pakistan has sent troops across and built fences along some remote tribal areas of its treaty-defined Durand Line border with Afghanistan which serve as bases for foreign terrorists and other illegal activities",
"Russia remains concerned about the smuggling of poppy derivatives from Afghanistan through Central Asian countries"
],
"refugees_and_iternally_displaced_persons": {
"refugees": {
"by_country": [
{
"people": 75893,
"country_of_origin": "Pakistan"
}
],
"date": "2017"
},
"internally_displaced_persons": {
"people": 1286000,
"note": "mostly Pashtuns and Kuchis displaced in the south and west due to natural disasters and political instability",
"date": "2017"
}
},
"illicit_drugs": {
"note": "world's largest producer of opium; poppy cultivation increased 63 percent, to 328,304 hectares in 2017; while eradication increased slightly, it still remains well below levels achieved in 2015; the 2017 crop yielded an estimated 9,000 mt of raw opium, a 88% increase over 2016; the Taliban and other antigovernment groups participate in and profit from the opiate trade, which is a key source of revenue for the Taliban inside Afghanistan; widespread corruption and instability impede counterdrug efforts; most of the heroin consumed in Europe and Eurasia is derived from Afghan opium; Afghanistan is also struggling to respond to a burgeoning domestic opiate addiction problem; a 2015 national drug use survey found that roughly 11% of the population tested positive for one or more illicit drugs; vulnerable to drug money laundering through informal financial networks; illicit cultivation of cannabis and regional source of hashish (2018)"
}
}
}
Country record
To come up with the data queries involved nothing more than visualizing the data we wanted to capture, then refining queries in the Azure Portal's Data Explorer for Cosmos DB.
Area - Largest and Area - Smallest
The first query I created was for Area - Largest, where the goal was to list the top 10 countries with the largest area. Total land area can be found in the JSON under the geography section, area subsection, where there is an object named total. geography.area.total.value will give us the land area in square kilometers.
Geography Section of Country JSON
SELECT top 10 c.name, c.key, c.geography.area.total, c.geography.area.land, c.geography.area.water, c.geography.area.global_rank FROM c order by c.geography.area.global_rank
Area -Largest Query in Azure Portal Data Explorer
The query result records look like this:
[
{
"name": "Russia",
"key": "russia",
"total": {
"value": 17098242,
"units": "sq km"
},
"land": {
"value": 16377742,
"units": "sq km"
},
"water": {
"value": 720500,
"units": "sq km"
},
"global_rank": 1
},
...
]
We also want an Area - Smallest report, so we'll need a query for that. This is exactly the same query that we just developed except that we're ordering by global_rank desc this time.
SELECT top 10 c.name, c.key, c.geography.area.total, c.geography.area.land, c.geography.area.water, c.geography.area.global_rank FROM c order by c.geography.area.global_rank desc
Imports/Exports - Largest and Smallest
The next set of reports are for imports (largest and smallest) and exports (largest and smallest). Imports and exports are structured similarly in the economy section of the country JSON.
Economy - Exports Section of Country JSON
The query for the Imports - Highest (top 10 countries with the highest exports) is below. Once again there is a global_rank value which makes the order by clause easy. We have decided to return the first (most recent) export value, but also the prior year value.
SELECT top 10 c.name, c.key, c.economy.imports.total_value.annual_values[0], c.economy.imports.total_value.annual_values[1], c.economy.imports.total_value.global_rank FROM c order by c.economy.imports.total_value.global_rank
Imports - Highest Query in Azure Portal Data Explorer
Once again, the sister query for Imports - Lowest is nothing more than adding desc to the end of the same query:
Deriving the export queries from the import queries involves nothing more than changing the c.economy.imports to c.economy.exports:
SELECT top 10 c.name, c.key, c.economy.exports.total_value.annual_values[0], c.economy.exports.total_value.annual_values[1], c.economy.exports.total_value.global_rank FROM c order by c.economy.exports.total_value.global_rank
Inflation - Highest and Lowest
Next up, queries for inflation (highest and lowest). The relevant country record JSON is:
Economy - Inflation Rate Section of Country JSON
This is becoming almost cookie-cutter simple at this point. Our two inflation queries are:
SELECT top 10 c.name, c.key, c.economy.inflation_rate.annual_values[0], c.economy.inflation_rate.global_rank FROM c order by c.economy.inflation_rate.global_rank desc
SELECT top 10 c.name, c.key, c.economy.inflation_rate.annual_values[0], c.economy.inflation_rate.global_rank FROM c order by c.economy.inflation_rate.global_rank desc
SELECT top 10 c.name, c.key, c.economy.inflation_rate.annual_values[0], c.economy.inflation_rate.global_rank FROM c order by c.economy.inflation_rate.global_rank asc
Internet Users - Most
To report on the 10 countries with the most Internet users, we can use the communication section of the country JSON:
Communications- Internet Section of Country JSON
And here's our query, which will give us Internet user count as well as percentage of population.
SELECT top 10 c.name, c.key, c.communications.internet.users.total, c.communications.internet.users.percent_of_population, c.communications.internet.users.global_rank FROM c order by c.communications.internet.users.global_rank asc
Population - Highest and Lowest
The last report chart query we'll do is for population (highest and lowest). We can find that data in the people section of the country JSON:
People - Population Section of Country JSON
Here are our queries:
SELECT top 10 c.name, c.key, c.people.population.total,c.people.population.global_rank FROM c order by c.people.population.global_rank
SELECT top 10 c.name, c.key, c.people.population.total,c.people.population.global_rank FROM c order by c.people.population.global_rank desc
Azure Functions
With our Cosmos DB queries ready, we can now write the Azure Functions for each chart. We'll be using Cosmos DB input bindings for each function which is where we'll configure each function's SQL query. Aside from that, all these functions have to do is return data, so their code is merely this:
module.exports = function (context, req, countryList) {
context.res = {
body: countryList
};
context.done();
};
Let's configure the report-area-highest function's Cosmos DB binding:
Cosmos DB Binding for report-area-highest Function
Now it's just more of the same. We create the following functions, all identical with minimal code, where the only difference is the query in the Cosmos DB binding:
Report Functions
Creating our functions took almost no time at all. We test them, and they work:
Testing a Chart Function
API Operations
In Part 2, we front-ended our Azure Functions with API Management operations. We'll continue that pattern here. We add new operations to our Factbook API, with the same names as the functions we just created: to run the report-area-highest report, the API path will /report-area-highest.
The only configuration needed for each function is its Backend Policies, where we set the URL rewriting and CORS policy.
The only configuration needed for each function is its Backend Policies, where we set the URL rewriting and CORS policy.
Configuring API Operaiton Backend Policies
Once again, things are cookie-cutter: each API has an identical back-end policy, except for the URL rewrite value.
We test our API functions, and they all work.
Testing API Operation
Configuring our API operations was a fast and easy activity.
Adding Charts to Web Site
Now that we've done the advance work of database queries, new Azure Functions, and new API Operations we are ready to add charts to our web site.
Layout
Layout-wise, we already have a banner area with a country select list and an input text box / search button for performing searches. We're going to add another drop-down for charts. That will give a user three ways to interact with the site: 1) select a country, 2) search for countries, or 3) select a chart. On the desktop, all three control areas will be visible at the same time:
On phones, however, it would be crowded to show all three areas. So, we're going to allow users to switch between Search and Charts with a button:
Phone view - toggle between Search and Chart
When the user selects a country, initiates a search, or select a chart, the main display changes accordingly.
- The country area is the accordion of topical content (Introduction, Geography, People, etc.).
- The search area lists search results. Selecting a country switches to country view.
- The chart area shows a chart rendered by Google Charts.
Charts
We'll be using Google Charts to draw our charts, which will all be column charts. Google Charts generates top-notch charts for minimal effort, and is free.
Before we can render our charts, we need to fetch the data for the selected chart. Here's the JavaScript code to call the appropriate API function after a chart has been selected:
// Chart selection changed - retrieve chart data and draw chart.
function chartSelectionChanged() {
var chartName = $('#select-chart').val();
if (!chartName) {
$('#chart_div').html('');
return;
}
if (!inChartView) {
setChartView();
}
$('#chart_div').html('');
xAxisTitle = null;
yAxisTitle = null;
yAxisTitle2 = null;
yAxisTitle3 = null;
var reportId = null;
var url = null;
switch (chartName) {
case 'area-highest':
chartTitle = 'Area - Largest';
xAxisTitle = 'Country';
yAxisTitle = 'Area (sq km)';
yAxisTitle2 = 'Land';
yAxisTitle3 = 'Water';
reportId = 'report-area-highest';
break;
case 'area-lowest':
chartTitle = 'Area - Smallest';
xAxisTitle = 'Country';
yAxisTitle = 'Area (sq km)';
yAxisTitle2 = 'Land';
yAxisTitle3 = 'Water';
reportId = 'report-area-lowest';
break;
case 'exports-highest':
chartTitle = 'Exports - Highest';
xAxisTitle = 'Country';
yAxisTitle = 'Exports (USD)';
yAxisTitle2 = 'Prior Year Exports (USD)';
reportId = 'report-exports-highest';
break;
case 'exports-lowest':
chartTitle = 'Exports - Lowest';
xAxisTitle = 'Country';
yAxisTitle = 'Exports (USD)';
yAxisTitle2 = 'Prior Year Exports (USD)';
reportId = 'report-exports-lowest';
break;
case 'imports-highest':
chartTitle = 'Imports - Highest';
xAxisTitle = 'Country';
yAxisTitle = 'Imports (USD)';
yAxisTitle2 = 'Prior Year Exports (USD)';
reportId = 'report-imports-highest';
break;
case 'imports-lowest':
chartTitle = 'Imports - Lowest';
xAxisTitle = 'Country';
yAxisTitle = 'Imports (USD)';
yAxisTitle2 = 'Prior Year Exports (USD)';
reportId = 'report-imports-lowest';
break;
case 'inflation-highest':
chartTitle = 'Inflation - Highest';
xAxisTitle = 'Country';
yAxisTitle = 'Inflation Rate';
reportId = 'report-inflation-highest';
break;
case 'inflation-lowest':
chartTitle = 'Inflation - Lowest';
xAxisTitle = 'Country';
yAxisTitle = 'Inflation Rate';
reportId = 'report-inflation-lowest';
break;
case 'internet-users-highest':
chartTitle = 'Internet Users - Most';
xAxisTitle = 'Country';
yAxisTitle = 'No. Internet Users (Millions)';
yAxisTitle2 = 'Percent of Populaton';
reportId = 'report-internet-users-highest';
break;
case 'population-highest':
chartTitle = 'Population - Highest';
xAxisTitle = 'Country';
yAxisTitle = 'Population';
reportId = 'report-population-highest';
break;
case 'population-lowest':
chartTitle = 'Population - Lowest';
xAxisTitle = 'Country';
yAxisTitle = 'Population';
reportId = 'report-population-lowest';
break;
}
if (!reportId) {
$('#chart_div').html('');
return;
}
url = 'https://factbook.azure-api.net/world-factbook/' + reportId;
$('#country').val('');
$("body").css("cursor", "progress");
$('#loading').css('visibility', 'visible');
var chartData = null;
$.ajax({
type: 'GET',
url: url,
accepts: "json",
}).done(function (result) {
chartData = $.parseJSON(result);
googleChartData = null;
switch (chartName) {
case 'internet-users-highest':
googleChartData = [];
if (chartData != null) {
var country = null;
countrySelect = [];
var total = 0;
for (var d = 0; d < chartData.length; d++) {
country = chartData[d];
countrySelect.push(country.name);
total = country.total / 1000000;
googleChartData.push([
{ v: country.name }, total, country.percent_of_population
]);
}
}
break;
case 'area-highest':
case 'area-lowest':
googleChartData = [];
if (chartData != null) {
var country = null;
var land = 0;
var water = 0;
countrySelect = [];
for (var d = 0; d < chartData.length; d++) {
country = chartData[d];
countrySelect.push(country.name);
land = 0;
water = 0;
if (country.land) land = country.land.value;
if (country.water) water = country.water.value;
googleChartData.push([
{ v: country.name }, country.total.value, land, water
]);
}
}
break;
case 'exports-highest':
case 'exports-lowest':
case 'imports-highest':
case 'imports-lowest':
googleChartData = [];
if (chartData != null) {
var country = null;
countrySelect = [];
for (var d = 0; d < chartData.length; d++) {
country = chartData[d];
countrySelect.push(country.name);
var n1 = country["$1"].value;
var n2 =(country["$2"] ? country["$2"].value : null);
googleChartData.push([
{ v: country.name }, n1, n2
]);
}
}
break;
case 'inflation-highest':
case 'inflation-lowest':
googleChartData = [];
if (chartData != null) {
var country = null;
countrySelect = [];
for (var d = 0; d < chartData.length; d++) {
country = chartData[d];
countrySelect.push(country.name);
googleChartData.push([
{ v: country.name }, country["$1"].value
]);
}
}
break;
case 'population-highest':
case 'population-lowest':
googleChartData = [];
if (chartData != null) {
var country = null;
countrySelect = [];
for (var d = 0; d < chartData.length; d++) {
country = chartData[d];
countrySelect.push(country.name);
googleChartData.push([
{ v: country.name }, country.total
]);
}
}
break;
}
google.charts.load('current', { packages: ['corechart', 'bar'] });
google.charts.setOnLoadCallback(drawColumnChart);
})
}
Retrieving Chart Data via API
The above code validates the selected chart (lines 24-100), fetches the chart data (114-121), stores the chart data and metadata in some variables (123-212), and then invokes drawColumnChart() to perform the actual chart drawing (214-215).
// Draw column chart
// Inputs:
// googleChartData .. rows of data
// xAxisTitle ....... title of horizontal chart axis
// yAxisTitle........ title of vertical chart axis, and first series
// yAxisTitle2 ...... title of second series, or null
// yAxisTitle3 ...... title of third series, or null
function drawColumnChart() {
var data = new google.visualization.DataTable();
data.addColumn('string', xAxisTitle);
data.addColumn('number', yAxisTitle);
if (yAxisTitle2) {
data.addColumn('number', yAxisTitle2);
}
if (yAxisTitle3) {
data.addColumn('number', yAxisTitle3);
}
data.addRows(googleChartData);
var options = {
title: chartTitle,
hAxis: {
title: xAxisTitle,
},
vAxis: {
title: yAxisTitle
},
};
var chart = new google.visualization.ColumnChart(
document.getElementById('chart_div'));
google.visualization.events.addListener(chart, 'select', function () {
selection = chart.getSelection()
var index = selection[0].row;
var name = countrySelect[index];
$('#country').val(name).trigger('change');
});
chart.draw(data, options);
$('#loading').css('visibility', 'collapse');
$("body").css("cursor", "default");
}
drawColumnChart code
The above code sets up the data structures the Google Charts API needs and renders a bar chart. For a single-series chart such as Population - Highest, this is the appearance:
The user can perform some interaction with this chart. On the desktop, hovering over a bar will provide a label-value tooltip. On a phone, a long-press on a bar will do the same. A click or tap on the bar will bring up that country in country view, just as if it had been selected from the country select drop-down.
Some charts provide multiple series, in which case there are different color bars. Here's the Area - Largest chart, which has 3 bar series (total area, land area, and water area):
Population - Highest Chart
The user can perform some interaction with this chart. On the desktop, hovering over a bar will provide a label-value tooltip. On a phone, a long-press on a bar will do the same. A click or tap on the bar will bring up that country in country view, just as if it had been selected from the country select drop-down.
Some charts provide multiple series, in which case there are different color bars. Here's the Area - Largest chart, which has 3 bar series (total area, land area, and water area):
Area - Largest Chart
If you rotate your phone or resize your desktop browser windows, charts will redraw to fit the new screen dimensions.
More Data
The site we created in Part 2 only shows a small part of the available country data. During this Part 3 activity, work was done to show more fields. The following were added:- Geography: climate, terrain
- People: population growth, birth rate, death rate
- Government: chief of state, head of government, national symbol, national anthem
- Economy: inflation rate, imports, exports
I'd like to call attention in particular to the Government section where national anthem information has been added. The World Factbook data includes a nation anthem audio link for most country records. I've added a Listen link to allow users of the web site to be able to listen to the national anthem.
Other Site Refinements
Lastly, polishing and refinements were made to a number of areas to improve usability.There is now a shortcut URL to the site: http://world-factbook.davidpallmann.com. The web site is static, hosted in Azure blob storage in just 3 files: index.html, site.css, and site.js. You can see the full source on github (link at end of post).
Most noticeable perhaps is the spinner that appears when a user has to wait. Since we rely on Azure Functions, a user may experience a few seconds' delay when the function they are accesing has a cold start (because it hasn't been accessed recently).
A thanks to site https://webkul.github.io/csspin where I found the pinwheel spinner CSS.
In Conclusion
In this post we built on the front-end work of Part 2. To support charts we first came up with queries for our Cosmos DB database; then created chart functions that run the queries via Cosmos DB bindings; and lastly extended our API from Part 2 with additional operations for the new functions. All of this went extremely rapidly as we used the same patterns for each report. All of our cloud services contributed to rapid development.With the API operations added, charting was implemented in the web site using Google Charts. With today's work, the World Country Data web site has become more useful and more polished.
Source Code on Github
No comments:
Post a Comment