def test_load_airports(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
make_movements_airports_file = make_test_data_file(
MOVEMENTS_REPORTING_AIRPORTS_DATA_FILE)
ok = load_airports(make_movements_airports_file(), context, connection)
connection_owner = ctx.get_connection(ctx.CONTEXT, ctx.POSTGRES_DB)
create_GIST_index(context, connection_owner)
self.assertTrue(ok)
#
# Check we actually added some user sectors
#
schemaName = context[ctx.SCHEMA_NAME]
with connection.cursor(cursor_factory=DictCursor) as dict_cursor:
dict_cursor.execute("SELECT count(id) FROM %s.airports;",
[AsIs(schemaName)])
self.assertTrue(dict_cursor.fetchone()[0] > 0)
dict_cursor.execute(
"SELECT * FROM %s.airports where icao_ap_code = 'EGLL';",
[AsIs(schemaName)])
heathrow = dict_cursor.fetchone()
latitude = heathrow['latitude']
longitude = heathrow['longitude']
icao_code = heathrow['icao_ap_code']
self.assertEquals(icao_code, 'EGLL')
self.assertTrue(51.3 < latitude < 51.5)
self.assertTrue(-0.48 < longitude < -0.44)
connection.close()
def test_make_simple_point(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
self.assertTrue(make_point(self.LON_1, self.LAT_1, connection))
self.assertTrue(make_point(self.LON_2, self.LAT_2, connection))
self.assertTrue(make_point(self.LON_3, self.LAT_3, connection))
self.assertTrue(make_point(self.LON_4, self.LAT_4, connection))
self.assertTrue(make_point(self.LON_5, self.LAT_5, connection))
def insert_aircraft(registration, type, address, date_time):
"""
Inserts a new record into the fleet data.
returns True db_id
False, "Error message"
"""
log.debug(f"Attempting to insert fleet record {registration}, {type}, {address}, {date_time}")
connection = ctx.get_connection(ctx.CONTEXT, ctx.REF_DB_USER)
context = ctx.CONTEXT
str_record = str([registration, type, address, date_time])
# Note http://initd.org/psycopg/docs/usage.html with statement
# Does not work as described. Exiting this with will close the connection
with connection:
# Does the combination of registration and type exist already?
found, res = find_by_reg_type(registration, type, context, connection)
if found:
# Is this a new address?
found_address, results = find_by_keys({'AIRCRAFT_REG': registration,
'AIRCRAFT_TYPE': type,
'AIRCRAFT_ADDRESS': address},
context, connection)
if not found_address:
# This is a new address so add it.
ok, id = add_fleet_record([registration, type, address, date_time], context, connection)
log.debug(f"A fleet data entry was created for {str_record}, id : {id}")
return ok, id
else:
log.debug(f"Fleet record {str_record} not inserted, already exists.")
return False, f"Fleet record {str_record} not inserted, already exists with given addresss."
else:
# We can just add this record since the reg and type combination is new.
ok, id = add_fleet_record([registration, type, address, date_time], context, connection)
log.debug(f"Inserted fleet record {str_record}, it was not present")
return [(False, "New fleet record but failed to add."), (ok, id)][ok]
def tear_down():
"""
Completely tears down the working database. There must be no open
connections to the database.
"""
log.info("Tearing down the reference database")
postgres_connection = ctx.get_connection(ctx.CONTEXT, ctx.REF_POSTGRES)
database_name = AsIs(ctx.CONTEXT[ctx.DB_NAME])
schema_name = AsIs(ctx.CONTEXT[ctx.SCHEMA_NAME])
admin_name = AsIs(ctx.CONTEXT[ctx.ADMIN_NAME])
user_name = AsIs(ctx.CONTEXT[ctx.USER_NAME])
with postgres_connection.cursor() as cursor:
cursor.execute("DROP TABLE IF EXISTS %s.fleet;", [schema_name])
cursor.execute("DROP SCHEMA IF EXISTS %s;", [schema_name])
cursor.execute("SELECT pg_terminate_backend "
"(pg_stat_activity.pid) FROM "
"pg_stat_activity WHERE datname = current_database() "
"AND pid <> pg_backend_pid();")
try:
with postgres_connection.cursor() as cursor:
cursor.execute("DROP DATABASE IF EXISTS %s;", [database_name])
cursor.execute("DROP ROLE IF EXISTS %s;", [user_name])
cursor.execute("DROP ROLE IF EXISTS %s;", [admin_name])
except OperationalError:
log.exception('Failed to drop database or roles.')
return False
finally:
postgres_connection.close()
log.info("Completed tearing down the reference database")
return True
def tear_down():
"""
Completely tears down the working database, in doing so
will close all the connections (excpet this one!!)
Use the postgres connection for this call.
"""
log.info("Tearing down the geo database")
postgres_connection = ctx.get_connection(ctx.CONTEXT, ctx.POSTGRES)
database_name = AsIs(ctx.CONTEXT[ctx.DB_NAME])
schema_name = AsIs(ctx.CONTEXT[ctx.SCHEMA_NAME])
admin_name = AsIs(ctx.CONTEXT[ctx.ADMIN_NAME])
user_name = AsIs(ctx.CONTEXT[ctx.USER_NAME])
with postgres_connection.cursor() as cursor:
cursor.execute("DROP TABLE IF EXISTS %s.sectors;", [schema_name])
cursor.execute("DROP TABLE IF EXISTS %s.airports;", [schema_name])
cursor.execute("DROP TABLE IF EXISTS %s.user_defined_sectors;",
[schema_name])
cursor.execute("DROP SCHEMA IF EXISTS %s;", [schema_name])
cursor.execute("SELECT pg_terminate_backend "
"(pg_stat_activity.pid) FROM "
"pg_stat_activity WHERE datname = current_database() "
"AND pid <> pg_backend_pid();")
try:
with postgres_connection.cursor() as cursor:
cursor.execute("DROP DATABASE IF EXISTS %s;", [database_name])
cursor.execute("DROP ROLE IF EXISTS %s;", [user_name])
cursor.execute("DROP ROLE IF EXISTS %s;", [admin_name])
except OperationalError:
log.exception('Failed to drop database or roles.')
return False
finally:
postgres_connection.close()
log.info("Completed tearing down the geo database")
return True
def test_load_user_airspace(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
make_user_airspace_file = make_test_data_file(USER_DEFINED_AIRSPACES)
ok = load_user_airspace(make_user_airspace_file(), context, connection)
connection_owner = ctx.get_connection(ctx.CONTEXT, ctx.POSTGRES_DB)
create_GIST_index(context, connection_owner)
self.assertTrue(ok)
#
# Check we actually added some user sectors
#
schemaName = context[ctx.SCHEMA_NAME]
with connection.cursor() as cursor:
cursor.execute("SELECT count(id) FROM %s.user_defined_sectors;",
[AsIs(schemaName)])
self.assertTrue(cursor.fetchone()[0] > 0)
connection.close()
def get_ref_db_connection():
"""
Get the user ref database connection. Connection for use by
application code.
returns a connection.
"""
return ctx.get_connection(ctx.CONTEXT, ctx.REF_DB_USER)
def test_load_airports(self):
"""
Remove all the airports then add them
"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
remove_all_airports()
make_aps_file = make_test_data_file(AIRPORTS_DATA_FILE)
load_airports(make_aps_file(), context, connection)
def test_find_by_bad_dict(self):
""" Find using the dict with bad key"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.REF_DB_USER)
context = ctx.CONTEXT
finder_dict = {'REG_KEYZ': 'GZZPP',
TYPE_KEY: 'MY-PLANE-2',
ADDRESS_KEY: '0x777776'}
ok, records = find_by_keys(finder_dict, context, connection)
self.assertFalse(ok)
def test_find_standard_sector_intersections(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
# Start with no sectors
remove_all_sectors()
# Add the simple sector
ok, sector_1_id = add_airspace_geometry(self.sector_1, context,
connection)
self.assertTrue(ok)
# Flight terminates in the sector
terminates = find_horizontal_sector_intersections(
*self.TERMINATES_FLIGHT)
terminates_lats = terminates[0]
terminates_lons = terminates[1]
terminates_ids = terminates[2]
self.assertEqual(1, len(terminates_lats))
self.assertEqual(1, len(terminates_lons))
self.assertEqual(1, len(terminates_ids))
sectors = find_airspace_by_database_ID(str(terminates_ids[0]), context,
connection)
self.assertEqual(1, len(sectors))
self.assertEqual(sectors[0][6], 'Made Up Sector')
# Flight transits the sector
transits = find_horizontal_sector_intersections(*self.TRANSIT_FLIGHT)
transit_lats = transits[0]
transit_lons = transits[1]
transit_ids = transits[2]
self.assertEquals(2, len(transit_lats))
self.assertEquals(2, len(transit_lons))
self.assertEquals(2, len(transit_ids))
# Flight transits the sector above the sector
transits = find_horizontal_sector_intersections(
*self.TRANSIT_TOO_HIGH_FLIGHT)
transit_lats = transits[0]
transit_lons = transits[1]
transit_ids = transits[2]
self.assertEquals(0, len(transit_lats))
self.assertEquals(0, len(transit_lons))
self.assertEquals(0, len(transit_ids))
# Flight originates in the sector
originates = find_horizontal_sector_intersections(
*self.ORIGINATES_FLIGHT)
originates_lats = originates[0]
originates_lons = originates[1]
originates_ids = originates[2]
self.assertEquals(2, len(originates_lats))
self.assertEquals(2, len(originates_lons))
self.assertEquals(2, len(originates_ids))
def create():
"""
Creates the complete reference database from scratch.
"""
context = ctx.CONTEXT
postgres_admin_connection = ctx.get_connection(context,
ctx.REF_POSTGRES_DB)
log.info("Creating the fleet tables")
ref_ok = create_fleet_table(context, postgres_admin_connection)
postgres_admin_connection.close()
return ref_ok
def test_find_by_dict(self):
""" Find using the dict of key values."""
connection = ctx.get_connection(ctx.CONTEXT, ctx.REF_DB_USER)
context = ctx.CONTEXT
insert_aircraft(record4[0], record4[1], record4[2], record4[3])
finder_dict = {REG_KEY: 'GZZPP',
TYPE_KEY: 'MY-PLANE-2',
ADDRESS_KEY: '0x777776'}
ok, records = find_by_keys(finder_dict, context, connection)
self.assertTrue(ok)
self.assertEquals(1, len(records))
record = records[0]
self.assertEquals(5, len(record))
self.assertEquals(record[2], 'MY-PLANE-2')
def test_add_record(self):
"""
"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.REF_DB_USER)
context = ctx.CONTEXT
insert_aircraft(record1[0], record1[1], record1[2], record1[3])
found, rows = find_by_keys({'AIRCRAFT_REG': 'GZZGG', 'AIRCRAFT_TYPE': 'MY-PLANE', 'AIRCRAFT_ADDRESS': '0x999999'}, context, connection)
self.assertTrue(found)
self.assertTrue(len(rows) == 1)
id = rows[0]['id']
print(rows[0])
remove_aircraft(id, context, connection)
found, rows = find_aircraft(id, context, connection)
self.assertFalse(found)
def create(db_type=DB_TYPE_GEO):
"""
Creates the database users, schema and database.
The flag type determines geo or ref.
db_type one of the supported db types above.
returns True if the create succeeded.
"""
if DB_TYPES.__contains__(db_type):
log.info(f"Creating users, db and schema for db type {db_type}.")
context = ctx.CONTEXT
if db_type == DB_TYPE_REF:
postgres_connection = ctx.get_connection(context, ctx.REF_POSTGRES)
else:
postgres_connection = ctx.get_connection(context, ctx.POSTGRES)
log.info(f"Creating the user roles for db type {db_type}.")
roles_ok = create_users_roles(context, postgres_connection)
log.info(f"Creating the database instance for db {db_type}.")
db_ok = create_db(context, postgres_connection)
log.info(f"Creating the database schema for db {db_type}.")
postgres_connection.close()
if db_type == DB_TYPE_REF:
postgres_db_connection = ctx.get_connection(
context, ctx.REF_POSTGRES_DB)
else:
postgres_db_connection = ctx.get_connection(
context, ctx.POSTGRES_DB)
schema_ok = create_db_schema(context, postgres_db_connection)
postgres_db_connection.close()
log.info(f"Completed creating the database for db {db_type}.")
return roles_ok and db_ok and schema_ok
else:
log.error(
f"Unknown database type, {db_type}, should be one of {DB_TYPES}.")
return False
def create():
"""
Creates the geo database tables.
"""
context = ctx.CONTEXT
postgres_admin_connection = ctx.get_connection(context, ctx.POSTGRES_DB)
log.info("Making the db spatial")
db_spatial_ok = make_db_spatial(context, postgres_admin_connection)
log.info("Creating the airspace tables")
airspace_table_ok = create_airspace_table(context,
postgres_admin_connection)
custom_airspace_table_ok = create_user_sector_table(
context, postgres_admin_connection)
airports_table_ok = create_airports_table(context,
postgres_admin_connection)
postgres_admin_connection.close()
return db_spatial_ok and custom_airspace_table_ok and airports_table_ok and airspace_table_ok
def test_find_standard_sector_intersections_with_segments(self):
"""
A more complex sector shape. A combe with three legs.
"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
# Start with no sectors
remove_all_sectors()
# Add combe like sector to the sectors database
ok, sector_2_id = add_airspace_geometry(self.sector_2, context,
connection)
self.assertTrue(ok)
# Terminates in the sector
terminates = find_horizontal_sector_intersections(
*self.TERMINATES_IN_COMBE_FLIGHT)
terminates_lats = terminates[0]
terminates_lons = terminates[1]
terminates_ids = terminates[2]
self.assertEquals(5, len(terminates_lats))
self.assertEquals(5, len(terminates_lons))
self.assertEquals(5, len(terminates_ids))
# Transits the sector
transits = find_horizontal_sector_intersections(
*self.TRANSITS_COMBE_FLIGHT)
transit_lats = transits[0]
transit_lons = transits[1]
transit_ids = transits[2]
self.assertEquals(6, len(transit_lats))
self.assertEquals(6, len(transit_lons))
self.assertEquals(6, len(transit_ids))
# Originates in the sector, should be an even number as we add in the
# position if we originate
originates = find_horizontal_sector_intersections(
*self.ORIGINATES_IN_COMBE_FLIGHT)
origin_lats = originates[0]
origin_lons = originates[1]
origin_sector_ids = originates[2]
self.assertEquals(6, len(origin_lats))
self.assertEquals(6, len(origin_lons))
self.assertEquals(6, len(origin_sector_ids))
def test_add_same_record(self):
"""
If the record already exists but has a different address we
create a new one with a new date.
If then a record is inserted with the old address we should add it in as a
new record with a new time stamp.
"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.REF_DB_USER)
context = ctx.CONTEXT
insert_aircraft(record1[0], record1[1], record1[2], record1[3])
found, rows = find_by_keys({'AIRCRAFT_REG': 'GZZGG', 'AIRCRAFT_TYPE': 'MY-PLANE', 'AIRCRAFT_ADDRESS': '0x999999'}, context, connection)
self.assertTrue(found)
self.assertTrue(len(rows) == 1)
self.assertTrue(insert_aircraft(record1[0], record1[1], record1[2], record1[3]))
print(insert_aircraft(record2[0], record2[1], record2[2], record2[3]))
now = datetime.today().isoformat(timespec='seconds')
print(insert_aircraft(record1[0], record1[1], '0x555555', record1[3]))
print(insert_aircraft(record1[0], record1[1], '0x555555', now + 'Z'))
def remove_all_reference_data():
"""
Deletes all the sectors in the sectors table.
"""
try:
db_user_connection = ctx.get_connection(ctx.CONTEXT, ctx.REF_DB_USER)
except OperationalError:
log.error("Could not get connection, probaly the user does not exist")
return False
try:
schema_name = AsIs(ctx.CONTEXT[ctx.SCHEMA_NAME])
with db_user_connection.cursor() as cursor:
cursor.execute("DELETE FROM %s.fleet;", [schema_name])
except ProgrammingError:
log.exception('Failed to remove fleet data, table does not exist.')
return False
finally:
db_user_connection.close()
return True
def test_find_airport_intersections(self):
"""
Check a simple airport intersection
"""
RADIUS_1 = 30
AIRPORT_ID_1 = "NONO"
AIRPORT_ID_2 = "LFPG"
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
remove_all_airports()
make_aps_file = make_test_data_file(AIRPORTS_DATA_FILE)
load_airports(make_aps_file(), context, connection)
ok, airports = finder('icao_ap_code', AIRPORT_ID_2)
self.assertTrue(ok)
self.assertEqual(1, len(airports))
airport = airports[0]
self.assertEqual(airport['icao_ap_code'], 'LFPG')
FLIGHT_ID_1 = "test-1-id-1"
LATS_1 = [
50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0
]
LONS_1 = [-0.5, -0.4, -0.3, -0.2, -0.1, 0.0, 0.1, 0.2, 0.3, 0.4, 0.5]
FLIGHT_ID_2 = "test-1-id-2"
LATS_2 = [
49.0, 49.0, 49.0, 49.0, 49.0, 49.0, 49.0, 49.0, 49.0, 49.0, 49.0
]
LONS_2 = [1.5, -1.4, 1.0, 1.2, 1.7, 2.0, 2.1, 2.4, 2.5, 2.6, 2.7]
# An airport we do not intersect with
lats, lons = find_airport_cylinder_intersection(
FLIGHT_ID_1, LATS_1, LONS_1, AIRPORT_ID_1, RADIUS_1, False)
self.assertEqual(0, len(lats))
self.assertEqual(0, len(lons))
# An airport we do intersect with
lats, lons = find_airport_cylinder_intersection(
FLIGHT_ID_2, LATS_2, LONS_2, AIRPORT_ID_2, RADIUS_1, False)
self.assertEqual(1, len(lats))
self.assertEqual(1, len(lons))
def test_load_user_sectors_from_file(self):
"""
Test we can load a set of user predefined sectors.
"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
make_user_test_sectors_file = make_test_data_file(
USER_DEFINED_AIRSPACES)
load_user_airspace(make_user_test_sectors_file(), context, connection)
# These vars are in the csv file.
ORG_ID = 'pru'
USER_ID1 = 'user1'
SECTOR_NAME_1 = 'test_sector-111'
ok, sectors = find_by_org_user_name(ORG_ID, USER_ID1, SECTOR_NAME_1)
self.assertTrue(ok)
self.assertEquals(1, len(sectors))
sector = sectors[0]
self.assertEquals(sector['radius'], 55560.0)
connection.close()
def remove_all_airports():
"""
Deletes all the airports in the airports table.
"""
log.info("removing all airports.")
try:
db_user_connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
except OperationalError:
log.error("Could not get connection, probaly the user does not exist")
return False
try:
schema_name = AsIs(ctx.CONTEXT[ctx.SCHEMA_NAME])
with db_user_connection.cursor() as cursor:
cursor.execute("DELETE FROM %s.airports;", [schema_name])
except ProgrammingError:
log.exception(
"Failed to delete the airports table data, perhaps it does not exist?"
)
return False
finally:
db_user_connection.close()
return True
def initialise_airports(airports_file_path, reset=False):
"""
Uses the provided file path to load an airports file,
must be csv.
If no airports file is found we return false.
Reset=True Remove all and replace with this file.
Reset=False Add these airports to the sectors table. Note,
this is not an update.
return True if we succeeded
A tuple of (False, message) if we fail
"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
if os.path.exists(airports_file_path):
if reset:
remove_all_airports()
load_airports(airports_file_path, context, connection)
return True
else:
return (False, "Path not found " + airports_file_path)
def initialise_fleet_data(fleet_file_path=None, reset=False):
"""
Uses the provided file path to load the fleet file csv file.
If no fleet file is found we return false.
Reset=True Remove all records and replace with this file.
Reset=False Add these fleet entries to the fleet table.
return True if we succeeded
A tuple of (False, message) if we fail
"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.REF_DB_USER)
context = ctx.CONTEXT
if os.path.exists(fleet_file_path):
if reset:
remove_all_reference_data()
load_fleet_data(fleet_file_path, context, connection)
connection.close()
return True
else:
return (False, "Path not found: " + fleet_file_path)
def initialise_user_airspace(user_sector_file_path, reset=False):
"""
Uses the provided file path to load the users sectors file,
may be csv or geojson.
If no sectors file is found we return false.
Reset=True Remove all and replace with this file.
Reset=False Add these sectors to the user sectors table. Note,
this is not an update.
return True if we succeeded
A tuple of (False, message) if we fail
"""
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
if os.path.exists(user_sector_file_path):
if reset:
remove_all_user_defined_sectors()
load_user_airspace(user_sector_file_path, context, connection)
return True
else:
return (False, "Path not found " + user_sector_file_path)
def simple_originates(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
AC_ID = "991"
AV_AIRSPACE_ID = "SQUARE_1_1"
AV_ICAO_STATE_ID = "LZ"
MIN_FLIGHT_LEVEL = "100"
MAX_FLIGHT_LEVEL = "200"
AV_NAME = "Square Sector Level 1"
SECTOR_TYPE = "ES"
OBJECT_ID = 2209999
USER_SECTOR_1_WKT = "POLYGON ((-0.3 50.5, 0.3 50.5, 0.3 49.5, -0.3 49.5, -0.3 50.5))"
# Upper layer of square sector
AC_ID = "992"
AV_AIRSPACE_ID_2 = "SQUARE_1_2"
AV_ICAO_STATE_ID = "LZ"
MIN_FLIGHT_LEVEL_2 = "200"
MAX_FLIGHT_LEVEL_2 = "250"
AV_NAME_2 = "Square Sector Level 2"
SECTOR_TYPE = "ES"
OBJECT_ID = 2209999
USER_SECTOR_2_WKT = "POLYGON ((-0.3 50.5, 0.3 50.5, 0.3 49.5, -0.3 49.5, -0.3 50.5))"
# Unrelated square sector
AC_ID = "993"
AV_AIRSPACE_ID_3 = "SQUARE_2_1"
AV_ICAO_STATE_ID = "LZ"
MIN_FLIGHT_LEVEL_2 = "200"
MAX_FLIGHT_LEVEL_2 = "250"
AV_NAME_3 = "Square Sector 2 Level 2"
SECTOR_TYPE = "ES"
OBJECT_ID = 2209999
USER_SECTOR_3_WKT = "POLYGON ((-0.3 45.5, 0.3 45.5, 0.3 41.5, -0.3 41.5, -0.3 45.5))"
FLIGHT_ID_1 = "west_to_east"
ORIGIN_POINT = make_point(0.15, 50.0, connection)
# A single sector as a square(ish)
sector_1 = [
AC_ID, AV_AIRSPACE_ID, AV_ICAO_STATE_ID, MIN_FLIGHT_LEVEL,
MAX_FLIGHT_LEVEL, AV_NAME, SECTOR_TYPE, OBJECT_ID,
USER_SECTOR_1_WKT
]
sector_2 = [
AC_ID, AV_AIRSPACE_ID_2, AV_ICAO_STATE_ID, MIN_FLIGHT_LEVEL_2,
MAX_FLIGHT_LEVEL_2, AV_NAME_2, SECTOR_TYPE, OBJECT_ID,
USER_SECTOR_2_WKT
]
sector_3 = [
AC_ID, AV_AIRSPACE_ID_3, AV_ICAO_STATE_ID, MIN_FLIGHT_LEVEL_2,
MAX_FLIGHT_LEVEL_2, AV_NAME_3, SECTOR_TYPE, OBJECT_ID,
USER_SECTOR_3_WKT
]
# Add to the sectors database
ok, sector_1_id = add_airspace_geometry(sector_1, context, connection)
self.assertTrue(ok)
ok, sector_2_id = add_airspace_geometry(sector_2, context, connection)
self.assertTrue(ok)
ok, sector_3_id = add_airspace_geometry(sector_3, context, connection)
self.assertTrue(ok)
self.assertTrue(
originates(ORIGIN_POINT, USER_SECTOR_1_WKT, FLIGHT_ID_1,
sector_1_id, connection))
self.assertTrue(
originates(ORIGIN_POINT, USER_SECTOR_2_WKT, FLIGHT_ID_1,
sector_2_id, connection))
self.assertFalse(
originates(ORIGIN_POINT, USER_SECTOR_3_WKT, FLIGHT_ID_1,
sector_3_id, connection))
def test_find_by_id(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
sector_1 = [
self.AC_ID, self.AV_AIRSPACE_ID, self.AV_ICAO_STATE_ID,
self.MIN_FLIGHT_LEVEL, self.MAX_FLIGHT_LEVEL, self.AV_NAME,
self.SECTOR_TYPE, self.OBJECT_ID, self.USER_SECTOR_1_WKT
]
ok, sector_1_id = add_airspace_geometry(sector_1, context, connection)
self.assertTrue(ok)
res = find_airspace_by_database_ID(sector_1_id,
context,
connection,
is_user_defined=False)
self.assertEquals(1, len(res))
sector = res[0]
self.assertEquals(11, len(sector))
user_sector_1 = [
self.ORG_ID, self.USER_ID, self.SECTOR_NAME, self.LAT, self.LON,
self.RAD, self.USER_MIN_FLIGHT_LEVEL, self.USER_MAX_FLIGHT_LEVEL,
False, self.USER_SECTOR_1_WKT
]
ok, user_sector_1_id = add_user_sector(user_sector_1, context,
connection)
self.assertTrue(ok)
res = find_airspace_by_database_ID(user_sector_1_id,
context,
connection,
is_user_defined=True)
self.assertEquals(1, len(res))
user_sector = res[0]
self.assertEquals(12, len(user_sector))
self.assertEquals(self.ORG_ID, user_sector['org_id'])
self.assertEquals(self.USER_ID, user_sector['user_id'])
self.assertEquals(self.SECTOR_NAME, user_sector['sector_name'])
self.assertEquals(
int(self.USER_MIN_FLIGHT_LEVEL) * 100, user_sector['min_altitude'])
user_sector_2 = [
self.ORG_ID, self.USER_ID, self.SECTOR_NAME_2, self.LAT, self.LON,
self.RAD, self.USER_MIN_FLIGHT_LEVEL, self.USER_MAX_FLIGHT_LEVEL,
True, self.USER_SECTOR_1_WKT
]
ok, user_sector_2_id = add_user_sector(user_sector_2, context,
connection)
self.assertTrue(ok)
res = find_airspace_by_database_ID(user_sector_2_id,
context,
connection,
is_user_defined=True)
self.assertEquals(1, len(res))
user_sector_2 = res[0]
self.assertEquals(12, len(user_sector))
self.assertEquals(self.ORG_ID, user_sector_2['org_id'])
self.assertEquals(self.USER_ID, user_sector_2['user_id'])
self.assertEquals(self.SECTOR_NAME_2, user_sector_2['sector_name'])
self.assertEquals(
int(self.USER_MIN_FLIGHT_LEVEL) * 100,
user_sector_2['min_altitude'])
def test_find_standard_sector_intersections(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
# Define a trajectory
FLIGHT_ID_1 = "test-id-1"
MIN_ALT_1 = 10090
MAX_ALT_1 = 20090
LATS_1 = [
50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0
]
LONS_1 = [-0.5, -0.4, -0.3, -0.2, -0.1, 0.0, 0.1, 0.2, 0.3, 0.4, 0.5]
# This one is above our test sector
FLIGHT_ID_2 = "test-id-2"
MIN_ALT_2 = 30000
MAX_ALT_2 = 60000
LATS_2 = [
50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0
]
LONS_2 = [-0.5, -0.4, -0.3, -0.2, -0.1, 0.0, 0.1, 0.2, 0.3, 0.4, 0.5]
# Define a sector like the csv sectors in elementary_sectors.csv
AC_ID = "991"
AV_AIRSPACE_ID = "ZZPMPMZZAA"
AV_ICAO_STATE_ID = "LZ"
MIN_FLIGHT_LEVEL = "100"
MAX_FLIGHT_LEVEL = "200"
AV_NAME = "Made Up Sector"
SECTOR_TYPE = "ES"
OBJECT_ID = 2209999
USER_SECTOR_1_WKT = "POLYGON ((-0.3 50.5, 0.3 50.5, 0.3 49.5, -0.3 49.5, -0.3 50.5))"
# A single row from the csv file
sector_1 = [
AC_ID, AV_AIRSPACE_ID, AV_ICAO_STATE_ID, MIN_FLIGHT_LEVEL,
MAX_FLIGHT_LEVEL, AV_NAME, SECTOR_TYPE, OBJECT_ID,
USER_SECTOR_1_WKT
]
# Add to the sectors database
ok, sector_1_id = add_airspace_geometry(sector_1, context, connection)
self.assertTrue(ok)
# Find known intersecting trajectory intersections
res1 = find_horizontal_sector_intersections(FLIGHT_ID_1, LATS_1,
LONS_1, MIN_ALT_1,
MAX_ALT_1)
self.assertTrue(res1)
# There should be at least one intersection with our artifical sector
self.assertTrue(str(sector_1_id) in res1[2])
# There should be 2 intersections with our sector, in and out, count
# the occurances of our sector id in the result
self.assertEquals(
2, len([id for id in res1[2] if str(sector_1_id) == id]))
# recover the sector ids
recovered_sector_id_1 = [
id for id in res1[2] if str(sector_1_id) == id
][0]
recovered_sector_id_2 = [
id for id in res1[2] if str(sector_1_id) == id
][1]
# Find the sector descriptive name
self.assertEquals(
"LZ/Made Up Sector/" + str(recovered_sector_id_1) + "/ZZPMPMZZAA",
get_elementary_airspace_name(recovered_sector_id_1))
self.assertEquals(
"LZ/Made Up Sector/" + str(recovered_sector_id_2) + "/ZZPMPMZZAA",
get_elementary_airspace_name(recovered_sector_id_2))
# Find the sector altitude ranges
self.assertEquals(
(10000, 20000),
get_elementary_airspace_altitude_range(recovered_sector_id_1))
self.assertEquals(
(10000, 20000),
get_elementary_airspace_altitude_range(recovered_sector_id_2))
# Find known non-intersecting trajectory intersections
res2 = find_horizontal_sector_intersections(FLIGHT_ID_2, LATS_2,
LONS_2, MIN_ALT_2,
MAX_ALT_2)
# There should be no intersections with our artificial sector
self.assertFalse(str(sector_1_id) in res2[2])
connection.close()
def test_find_qudrant_intersections(self):
"""
Test against a four section (quadrants) airspace.
See Trajectories Production Airspace Intersections Report Test
Sectors, page 18.
"""
AC_ID_A1 = "990"
AV_AIRSPACE_ID_A1 = "SYNTHA1"
AV_ICAO_STATE_ID_A1 = "CS"
MIN_FLIGHT_LEVEL_A1 = "0"
MAX_FLIGHT_LEVEL_A1 = "200"
AV_NAME_A1 = "Square A1"
SECTOR_TYPE_A1 = "ES"
OBJECT_ID_A1 = 2399999
SECTOR_A1_WKT = "POLYGON Z (( 0.00000000 0.00000000 0.00000000, " + \
"1.00000000 0.00000000 0.00000000, " + \
"1.00000000 1.00000000 0.00000000, " + \
"0.00000000 1.000000000 0.00000000, " + \
"0.00000000 0.00000000 0.00000000))"
AC_ID_A2 = "989"
AV_AIRSPACE_ID_A2 = "SYNTHA2"
AV_ICAO_STATE_ID_A2 = "CS"
MIN_FLIGHT_LEVEL_A2 = "0"
MAX_FLIGHT_LEVEL_A2 = "200"
AV_NAME_A2 = "Square A2"
SECTOR_TYPE_A2 = "ES"
OBJECT_ID_A2 = 2399999
SECTOR_A2_WKT = "POLYGON Z (( 0.00000000 0.00000000 0.00000000, " + \
"-1.000000000 0.000000000 0.00000000, " + \
"-1.00000000 1.00000000 0.00000000, " + \
"0.000000000 1.00000000 0.00000000, " + \
"0.00000000 0.00000000 0.00000000))"
AC_ID_A3 = "979"
AV_AIRSPACE_ID_A3 = "SYNTHA3"
AV_ICAO_STATE_ID_A3 = "CS"
MIN_FLIGHT_LEVEL_A3 = "0"
MAX_FLIGHT_LEVEL_A3 = "200"
AV_NAME_A3 = "Square A3"
SECTOR_TYPE_A3 = "ES"
OBJECT_ID_A3 = 2399999
SECTOR_A3_WKT = "POLYGON Z (( 0.00000000 0.00000000 0.00000000, " + \
"-1.000000000 0.000000000 0.00000000, " + \
"-1.00000000 -1.00000000 0.00000000, " + \
"0.000000000 -1.00000000 0.00000000, " + \
"0.00000000 0.00000000 0.00000000))"
AC_ID_A4 = "969"
AV_AIRSPACE_ID_A4 = "SYNTHA4"
AV_ICAO_STATE_ID_A4 = "CS"
MIN_FLIGHT_LEVEL_A4 = "0"
MAX_FLIGHT_LEVEL_A4 = "200"
AV_NAME_A4 = "Square A3"
SECTOR_TYPE_A4 = "ES"
OBJECT_ID_A4 = 2399999
SECTOR_A4_WKT = "POLYGON Z (( 0.00000000 0.00000000 0.00000000, " + \
"1.000000000 0.000000000 0.00000000, " + \
"1.00000000 -1.00000000 0.00000000, " + \
"0.000000000 -1.00000000 0.00000000, " + \
"0.00000000 0.00000000 0.00000000))"
sector_A1 = [
AC_ID_A1, AV_AIRSPACE_ID_A1, AV_ICAO_STATE_ID_A1,
MIN_FLIGHT_LEVEL_A1, MAX_FLIGHT_LEVEL_A1, AV_NAME_A1,
SECTOR_TYPE_A1, OBJECT_ID_A1, SECTOR_A1_WKT
]
sector_A2 = [
AC_ID_A2, AV_AIRSPACE_ID_A2, AV_ICAO_STATE_ID_A2,
MIN_FLIGHT_LEVEL_A2, MAX_FLIGHT_LEVEL_A2, AV_NAME_A2,
SECTOR_TYPE_A2, OBJECT_ID_A2, SECTOR_A2_WKT
]
sector_A3 = [
AC_ID_A3, AV_AIRSPACE_ID_A3, AV_ICAO_STATE_ID_A3,
MIN_FLIGHT_LEVEL_A3, MAX_FLIGHT_LEVEL_A3, AV_NAME_A3,
SECTOR_TYPE_A3, OBJECT_ID_A3, SECTOR_A3_WKT
]
sector_A4 = [
AC_ID_A4, AV_AIRSPACE_ID_A4, AV_ICAO_STATE_ID_A4,
MIN_FLIGHT_LEVEL_A4, MAX_FLIGHT_LEVEL_A4, AV_NAME_A4,
SECTOR_TYPE_A4, OBJECT_ID_A4, SECTOR_A4_WKT
]
AC_ID_B1 = "990"
AV_AIRSPACE_ID_B1 = "SYNTHB1"
AV_ICAO_STATE_ID_1 = "CS"
MIN_FLIGHT_LEVEL_1 = "0"
MAX_FLIGHT_LEVEL_1 = "200"
AV_NAME_1 = "Square B1"
SECTOR_TYPE_1 = "ES"
OBJECT_ID_1 = 2399999
SECTOR_B1_WKT = "POLYGON Z (( 0.00000000 0.00000000 0.00000000, " + \
"1.00000000 0.00000000 0.00000000, " + \
"1.00000000 1.00000000 0.00000000, " + \
"0.00000000 1.000000000 0.00000000, " + \
"0.00000000 0.00000000 0.00000000))"
AC_ID_B2 = "989"
AV_AIRSPACE_ID_B2 = "SYNTHB2"
AV_ICAO_STATE_ID_2 = "CS"
MIN_FLIGHT_LEVEL_2 = "0"
MAX_FLIGHT_LEVEL_2 = "200"
AV_NAME_2 = "Square B2"
SECTOR_TYPE_2 = "ES"
OBJECT_ID_2 = 2399999
SECTOR_B2_WKT = "POLYGON Z (( 0.00000000 0.00000000 0.00000000, " + \
"-1.000000000 0.000000000 0.00000000, " + \
"-1.00000000 1.00000000 0.00000000, " + \
"0.000000000 1.00000000 0.00000000, " + \
"0.00000000 0.00000000 0.00000000))"
AC_ID_B3 = "979"
AV_AIRSPACE_ID_B3 = "SYNTHB3"
AV_ICAO_STATE_ID_3 = "CS"
MIN_FLIGHT_LEVEL_3 = "0"
MAX_FLIGHT_LEVEL_3 = "200"
AV_NAME_3 = "Square B3"
SECTOR_TYPE_3 = "ES"
OBJECT_ID_3 = 2399999
SECTOR_B3_WKT = "POLYGON Z (( 0.00000000 0.00000000 0.00000000, " + \
"-1.000000000 0.000000000 0.00000000, " + \
"-1.00000000 -1.00000000 0.00000000, " + \
"0.000000000 -1.00000000 0.00000000, " + \
"0.00000000 0.00000000 0.00000000))"
AC_ID_B4 = "969"
AV_AIRSPACE_ID_B4 = "SYNTHB4"
AV_ICAO_STATE_ID_4 = "CS"
MIN_FLIGHT_LEVEL_4 = "0"
MAX_FLIGHT_LEVEL_4 = "200"
AV_NAME_4 = "Square B4"
SECTOR_TYPE_4 = "ES"
OBJECT_ID_4 = 2399999
SECTOR_B4_WKT = "POLYGON Z (( 0.00000000 0.00000000 0.00000000, " + \
"1.000000000 0.000000000 0.00000000, " + \
"1.00000000 -1.00000000 0.00000000, " + \
"0.000000000 -1.00000000 0.00000000, " + \
"0.00000000 0.00000000 0.00000000))"
sector_B1 = [
AC_ID_B1, AV_AIRSPACE_ID_B1, AV_ICAO_STATE_ID_1,
MIN_FLIGHT_LEVEL_1, MAX_FLIGHT_LEVEL_1, AV_NAME_1, SECTOR_TYPE_1,
OBJECT_ID_1, SECTOR_B1_WKT
]
sector_B2 = [
AC_ID_B2, AV_AIRSPACE_ID_B2, AV_ICAO_STATE_ID_2,
MIN_FLIGHT_LEVEL_2, MAX_FLIGHT_LEVEL_2, AV_NAME_2, SECTOR_TYPE_2,
OBJECT_ID_2, SECTOR_B2_WKT
]
sector_B3 = [
AC_ID_B3, AV_AIRSPACE_ID_B3, AV_ICAO_STATE_ID_3,
MIN_FLIGHT_LEVEL_3, MAX_FLIGHT_LEVEL_3, AV_NAME_3, SECTOR_TYPE_3,
OBJECT_ID_3, SECTOR_B3_WKT
]
sector_B4 = [
AC_ID_B4, AV_AIRSPACE_ID_B4, AV_ICAO_STATE_ID_4,
MIN_FLIGHT_LEVEL_4, MAX_FLIGHT_LEVEL_4, AV_NAME_4, SECTOR_TYPE_4,
OBJECT_ID_4, SECTOR_B4_WKT
]
# test trajectory, southwest to north east
FLIGHT_ID_1 = "sw-ne"
MIN_ALT_1 = 0
MAX_ALT_1 = 600
LATS_1 = [-1.5, -1.0, -0.5, 0.0, 0.5, 1.0, 1.5]
LONS_1 = [-1.5, -1.0, -0.5, 0.0, 0.5, 1.0, 1.5]
FLIGHT_SW_NE = (FLIGHT_ID_1, LATS_1, LONS_1, MIN_ALT_1, MAX_ALT_1)
# test trajectory, south to north
FLIGHT_ID_2 = "s-n"
MIN_ALT_2 = 0
MAX_ALT_2 = 600
LATS_2 = [-1.5, -1.0, -0.5, 0.0, 0.5, 1.0, 1.5]
LONS_2 = [0.5, 0.5, 0.5, 0.5, 0.5, 0.5, 0.5]
FLIGHT_S_N = (FLIGHT_ID_2, LATS_2, LONS_2, MIN_ALT_2, MAX_ALT_2)
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
# Start with no sectors
remove_all_sectors()
ok1, sector_1_id = add_airspace_geometry(sector_B1, context,
connection)
self.assertTrue(ok1)
ok2, sector_2_id = add_airspace_geometry(sector_B2, context,
connection)
self.assertTrue(ok2)
ok3, sector_3_id = add_airspace_geometry(sector_B3, context,
connection)
self.assertTrue(ok3)
ok4, sector_4_id = add_airspace_geometry(sector_B4, context,
connection)
self.assertTrue(ok4)
quad_sectors = [sector_1_id, sector_2_id, sector_3_id, sector_4_id]
sector_b_sw_ne_intersections = find_horizontal_sector_intersections(
*FLIGHT_SW_NE)
# We should find six points of intersection, 1 at the sw corner, 4 in
# the middle and 1 at the ne corner.
self.assertEquals(6, len(sector_b_sw_ne_intersections[0]))
sector_b_s_n_intersections = find_horizontal_sector_intersections(
*FLIGHT_S_N)
# Should find 4 intersections, 1 at southern boundary,
# 2 in the common boundary and 1 at the northern boundary.
self.assertEquals(4, len(sector_b_s_n_intersections[0]))
def test_find_user_sector_intersections_non_cylinder(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
remove_all_sectors()
# Define a trajectory
FLIGHT_ID_1 = "test-id-1"
MIN_ALT_1 = 1009
MAX_ALT_1 = 2009
LATS_1 = [
50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0
]
LONS_1 = [-0.5, -0.4, -0.3, -0.2, -0.1, 0.0, 0.1, 0.2, 0.3, 0.4, 0.5]
FLIGHT_ID_2 = "test-id-2"
MIN_ALT_2 = 300
MAX_ALT_2 = 500
LATS_2 = [
50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0
]
LONS_2 = [-0.5, -0.4, -0.3, -0.2, -0.1, 0.0, 0.1, 0.2, 0.3, 0.4, 0.5]
# Define a custom sector
ORG_ID = "PRU"
USER_ID = "user-name-1"
SECTOR_ID = "test-sector-1-poly"
MIN_FL = 10
MAX_FL = 150
LAT = None
LON = None
RADIUS = 0
IS_CYLINDER = False
USER_SECTOR_1_WKT = "POLYGON ((-0.3 50.5, 0.3 50.5, 0.3 49.5, -0.3 49.5, -0.3 50.5))"
user_sector_1 = [
ORG_ID, USER_ID, SECTOR_ID, LAT, LON, RADIUS, MIN_FL, MAX_FL,
IS_CYLINDER, USER_SECTOR_1_WKT
]
# Add a custom sector to the user sectors
ok, sector_1_id = add_user_sector(user_sector_1, context, connection)
self.assertTrue(ok)
# Find known intersecting trajectory intersections
res1 = find_horizontal_user_airspace_intersections(
FLIGHT_ID_1, LATS_1, LONS_1, MIN_ALT_1, MAX_ALT_1)
self.assertTrue(res1)
# There should be at least one intersection with our artifical sector
self.assertTrue(str(sector_1_id) in res1[2])
# recover the sector ids
recovered_sector_id_1 = [
id for id in res1[2] if str(sector_1_id) == id
][0]
recovered_sector_id_2 = [
id for id in res1[2] if str(sector_1_id) == id
][1]
# Find the sector descriptive name
self.assertEquals("PRU/user-name-1/test-sector-1-poly",
get_user_sector_name(recovered_sector_id_1))
self.assertEquals("PRU/user-name-1/test-sector-1-poly",
get_user_sector_name(recovered_sector_id_2))
# Find the sector altitude ranges
self.assertEquals(
(1000, 15000),
get_user_sector_altitude_range(recovered_sector_id_1))
self.assertEquals(
(1000, 15000),
get_user_sector_altitude_range(recovered_sector_id_2))
# Find known non-intersecting trajectory intersections
res2 = find_horizontal_user_airspace_intersections(
FLIGHT_ID_2, LATS_2, LONS_2, MIN_ALT_2, MAX_ALT_2)
# There should be no intersections with our artificial sector
self.assertFalse(str(sector_1_id) in res2[2])
connection.close()
def test_find_user_cylinder_intersections(self):
connection = ctx.get_connection(ctx.CONTEXT, ctx.DB_USER)
context = ctx.CONTEXT
remove_all_sectors()
# Define a trajectory
FLIGHT_ID_1 = "test-id-1"
MIN_ALT_1 = 10090
MAX_ALT_1 = 20090
LATS_1 = [
50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0
]
LONS_1 = [-10.5, -0.4, -0.3, -0.2, -0.1, 0.0, 0.1, 0.2, 0.3, 0.4, 10.5]
# This one is above our test sector
FLIGHT_ID_2 = "test-id-2"
MIN_ALT_2 = 30000
MAX_ALT_2 = 60000
LATS_2 = [
50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0
]
LONS_2 = [-10.5, -0.4, -0.3, -0.2, -0.1, 0.0, 0.1, 0.2, 0.3, 0.4, 10.5]
# Define a custom sector
ORG_ID = "PRU"
USER_ID = "user-name-1"
SECTOR_ID = "test-cylinder_user_sector-1-poly"
MIN_FL = 10
MAX_FL = 150
LAT = 50.0
LON = 0.0
RADIUS = 20
IS_CYLINDER = "True"
USER_SECTOR_1_WKT = ""
user_cylinder_sector_1 = \
[ORG_ID, USER_ID, SECTOR_ID, LAT, LON, RADIUS, MIN_FL, MAX_FL,
IS_CYLINDER, USER_SECTOR_1_WKT]
# Add a custom sector to the user sectors
ok, sector_1_id = add_user_sector(user_cylinder_sector_1, context,
connection)
self.assertTrue(ok)
# Find known intersecting trajectory intersections
res1 = find_horizontal_user_airspace_intersections(
FLIGHT_ID_1, LATS_1, LONS_1, MIN_ALT_1, MAX_ALT_1)
self.assertTrue(res1)
# There should be at least one intersection with our artifical sector
self.assertTrue(str(sector_1_id) in res1[2])
# Find the sector descriptive name
self.assertEquals("PRU/user-name-1/test-cylinder_user_sector-1-poly",
get_user_sector_name(sector_1_id))
# Find the sector altitude range
self.assertEquals((1000, 15000),
get_user_sector_altitude_range(sector_1_id))
# Find known non-intersecting trajectory intersections
res2 = find_horizontal_user_airspace_intersections(
FLIGHT_ID_2, LATS_2, LONS_2, MIN_ALT_2, MAX_ALT_2)
# There should be no intersections with our artificial sector
self.assertFalse(str(sector_1_id) in res2[2])
connection.close()
