Changes tracking in PostGIS - basics

One of the huge benefit of using database is that many user can work on the database at the same time, e.g editing, updating database of airport taxiways and we can track their activity - who and when made changes.

Let’s assume that you want to build and maintain database of taxiways (co to jest taxiway). You not only want to model this part of real world but also want to store information when an by who (which user) specified feature has been created and changed.

Firstly we need database to store data about taxiways.

Obstacle data - symbology as on aeronautical charts

DOF_Manager QGIS plugin (2) - inserting parsed data into PostGIS database

This post won't be revealing if you familiar with previous posts related to insert data into database.

Method dof_dat_file2postgis simply reads each line of the data file, parse it and after parsing 10 000 lines (or all file if there is less lines) into database.  Notice, that before executing inert satatement parsed data is validated by method check_dof_parsed_data(), which at this satge does nothing and will be completed in the next post.:

Dealing with obstacles data stored in CSV files (2) - validating parsed data

Data should be checked - e. g. coordinates have 'sense' before inserting it into database.
In this post I am  going to write method which checks data in terms of constraint imposed by our model of obstacle database.

Quick 3D visualizaion of airspaces in QGIS

We can use Qgis2threejs to visualize DEM and vector data in 3D.

DOF_Manager QGIS plugin (1) - introduction

Let's back to the problem of dealing with obstacles coming from Digital Obstacle File (DOF) with GIS. I have covered this issue in following posts so far:
PostGIS database for storing FAA Digital Obstacle File - SQL statements to create database
Challenge: importing FAA digital obstacle file content into PostGIS database - Python script to import data from dat file with obstacle data directly into PostGIS database
QGIS Plugin to convert DOF (Digital Obstacle File) data - QGIS plugin that reads dat file and convert it into CSV, KML or shapefile format.
As you can see, we are already able to import obstacles into spatial database, display it using QGIS and convert data into other formats. But we want to go further - a QGIS plugins that reads data and import it directly to database, updates imported data with data from daily change files, easily search and export data with specified criteria (e.g type, location within circle of specified point) to other formats, manually adding data etc.

QGIS customized attribute dialog - Value Relation

In the post you saw how to use Value Map 'method' to bind values between two tables that are linked together - there is a relation between the tables in other words.
But what if the foreign keys or attributes assigned to those keys are changed in one table? This method ('hard coding' values using Value Map) does not track such changes - unless values are updated every time such changes takes place.
Good news is that QGIS has other methods, that are better for such cases - you can use
In simply words any changes in database tables will be reflect in customized attribute dialog.

insert into country (ctry_iso2, ctry_name) values 
('AF', 'Afghanistan'),
('AX','Aland Islands'),
('AL','Albania'),
('DZ','Algeria'),
('AS','American Samoa');

insert into country (ctry_iso2, ctry_name) values 
('GA', 'Gabon'),
('GM', 'Gambia'),
('GE', 'Georgia'),
('DE', 'Germany'),
('GH', 'Ghana'),
('GI', 'Gibraltar'), 
('GR', 'Greece'),
('GL', 'Greenland');

select *
from airspace
where ctry_iso2 = 'GE';

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?

Customized dialog for airspace database - part 1

In this post we want to achieve customized attribute dialog that looks as follows:

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: