README.md

Scripts to create PostgreSQL schema with tables and views and add data

Add functions get_pretty_number and getbreaks_classes from postgresql-common repository.

1. role.sql – to create users and roles (replace password with user password).
2. tea.sql – to create the tea schema.
3. create_tables.sql – to create tables.
4. texts_title.sql – to create and populate a table that supplies data for titles, legends, and data tables.
5. texts_pop.sql – to create and populate a table that supplies data for pop-ups and information panel.
6. update_data.sh – a bash script to populate tea.data table with data published on Open Data Portal of Latvia (user must be followed by the user name and PGPASSWORD by the user password).

Select contents of tea.translations in JSON format to be inserted in translations.json (JSON must be formatted before insertion):

WITH c
AS (
  SELECT *
  FROM tea.translations
  ORDER BY variable_name
  )
SELECT json_object_agg(variable_name, json_build_array(lv, en, used, html)) AS result
FROM c;

Functions

• get_territory – returns territory names for use in other functions.
• get_menu_territories – returns territory names for the menu.
• generate_text_menu – returns names of economic activities, occupations, and sectors.
• generate_data_table – returns the contents of the table in the panel.

For maps:

• a_generate_map_viz – returns base data.
• generate_text_title – returns map titles. Uses helper function generate_text_title_sub.
• generate_text_title_sub – the helper function used by generate_text_title.
• generate_text_pop – returns pop-up texts.
• get_map_centroids – returns centroid coordinates of the territory polygons for placing the pie charts.