Obstacle FAA Digital Obstacle File version

 version 0.1

Table of contents

• Plugin description
• Usage
• Import obstacles from
  Digital Obstacle File
• Insert single obstacle
• Digital Obstacle File Conversion
• Obstace Database (PostGIS)
• Tables description
• hor_acc
• lighting
• marking
• obstacle
• obstacle_type
• us_state
• verif_status
• vert_acc

Plugin description

ObstacleFAADigitalObstacleFileDB plugin allows user to deal with obstacle data coming from FAA Digital Obstacle File.
There are following functionality in the plugin:

• Import DOF data into PostGIS database
• Insert Single Obstacle into PostGIS database
• Convert DOF into: csv, shp, kml formats

This plugin is designed to work with Obstacle Database based on PostGIS: obstacle_faa_dof. GitHub repository for plugin including scripts for database: ObstacleDatabase_FAA_DOF

Import obstacles from Digital Obstacle File

To Import obstacle from FAA DOF into database:

Proces of importing consitos of two steps:

1. Importing DOF into Database
2. Analyzing imported log file to check if some record were not imported and find out the reasn of that.

Importing DOF

1. Select Import DOF File tab.
2. Select FAA Digital Obstacle File to import.
3. Select file where to store log for imprted data. This log will contain infomration related only to those records
   that importing failed for reason such as: incorreced data (example coordinates) or validating database constraints (duplictaed key etc.)
4. Press Import button to start importing

Analyzing imported log file

After import is completed, Import Show Log button will be enabled.
Press Import Show Log button to open import log file. Information about not imported records are in log. Example:

2021-10-03 20:00:38,066 | ERROR | dof_importer | import_dof | 34 | DOF file line number: 5 | Attribute hor_acc_code value error. invalid literal for int() with base 10: ' ' | Attribute vert_acc_code value error. Unknown vertical accuracy code. Actual value:
2021-10-03 20:00:38,120 | ERROR | dof_importer | import_dof | 34 | DOF file line number: 10 | Attribute hor_acc_code value error. invalid literal for int() with base 10: ' ' | Attribute vert_acc_code value error. Unknown vertical accuracy code. Actual value:
2021-10-03 20:00:38,163 | ERROR | dof_importer | import_dof | 41 | DOF file line number: 22 | BŁĄD:  Obstacle outside Alaska state!
CONTEXT:  funkcja PL/pgSQL insert_obstacle(integer,character varying,integer,character,character,double precision,double precision,character,integer,character,character,character,character varying,double precision,double precision), wiersz 56 w RAISE
 

Insert single obstacle

Digital Obstacle File Conversion

Obstace Database (PostGIS)

Tables description

hor_acc

KEY	COLUMN NAME	DATA TYPE	NULLABLE	DEFAULT VALUE	DESCRIPTION
Primary key	hor_acc_code	int	No		horizontal accuracy code
	tolerance_value	float	No		horizontal accuracy value
	tolerance_uom	varchar(3)	No		horizontal accuracy unit of measure

lighting

KEY	COLUMN NAME	DATA TYPE	NULLABLE	DEFAULT VALUE	DESCRIPTION
Primary key	lighting_code	char(1)	No		lighting code
	lighting_desc	varchar(50)	No		lighting description

marking

KEY	COLUMN NAME	DATA TYPE	NULLABLE	DEFAULT VALUE	DESCRIPTION
Primary key	marking_code	char(1)	No		marking code
	marking_desc	varchar(50)	No		marking description

obstacle

KEY	COLUMN NAME	DATA TYPE	NULLABLE	DEFAULT VALUE	DESCRIPTION
Primary key	rec_id	serial	No		record ident
	state_id	int	No		State id. Foreign key to table us_state. Join: obstacle.state_id = us_state.state_id
	obst_ident	char(9)	No		obstacle identifier
	lat_src	char(12)	No		obstacle latitude in format DD MM SS.s H
	lon_src	char(13)	No		obstacle longitude
	agl	float	No		height above ground level DD MM SS.s H
	amsl	float	Yes		elevation avove mean sea level
	vert_acc_code	char(1)	No		Vertical accuracy. Foreign key to table hor_acc. Join: obstacle.vert_acc_code = vert_acc.vert_acc_code
	hor_acc_code	int	No		Horizontal accuracy. Foreign key to table hor_acc. Join: obstacle.hor_acc_code = hor_acc.hor_acc_code
	quantity/td>	int	Yes		Number of obstacles
	marking_code	char(1)	No		Marking code Foreign key to table marking. Join: obstacle.marking_code = marking.marking_code
	lighting_code	char(1)	No		lighting code Foreign key to table lighting. Join: obstacle.lighting_code = lighting.lighting_code
	verif_status_code	char(1)	No		Verification status code Foreign key to table verif_status. Join: obstacle.verif_status_code = verif_status.verif_status_code
	city_name	varchar(20)	Yes		City name related to the obstacle
	faa_study_number	char(14)	Yes		FAA internal study number
	julian_date	char(7)	Yes		Date of action
	insert_tmsp	timestamptz	No	now()	Insert obstacle into database timestamp
	insert_user	varcchar(10)	No	current_user	User name that inserted the obstacle
	modification_tmsp	timestamptz	No	now()	Obstacle last modification timestamp
	modification_user	varcchar(10)	No	current_user	User name that that made last modification
	geo_location	geography(POINT, 4326))	No		Gegraphy location of the obstacle

obstacle_type

KEY	COLUMN NAME	DATA TYPE	NULLABLE	DEFAULT VALUE	DESCRIPTION
Primary key	obst_type_id	serial	No		obstacle type record ident
	obst_type	varchar(50)	No		obstacle type

us_state

KEY	COLUMN NAME	DATA TYPE	NULLABLE	DEFAULT VALUE	DESCRIPTION
Primary key	state_id	serial	No		record ident
	state_name	varchar(75)	No		state name
	boundary	geography(POLYGON, 4326)	No		state geograhical boundaries

verif_status

KEY	COLUMN NAME	DATA TYPE	NULLABLE	DEFAULT VALUE	DESCRIPTION
Primary key	verif_status_code	char(1)	No		verification status code
	status_desc	varchar(20)	No		verification status description

vert_acc

KEY	COLUMN NAME	DATA TYPE	NULLABLE	DEFAULT VALUE	DESCRIPTION
Primary key	vert_acc_code	char(1)	No		vertical accuracy code
	tolerance_value	float	No		vertical accuracy value
	tolerance_uom	varchar(3)	No		vertical accuracy unit of measure