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 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 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 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_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"