def dic(self):
return dict(apt_id=self.apt_id,
thr_ident=self.ident,
reciprocal=self.reciprocal,
thr_hdg=round(self.heading, 2),
thr_start=ut.mk_point(lon=self.lon, lat=self.lat),
thr_thr=ut.mk_point(lon=self.dlon, lat=self.dlat),
thr_displaced=self.displace,
thr_overrun=self.overrun,
thr_marking=self.marking,
thr_app_light_id=self.app_lights,
thr_tdz_lights=self.tdz_lights,
thr_reil=self.reil,
src_name=self.src_name,
src_src=self.src_src)
def update_gs(line):
"""Inserts a rowcode `6` ILS
.. code-block:: text
0 1 2 3 4 5 6 7 8 9 10
6 64.13363889 -021.94091667 48 10990 10 350175.260 IRK BIRK 19 GS
rc lat lon elev freq range heading idenr apt rwy type
"""
print "update_gs", line
parts = ut.xsplit(line)
#print len(parts), parts
if len(parts) < 10:
print "OOOOOOOOOPS", line
#YES
return
apt_ident = parts[8]
ident = parts[9]
sql = "select threshold.thr_id from threshold "
sql += "inner join airport on airport.apt_id = threshold.apt_id "
sql += " where airport.apt_ident = %(apt_ident)s and threshold.ident = %(ident)s "
db.Cur.execute(sql, dict(apt_ident=apt_ident, ident=ident))
rows = db.Cur.fetchall()
thr_id = None
ident_lost = ""
if len(rows) > 0:
## Found a matching threshold from apt_dats
thr_id = rows[0][0]
else:
## not row found so set ident_lost
ident_lost = "%s-%s" % (apt_ident, ident)
if thr_id:
## Update the threshold and om_id, im_id or mm_id
#col_id = "%s_id" % mcode.lower() # make mm_id from MM
point = ut.mk_point(lat=parts[1], lon=parts[2], alt=parts[3].strip())
## pete hack to calculate runwaycenter
#rwy_middle = Geodesic.WGS84.Direct(float(t0.lat),float(t0.lon), float(t0.heading), float(rwy.length_m / 2))
#hdgg = float(parts[6])
#dist = float(parts[5]) * 100
#//extent_po = Geodesic.WGS84.Direct(float(parts[1]),float(parts[2]), hdgg, dist)
# umm maybe we need to calc middle of displaced ??
extent = None #sut.mk_point(lat=str(extent_po["lat2"]), lon=str(extent_po["lon2"]), alt=parts[3].strip())
sql = "update threshold "
#sql += " set " + col_id + " = %(marker_id)s "
sql += " set " #ils_freq = %(freq)s, "
sql += " gs_deg = %(gs_deg)s, "
sql += " gs_point = ST_Transform(ST_GeomFromText(%(point)s, 4326),3857) "
#sql += " loc_extent = ST_Transform(ST_GeomFromText(%(extent)s, 4326),3857) "
sql += " where thr_id = %(thr_id)s "
#db.Cur.execute(sql, dict(thr_pk=thr_id, marker_id=marker_id, point=point))
db.Cur.execute(sql, dict(thr_id=thr_id, point=point, gs_deg=3))
db.Con.commit()
print "yes", apt_ident, ident
else:
print "no", apt_ident, ident
def insert_ndb(line, src_name):
"""Inserts a `2` NDB Non-directional beacon
"""
parts = ut.xsplit(line)
point = ut.mk_point(lat=parts[1], lon=parts[2], alt=parts[3].strip())
#elevation_ft = parts[3].strip()
#print parts
## Insert navaid
sqli = "insert into navaid("
sqli += " ident, name, ntype_id, freq, range_nm, search, "
sqli += " point, src_name, src_src "
sqli += " )values ("
sqli += " %(ident)s, %(name)s, %(navaid_type_id)s, %(freq)s, %(range_nm)s, %(search)s,"
sqli += " ST_Transform(ST_GeomFromText(%(point)s, 4326),3857), %(src_name)s, %(src_src)s "
sqli += ") returning navaid_id; "
name = " ".join(parts[8:-1])
ident = parts[7].strip()
params = dict(
ident=ident,
point=point,
navaid_type_id=2, #elevation_ft=parts[3].strip(),
freq=parts[4].strip(),
range_nm=parts[5].strip(),
name=name,
search=ident.upper() + name.upper(),
src_name=src_name,
src_src=line)
#print params
db.Cur.execute(sqli, params)
db.Con.commit()
navaid_pk = db.Cur.fetchone()[0]
#print "NDB=", parts[7].strip(), navaid_pk
return navaid_pk
def dic(self):
return dict(
apt_id=self.apt_id,
thr_ident=self.ident,
reciprocal=self.reciprocal,
thr_hdg=round(self.heading, 2),
thr_start=ut.mk_point(lon=self.lon, lat=self.lat),
thr_thr=ut.mk_point(lon=self.dlon, lat=self.dlat),
thr_displaced=self.displace,
thr_overrun=self.overrun,
thr_marking=self.marking,
thr_app_light_id=self.app_lights,
thr_tdz_lights=self.tdz_lights,
thr_reil=self.reil,
src_name=self.src_name,
src_src=self.src_src
)
def set_runway_hdg_length(rwy, line=None):
"""Caclulates and sets the threshold offset lines, threshold heading, disatance"""
t0 = rwy.thr0
t1 = rwy.thr1
## We calculate the runway length as distance between two thresholds
## but does this allow for displacements ??
# maybe it should all be metres in db and convert on the fly
rwy_calc = Geodesic.WGS84.Inverse(float(t0.lat), float(t0.lon),
float(t1.lat), float(t1.lon))
rwy.length_m = rwy_calc.get("s12")
#rwy_len_ft = rwy_calc.get("s12") * 3.048
## the heading for this end
t0.heading = rwy_calc.get("azi2")
if t0.heading < 0:
t0.heading = rwy_calc.get("azi2") + 360
"""
print "=", t0.ident, rwy_calc.get("azi1"), rwy_calc.get("azi2")
print line
print float(t0.lat), float(t0.lon), float(t1.lat), float(t1.lon)
print "?", t0.ident, rwy_calc.get("azi2") + 360
print
"""
# Calculate the heading for other end
rwy_length_end = Geodesic.WGS84.Inverse(float(t1.lat), float(t1.lon),
float(t0.lat), float(t0.lon))
t1.heading = 360.0 + rwy_length_end.get("azi2")
## Caclulate the point for the threhold.. = start point, heading and distance in m
rwy_threshold_direct = Geodesic.WGS84.Direct(float(t0.lat), float(t0.lon),
float(t0.heading),
float(t0.displace))
t0.dlon = str(rwy_threshold_direct["lon2"])
t0.dlat = str(rwy_threshold_direct["lat2"])
rwy_threshold_direct_end = Geodesic.WGS84.Direct(
float(t1.lat), float(t1.lon), rwy_length_end.get("azi2"),
float(t1.displace))
t1.dlon = str(rwy_threshold_direct_end["lon2"])
t1.dlat = str(rwy_threshold_direct_end["lat2"])
## pete hack to calculate runwaycenter
rwy_middle = Geodesic.WGS84.Direct(float(t0.lat), float(t0.lon),
float(t0.heading),
float(rwy.length_m / 2))
# umm maybe we need to calc middle of displaced ??
rwy.center = ut.mk_point(lat=str(rwy_middle["lat2"]),
lon=str(rwy_middle["lon2"]))
#if t1.heading > 360:
# t1.heading = t1.heading - 360
if t0.heading > 360.00:
t0.heading = t0.heading - 360.00
if t1.heading > 360.00:
t1.heading = t1.heading - 360.00
def set_runway_hdg_length(rwy, line=None):
"""Caclulates and sets the threshold offset lines, threshold heading, disatance"""
t0 = rwy.thr0
t1 = rwy.thr1
## We calculate the runway length as distance between two thresholds
## but does this allow for displacements ??
# maybe it should all be metres in db and convert on the fly
rwy_calc = Geodesic.WGS84.Inverse(float(t0.lat), float(t0.lon), float(t1.lat), float(t1.lon))
rwy.length_m = rwy_calc.get("s12")
#rwy_len_ft = rwy_calc.get("s12") * 3.048
## the heading for this end
t0.heading = rwy_calc.get("azi2")
if t0.heading < 0:
t0.heading = rwy_calc.get("azi2") + 360
"""
print "=", t0.ident, rwy_calc.get("azi1"), rwy_calc.get("azi2")
print line
print float(t0.lat), float(t0.lon), float(t1.lat), float(t1.lon)
print "?", t0.ident, rwy_calc.get("azi2") + 360
print
"""
# Calculate the heading for other end
rwy_length_end = Geodesic.WGS84.Inverse(float(t1.lat), float(t1.lon), float(t0.lat), float(t0.lon))
t1.heading = 360.0 + rwy_length_end.get("azi2")
## Caclulate the point for the threhold.. = start point, heading and distance in m
rwy_threshold_direct = Geodesic.WGS84.Direct(float(t0.lat), float(t0.lon), float(t0.heading), float(t0.displace))
t0.dlon = str(rwy_threshold_direct["lon2"])
t0.dlat = str(rwy_threshold_direct["lat2"])
rwy_threshold_direct_end = Geodesic.WGS84.Direct(float(t1.lat), float(t1.lon), rwy_length_end.get("azi2"),
float(t1.displace))
t1.dlon = str(rwy_threshold_direct_end["lon2"])
t1.dlat = str(rwy_threshold_direct_end["lat2"])
## pete hack to calculate runwaycenter
rwy_middle = Geodesic.WGS84.Direct(float(t0.lat), float(t0.lon), float(t0.heading), float(rwy.length_m / 2))
# umm maybe we need to calc middle of displaced ??
rwy.center = ut.mk_point(lat=str(rwy_middle["lat2"]), lon=str(rwy_middle["lon2"]))
#if t1.heading > 360:
# t1.heading = t1.heading - 360
if t0.heading > 360.00:
t0.heading = t0.heading - 360.00
if t1.heading > 360.00:
t1.heading = t1.heading - 360.00
def insert_marker(line):
"""Insert a marker
The threshold table is queried to find the esiting threshod from apt_dat
If found the then marker is inserted and one of mm_id, om_id, im_id updated
Else the marker is inserted and "ident_lost" set to the unfound matching runway
"""
#print "line=", line
parts = ut.xsplit(line)
#print parts
apt_ident = parts[8]
ident = parts[9]
sql = "select threshold.thr_id from threshold "
sql += "inner join airport on airport.apt_id = threshold.apt_id "
sql += " where airport.apt_ident = %(apt_ident)s and threshold.ident = %(ident)s "
db.Cur.execute(sql, dict(apt_ident=apt_ident, ident=ident))
rows = db.Cur.fetchall()
#print "MM=", rows, len(rows)
thr_id = None
ident_lost = ""
if len(rows) > 0:
## Found a matching threshold from apt_dat
thr_id = rows[0][0]
else:
## not row found so set ident_lost
ident_lost = "%s-%s" % (apt_ident, ident)
## Set the marker ID (maybe same as row_code ?)
mcode = parts[-1]
if mcode == "IM":
mt_id = 9
elif mcode == "MM":
mt_id = 8
elif mcode == "OM":
mt_id = 7
if thr_id:
## Update the threshold and om_id, im_id or mm_id
#col_id = "%s_id" % mcode.lower() # make mm_id from MM
col_id = mcode.lower() # make mm_id from MM
point = ut.mk_point(lat=parts[1], lon=parts[2], alt=parts[3].strip())
sql = "update threshold "
#sql += " set " + col_id + " = %(marker_id)s "
sql += " set " + col_id + " = ST_Transform(ST_GeomFromText(%(point)s, 4326),3857) "
sql += " where thr_id = %(thr_id)s"
#db.Cur.execute(sql, dict(thr_pk=thr_id, marker_id=marker_id, point=point))
db.Cur.execute(sql, dict(thr_id=thr_id, point=point))
db.Con.commit()
def import_awy_dat(file_path):
inputfile = file_path + "/earth_awy.dat"
c = 0
with open(inputfile) as readnav:
for line in readnav:
c += 1
# Skip first three lines, hope Robin Peel will never change this behaviour ;-)
if c < 4:
pass
else:
parts = ut.xsplit(line)
if parts[0] == "99":
return
## 0 1 2 3 4 5 6 7 8 9
## 00UPP 20.566668 -154.125000 FITES 20.794556 -153.000633 1 210 460 R578
dic = dict(s_ident=parts[0],
s_point=ut.mk_point(lat=parts[1], lon=parts[2]),
e_ident=parts[3],
e_point=ut.mk_point(lat=parts[4], lon=parts[5]),
awy=parts[6],
bottom=parts[7],
top=parts[8],
airways=parts[9].split("-"))
#print dic
insert_temp(**dic)
## We commit every x to make faster
if c % 5000 == 0:
print " > fix at = %s of %s - %s" % (c, MAX_LINES_GUESS,
parts[0])
db.Con.commit()
## commit any outstanding after rows at end of loop
db.Con.commit()
def import_awy_dat(file_path):
inputfile = file_path + "/earth_awy.dat"
c = 0
with open(inputfile) as readnav:
for line in readnav:
c += 1
# Skip first three lines, hope Robin Peel will never change this behaviour ;-)
if c < 4:
pass
else:
parts = ut.xsplit(line)
if parts[0] == "99":
return
## 0 1 2 3 4 5 6 7 8 9
## 00UPP 20.566668 -154.125000 FITES 20.794556 -153.000633 1 210 460 R578
dic = dict( s_ident = parts[0],
s_point = ut.mk_point(lat=parts[1], lon=parts[2]),
e_ident = parts[3],
e_point = ut.mk_point(lat=parts[4], lon=parts[5]),
awy=parts[6],
bottom=parts[7], top=parts[8],
airways=parts[9].split("-")
)
#print dic
insert_temp( **dic )
## We commit every x to make faster
if c % 5000 == 0:
print " > fix at = %s of %s - %s" %( c, MAX_LINES_GUESS, parts[0])
db.Con.commit()
## commit any outstanding after rows at end of loop
db.Con.commit()
def insert_fix(**kwargs):
"""Insert a fix into the `fix` table"""
kwargs['fix_center'] = ut.mk_point(lon=kwargs['lon'], lat=kwargs['lat'], alt=0)
"""
sql = "insert into fix(ident, major, point) values("
sql += "%(ident)s, %(major)s, "
sql += "ST_Transform(ST_GeomFromText(%(fix_center)s, 4326),3857));"
"""
sql = "insert into navaid(ident, name, search, ntype_id, point) values("
sql += "%(ident)s, %(ident)s, %(ident)s, 201, "
sql += "ST_Transform(ST_GeomFromText(%(fix_center)s, 4326),3857));"
#print sql
db.Cur.execute(sql, kwargs)
def db_write_waypoints(proc_id, waypoints):
for idx, wp in enumerate(waypoints):
print "INS_", wp
sql = "insert into procedure_wp( "
sql += " proc_id, wp_no, ident, alt_ft, alt_restriction, point "
sql += ") values ("
sql += " %(proc_id)s, %(wp_no)s, %(ident)s, %(alt_ft)s, %(alt_restriction)s, "
sql += "ST_Transform(ST_GeomFromText(%(point)s, 4326),3857)"
sql += ");"
pnt =ut.mk_point(lon=wp['longitude'], lat=wp['latitude'], alt=wp['altitude'])
wdic = dict(proc_id=proc_id, ident=wp['name'], wp_no=idx,
alt_restriction=wp['altituderestriction'], alt_ft=wp['altitude'], point=pnt)
db.Cur.execute(sql, wdic)
db.Con.commit()
def insert_sign(apt_id, line):
#print line
#print "20 22.32152700 114.19750500 224.10 0 3 {@Y,^l}31-13{^r}"
parts = ut.xsplit(line)
point = ut.mk_point(lat=parts[1], lon=parts[2])
params = dict(apt_id=apt_id,
center=point, orientation=parts[3],
size_id=parts[5], text=" ".join(parts[6:]))
#print parts
#print fii
sql = "insert into sign("
sql += "apt_id, text, size_id, orientation, point "
sql += ")values("
sql += " %(apt_id)s, %(text)s, %(size_id)s,%(orientation)s, "
sql += " ST_Transform(ST_GeomFromText(%(center)s, 4326),3857) "
sql += ");"
db.Cur.execute(sql, params)
db.Con.commit()
def db_write_waypoints(proc_id, waypoints):
for idx, wp in enumerate(waypoints):
print "INS_", wp
sql = "insert into procedure_wp( "
sql += " proc_id, wp_no, ident, alt_ft, alt_restriction, point "
sql += ") values ("
sql += " %(proc_id)s, %(wp_no)s, %(ident)s, %(alt_ft)s, %(alt_restriction)s, "
sql += "ST_Transform(ST_GeomFromText(%(point)s, 4326),3857)"
sql += ");"
pnt = ut.mk_point(lon=wp['longitude'],
lat=wp['latitude'],
alt=wp['altitude'])
wdic = dict(proc_id=proc_id,
ident=wp['name'],
wp_no=idx,
alt_restriction=wp['altituderestriction'],
alt_ft=wp['altitude'],
point=pnt)
db.Cur.execute(sql, wdic)
db.Con.commit()
def insert_sign(apt_id, line):
#print line
#print "20 22.32152700 114.19750500 224.10 0 3 {@Y,^l}31-13{^r}"
parts = ut.xsplit(line)
point = ut.mk_point(lat=parts[1], lon=parts[2])
params = dict(apt_id=apt_id,
center=point,
orientation=parts[3],
size_id=parts[5],
text=" ".join(parts[6:]))
#print parts
#print fii
sql = "insert into sign("
sql += "apt_id, text, size_id, orientation, point "
sql += ")values("
sql += " %(apt_id)s, %(text)s, %(size_id)s,%(orientation)s, "
sql += " ST_Transform(ST_GeomFromText(%(center)s, 4326),3857) "
sql += ");"
db.Cur.execute(sql, params)
db.Con.commit()
def insert_dme(line, src_name):
"""Inserts a rowcode `3` VOR directional beacon
.. code-block:: text
3 43.88802800 000.87286100 896 11480 120 -2.0 AGN AGEN-Gaudonville VOR-DME
rc lat lon
"""
parts = ut.xsplit(line)
point = ut.mk_point(lat=parts[1], lon=parts[2], alt=parts[3].strip())
#print parts
## Insert navaid
sqli = "insert into navaid("
sqli += " ident, name, ntype_id, freq, range_nm, search, "
sqli += " point, src_name, src_src "
sqli += " )values ("
sqli += " %(ident)s, %(name)s, %(ntype_id)s, %(freq)s, %(range_nm)s, %(search)s,"
sqli += " ST_Transform(ST_GeomFromText(%(point)s, 4326),3857), %(src_name)s, %(src_src)s "
sqli += ") returning navaid_id; "
name = " ".join(parts[8:-1])
ident = parts[7].strip()
params = dict(ident=ident,
point=point,
ntype_id=12,
freq=parts[4].strip(),
range_nm=parts[5].strip(),
name=name,
search=ident.upper() + name.upper(),
src_name=src_name,
src_src=line)
#print params
db.Cur.execute(sqli, params)
#db.Con.commit()
marker_id = db.Cur.fetchone()[0]
def update_ils(line):
"""Inserts a rowcode `4` ILS
.. code-block:: text
0 1 2 3 4 5 6 7 8 9 10
4 42.37710200 -071.02169100 19 11070 18 315.261 ILIP KBOS 33L ILS-cat-I
rc lat lon elev freq range heading idenr apt rwy type
"""
print "update_ils", line
parts = ut.xsplit(line)
#print parts
apt_ident = parts[8]
ident = parts[9]
sql = "select threshold.thr_id from threshold "
sql += "inner join airport on airport.apt_id = threshold.apt_id "
sql += " where airport.apt_ident = %(apt_ident)s and threshold.ident = %(ident)s "
db.Cur.execute(sql, dict(apt_ident=apt_ident, ident=ident))
rows = db.Cur.fetchall()
thr_id = None
ident_lost = ""
if len(rows) > 0:
## Found a matching threshold from apt_dats
thr_id = rows[0][0]
else:
## not row found so set ident_lost
ident_lost = "%s-%s" % (apt_ident, ident)
if thr_id:
## Update the threshold and om_id, im_id or mm_id
#col_id = "%s_id" % mcode.lower() # make mm_id from MM
point = ut.mk_point(lat=parts[1], lon=parts[2], alt=parts[3].strip())
## pete hack to calculate runwaycenter
#rwy_middle = Geodesic.WGS84.Direct(float(t0.lat),float(t0.lon), float(t0.heading), float(rwy.length_m / 2))
hdgg = float(parts[6]) + 180
if hdgg > 360:
hdgg = hdgg - 360
dist = float(parts[5]) * NM_2_M
extent_po = Geodesic.WGS84.Direct(float(parts[1]), float(parts[2]),
hdgg, dist)
# umm maybe we need to calc middle of displaced ??
extent = ut.mk_point(lat=str(extent_po["lat2"]),
lon=str(extent_po["lon2"]),
alt=parts[3].strip())
sql = "update threshold "
#sql += " set " + col_id + " = %(marker_id)s "
sql += " set ils_freq = %(freq)s, "
sql += " loc_hdg = %(loc_hdg)s, "
sql += " loc_point = ST_Transform(ST_GeomFromText(%(point)s, 4326),3857), "
sql += " loc_extent = ST_Transform(ST_GeomFromText(%(extent)s, 4326),3857) "
sql += " where thr_id = %(thr_id)s "
#db.Cur.execute(sql, dict(thr_pk=thr_id, marker_id=marker_id, point=point))
db.Cur.execute(
sql,
dict(thr_id=thr_id,
point=point,
freq=parts[4].strip(),
loc_hdg=parts[6].strip(),
loc_range=parts[5].strip(),
extent=extent))
db.Con.commit()
print "yes", hdgg, dist
else:
print "no"