Airspace database - adding country table and further customizing attribute dialog

Let's modify airspace database a bit. We have ctry_iso2 column in airspace table, because we want to to know in which country given airspace is. But what if we want to store more information about country? For example, link to authority that publishes aeronautical data or online version of aeronautical publication?


Data Definition Language (DDL) statement to create basic country table:

CREATE TABLE country (
 rec_id serial,
 ctry_iso2 CHAR(2) PRIMARY KEY,
 ctry_name VARCHAR(150) NOT NULL
);

Let's insert some data that are uses in our airspace table - so far we used '-1' code for country iso alpha 2 code -that code doesn't exist in real world and will be used for test purposes only!

INSERT INTO country (ctry_iso2, ctry_name)
VALUES ('-1', 'TEST_CTRY')

Now, we are ready to add foreign key and 'link' airspace and country tables:

ALTER TABLE airspace
ADD CONSTRAINT fk_asp_ctry FOREIGN KEY (ctry_iso2) REFERENCES country (ctry_iso2);

Let's check if  foreign key 'works' by typing following statement that join country and airspace tables:


And the result is:

so, it works.

We have to enter 2-letter ISO country code (to be more precises: ISO 3166-1 alpha-2 code, for example PL for Poland). Let's assume that user wants to uses country names (e. g.: Poland) instead of ISO codes (e. g.: PL) in attribute dialog, but in database value should be stored as ISO code. We can achieve this by changing customized form in following way:
1. Open form in Qt Designer and replace ctry_iso2 QLineEdit widget with ctry_iso2 QComboBox widget. We use QComboBox widget because we enable selecting country name from list of countries.
2. Open attribute dialog for airspace layer in QGIS and go to Attributes Form tab.
Find ctry_iso2 item on Fields list (from Available Widgets) click on it.
In Widget Type choose Value Map:

 3. Now we have to fill in fields Value (what will be stored in  layer - airspace table in our case) and Description (what user will see in attribute dialog). We can enter manually all items, but in our case (more than 250 pairs of country iso 2 code and country name) the better idea is to load data from CSV file.