Dealing with obstacles data stored in CSV files (1)

Authorities sometimes publishes obstacle data sets called eTOD, which stands from Electronic Terrain and Obstacle Data. Those data sets comes in various formats: csv files, dat files, xml files (AIXM format) or even shapefiles.
This post will start a mini-series which result will be a QGIS plugin to import eTOD data from CSV files into PostGIS database.


To see exmaple how CSV eTOD data looks like, go to websites where eAIP is published, for example:
Finland: https://www.ais.fi/ais/aipobst/aipobst.htm
Poland:
Romania:

As you can see data are not published in common format in terms of columns names, coordinates format, or attribute values (description of marking and lighting).
So, to challenge here is to find out how to deal with eTOD data in a generic way.
First at all, we have to decide which attributes we are going to store in our database. As usual, because it is for training and demonstration purposes rather than for use in real life, I am not going to use all attributes that are required by international law regulations and etc. So, at this stage I have decided that I want to store following attributes:

• iso alpha 2 country code
• name of source file
• obstacle ident
• obstacle type
• obstacle marking infromation
• obstacle lighting infromation
• latitude and longitude as they are given in source file
• used horizontal reference system
• elevation 
• height
• vertical unit of measure - in other word in what unit elevation and height are expressed

As you can see those attributes are minimum for example to create obstacle charts that are similar to those we can see in aviation charts and to perform some analysis.

File mapping_rules.py contains dictionary that map fields in CSV files with eTOD data with attributes that will be stored in database:

csv_etod_fields = {'Finland': {'IDENT': ('OBST ID',),
                               'TYPE': ('TYPE',),
                               'MARKING': ('MARKINGS',),
                               'LIGHTING': ('LGT TYPE',),
                               'LAT_SRC': ('LAT',),
                               'LON_SRC': ('LONG',),
                               'H_REF_SRC': (None, 'WGS_84'),
                               'AMSL': ('ELEV MSL (FT)',),
                               'AGL': ('HGT AGL (FT)',),
                               'V_UOM': (None, 'FT')},
                   'Romania': {'IDENT': ('Ident',),
                               'TYPE': ('Type',),
                               'MARKING': (None, 'NO_DATA'),
                               'LIGHTING': ('LGHT',),
                               'LAT_SRC': ('Latitude',),
                               'LON_SRC': ('Longitude',),
                               'H_REF_SRC': ('HorizRefSyst',),
                               'AMSL': ('Elevation',),
                               'AGL': ('Height',),
                               'V_UOM': ('UOM',)},
                   'Poland': {'IDENT': ('Obstacle Identifier',),
                              'TYPE': ('Obstacle type',),
                              'MARKING': ('Marking',),
                              'LIGHTING': ('Lighting',),
                              'LAT_SRC': ('Latitude',),
                              'LON_SRC': ('Longitude',),
                              'H_REF_SRC': ('Horizontal reference system',),
                              'AMSL': ('Elevation',),
                              'AGL': ('Height',),
                              'V_UOM': ('Height Uom',)},
                   'Spain': {'IDENT': ('Identificador_Identifier',),
                             'TYPE': ('Tipo_Type',),
                             'MARKING': (None, 'NO_DATA'),
                             'LIGHTING': ('LGHT',),
                             'LAT_SRC': ('Latitude',),
                             'LON_SRC': ('Longitude',),
                             'H_REF_SRC': ('HorizRefSyst',),
                             'AMSL': ('Elevation',),
                             'AGL': ('Height',),
                             'V_UOM': ('UOM',)}}

File ctry_iso_codes.py contains dictionary to get iso coutry code based on its name:

ctry_iso_codes = {'Poland': 'PL'}

Now we are ready to write CsvEtodObstacle class. Let' create file csv_etod_tools.py and import first modules we have just create:

from mapping_rules import csv_etod_fields
from ctry_iso_codes import ctry_iso_codes

Then create constructor of CsvEtodObstacle class. At its initial state it contains empty mapping rules and obstacle_data dictionaries:

class CsvEtodObstacle:
    def __init__(self, ctry_name, source_file_fpath, source_coord_format=''):
        self.ctry_name = ctry_name
        self.source_file_fpath = source_file_fpath
        self.source_coord_format = source_coord_format
        self._mapping_fields = {'IDENT': None,
                                'TYPE': None,
                                'MARKING': None,
                                'LIGHTING': None,
                                'LAT_SRC': None,
                                'LON_SRC': None,
                                'H_REF_SRC': None,
                                'AMSL': None,
                                'AGL': None,
                                'V_UOM': None}
        self._obstacle_data = {'CTRY_ISO_CODE': '',
                               'SOURCE_FILE': '',
                               'IDENT': '',
                               'TYPE': '',
                               'MARKING': '',
                               'LIGHTING': '',
                               'LAT_SRC': '',
                               'LON_SRC': '',
                               'H_REF_SRC': '',
                               'AMSL': '',
                               'AGL': '',
                               'V_UOM': ''}

Before we will assigning data from CSV file to selected attributes (IDENT, TYPE, MARKING etc.) we need to 'map' correct fileds from CSV file in order to assign correct values to given attribute.
Method map_csv_values will assging 'mapping rules' for country given during creation of instance CsvEtodObstacle object:

def map_csv_rules(self):
    for field in self._mapping_fields:
        self._mapping_fields[field] = csv_etod_fields[self.ctry_name][field]

We can easily check if it works by typing following code: