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