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()