Airspaces and PostGIS - introduction

The shapefile has been the only format for polygon 'part' of aviation data (airspaces). This format is handy, when we want  to store, manage or share data among user but has many limitations - and I don't mean only those specified here:
http://desktop.arcgis.com/en/arcmap/latest/manage-data/shapefiles/geoprocessing-considerations-for-shapefile-output.htm
We store only very limited scope of the data, in terms of attributes and dosen't store any information about relation among various tables: we have information about airspace ident and its geometry. If we look at any AIP or other aeronautcial publication we will see that each airspace has many attributes such as:
country - where airspace is located (in my database it usually be country that publishes publication that issue airspace)
airspace designation - which sometimes is composed by airspace ident and airspace name
vertical limit - upper and lower limits and their unit of measure
activity time - when airspace is active
lateral limits - textual description of the airspace 'shape'
airspace type, airspace class
we also might be interested in which aeronautical publication issues the airsace

And there is a big problem how to deal with some of those attributes when we use shapefile.
Let's take for example country. Country attribute will appear also in navigation aid, obstacle and other tables. What about vertical limits (lower and upper limit) and classes of airspace? Bear in mind that some airspace might have more than one class, this means that they might have several 'lower' and 'upper' limits that corresponds to given airspace class. 

As you can see, storing and managing airspaces is more complex problem that it looks at a first glance.

Let's start with something simple: in this post (or stage of building airspace database) we are going only transfer data stored in shapefile (for example result of plugins) into simple (so far) database.
First, we need to create table airspace:

CREATE TABLE airspace (
 rec_id serial PRIMARY KEY,
 asp_ident VARCHAR(100) NOT NULL,
 asp_lateral_limits_geo geography(POLYGON, 4326)
);

Then, after importing shapefile into PostGIS database, lest copy data  into our airspace table:

INSERT INTO airspace (asp_ident, 
        asp_lateral_limits_geo)
SELECT poly_id,
       geom
FROM tmp_asp;

An now we have got exactly the same data but stored in database table. So, actually there is no any 'increment' of data, in terms of additional attributes. Let's modify airspace table, so we will be able to store more information that only ident and geometry of the airspace.

ALTER TABLE airspace
ADD COLUMN ctry_iso2 CHAR(2),
ADD COLUMN asp_name VARCHAR(100),
ADD COLUMN asp_type VARCHAR(50) NOT NULL DEFAULT 'NO_DATA',
ADD COLUMN asp_activity VARCHAR(300),
ADD COLUMN asp_lateral_limits_txt VARCHAR(1000),
ADD COLUMN asp_class VARCHAR(10),
ADD COLUMN tmp_upper_limit FLOAT,
ADD COLUMN tmp_upper_limit_uom CHAR(10),
ADD COLUMN tmp_lower_limit FLOAT,
ADD COLUMN tmp_lower_limit_uom CHAR(10),
ADD COLUMN aero_pub_type VARCHAR(25),
ADD COLUMN aero_pub_nr VARCHAR(25),
ADD COLUMN added_date TIMESTAMP NOT NULL DEFAULT now();

Column names are quite significant so I am not going to explain in details what kind of data each of them will store.

You probabbly want to ask at least two questions:
1. Why there are not any constraints imposed on column ctry_iso2?
(ctry_iso2 store country ISO alphanumeric -2 code, more infromation:
2. Why lower and upper limits are not in separated table? This solution has the same disadvantages as described above (keeping data in single file not in database). So, what is the purpose of that?

After all, if we want to add a new object to airspace database (and layer) we will see following attribute dialog by default after 'shape' creation:

It is not very user friendly at all: user have to enter manually each of attribute (so far only the asp_ident is mandatory but it will change soon). asp_type by default will have 'NO_DATA' value; but if we want other type first we need to remove content of the text field.
Needles to say it is difficult to keep data consistent, e. g. free from typos etc. - because there is no mechanism to control what user enters: neither on the database site or attribute dialog.

Those drawback will be gradually decreased in next posts.