FlightAware获取航迹数据，修正及入库。

最新推荐文章于 2024-05-07 12:51:22 发布

qq_1041

最新推荐文章于 2024-05-07 12:51:22 发布

阅读量4.4k

点赞数 23

文章标签： windows

本文链接：https://blog.csdn.net/qq_43141961/article/details/131913063

版权

数据获取

1.航迹数据从FlightAware - 航班跟踪/航班状态/飞行跟踪网站上获取。

2.点击地图上的一架飞机，进入到航班信息页面。

3.完整航迹数据一般从Past Flights里取；点击View Track Log，获取轨迹报表。

4.直接复制即可，复制到Excel表。

表格修正

1.航班对应的机场编号和中文名称，要自己手动添加。

2.表头添加总行数

3.表格内部有白格就添加NULL

4.米数部分如果有空白，要手动修正连贯；头尾数据空白，填上合适的值。

数据入库

数据导入用C#读取Excel表；导入到postgres。

表结构

表格类和点集类

namespace ImportAirs
{
    internal class AircraftTrailTable
    {
        public Guid id { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTimeOffset creationTime { get; set; }
        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTimeOffset updateTime { get; set; }
        /// <summary>
        /// 备注
        /// </summary>
        public string remarks { get; set; }
        /// <summary>
        /// 途径点集合
        /// </summary>
        public List<WayPointSet> wayPointSet = new List<WayPointSet>();
        /// <summary>
        /// 起点
        /// </summary>
        public string startingPoint { get; set; }
        /// <summary>
        /// 终点
        /// </summary>
        public string endPoint { get; set; }
    }

    public class WayPointSet
    {
        public double time { get; set; }
        public double longitude { get; set; }
        public double latitude { get; set; }
        public double altitude { get; set; }
    }
}

主代码

1.这里要自己根据表格的sheet数量设定循环数。

2.整体代码

using ImportAirs;
using Npgsql;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using System.Collections.Generic;


string conStr = "Host=localhost;Port=5432;Username=postgres;Password=123456;Database=yyfz";
NpgsqlConnection conn = new NpgsqlConnection(conStr);



string importExcelPath = "C:\\Users\\10412\\Desktop\\trail\\" + "trail.xlsx";
//System.IO.Directory.GetCurrentDirectory() + "\\" + "亚洲飞机航迹数据.xlsx";//"D:\\数据\\航班信息\\云南昆明.xlsx";//System.IO.Directory.GetCurrentDirectory() + "\\3dModels\\" + fileName;
//string exportExcelPath = "E:\\export.xlsx";

IWorkbook workbook = WorkbookFactory.Create(importExcelPath);

conn.Open();
for (int h = 0; h < 1; h++)
{
    ISheet sheet = workbook.GetSheetAt(h);//获取第一个工作薄

    string insertStr = $"INSERT INTO public.\"aircraftTrailTable_copy1\"" +
    $"( \"id\", \"creationTime\",\"updateTime\",\"startingPoint\",\"endPoint\",\"remarks\",\"wayPointSet\") " +
    $"VALUES(@id,@creationTime,@updateTime,@startingPoint,@endPoint,@remarks,@wayPointSet)";

    NpgsqlCommand cmd = new NpgsqlCommand(insertStr, conn);


    AircraftTrailTable cameraParameters = new AircraftTrailTable();

    string str = ((IRow)sheet.GetRow(0)).Cells[0].ToString();
    int number = Convert.ToInt32(((IRow)sheet.GetRow(0)).Cells[0].ToString());

    cameraParameters.startingPoint = ((IRow)sheet.GetRow(1)).Cells[0].ToString();
    cameraParameters.endPoint = ((IRow)sheet.GetRow(number - 2)).Cells[0].ToString();
    cameraParameters.remarks = ((IRow)sheet.GetRow(number - 1)).Cells[0].ToString();



    for (int i = 2; i < number - 2; i++)
    {



        IRow row = (IRow)sheet.GetRow(i);//获取第一行

        if (!(row.Cells[0].ToString()).Contains("Gap in available data"))
        {

            WayPointSet wayPointSet = new WayPointSet();

            wayPointSet.longitude = Convert.ToDouble(row.Cells[2].ToString());
            wayPointSet.latitude = Convert.ToDouble(row.Cells[1].ToString());
            //string strss = row.Cells[6].ToString();//Estimated
            wayPointSet.altitude = Convert.ToDouble(row.Cells[6].ToString());

            cameraParameters.wayPointSet.Add(wayPointSet);
        }


    }



    string[] array = new string[cameraParameters.wayPointSet.Count];

    Guid insertId = Guid.NewGuid();
    cmd.Parameters.AddWithValue("@id", insertId);
    cmd.Parameters.AddWithValue("@creationTime", DateTime.UtcNow);
    cmd.Parameters.AddWithValue("@updateTime", DateTime.UtcNow);
    cmd.Parameters.AddWithValue("@startingPoint", cameraParameters.startingPoint);
    cmd.Parameters.AddWithValue("@endPoint", cameraParameters.endPoint);
    cmd.Parameters.AddWithValue("@remarks", cameraParameters.remarks);


    for (int i = 0; i < cameraParameters.wayPointSet.Count; i++)
    {
     array[i] = cameraParameters.wayPointSet[i].longitude.ToString() + "," + cameraParameters.wayPointSet[i].latitude.ToString() + "," + cameraParameters.wayPointSet[i].altitude.ToString();
    }

    cmd.Parameters.AddWithValue("@wayPointSet", array);
    cmd.ExecuteNonQuery();

    //string paht = @"C:\Users\hy\Desktop\新建文件夹 (4)\chongQingBeiJingPath.json";
}
conn.Close();

Console.WriteLine("成功！");

算法修正

算法修正主要是为了去除航迹中的骤变点，包括经纬度修正和高度修正；用C#实现。

经纬度修正

1.飞行过程中三点向量夹角大于等于90°，判断轨迹发生急转。

2.坐标及角度余弦获取代码

        double Ax = Convert.ToDouble(b[0]) - Convert.ToDouble(a[0]);
        double Ay = Convert.ToDouble(b[1]) - Convert.ToDouble(a[1]);

        double Bx = Convert.ToDouble(c[0]) - Convert.ToDouble(b[0]);
        double By = Convert.ToDouble(c[1]) - Convert.ToDouble(b[1]);

        double cos = (Ax * Bx + Ay * By) / (Math.Sqrt(Ax * Ax + Ay * Ay) * Math.Sqrt(Bx * Bx + By * By));
        double angle = Math.Acos(cos);

高度修正

1.提升或降低中间点的高度，使高度平滑。

//高度修改
if (Convert.ToDouble(b[2]) < Convert.ToDouble(a[2]) && Convert.ToDouble(b[2]) < Convert.ToDouble(c[2])|| Convert.ToDouble(b[2]) > Convert.ToDouble(a[2]) && Convert.ToDouble(b[2]) > Convert.ToDouble(c[2]))
            {
                b[2] = Convert.ToString((Convert.ToDouble(a[2])+ Convert.ToDouble(c[2]))/4);
            }

代码部分

表格类和点集类

沿用数据导入部分的。

点类

namespace ConsoleApp1
{
    internal class PointD
    {

        public double X { get; set; }
        public double Y { get; set; }

        public PointD(double x, double y)
        {
            X = x;
            Y = y;
        }

        public PointD() { }

    }
}

数据库操作类

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace ConsoleApp1
{
    internal class CommRatePgSql
    {
        //测试AppSetting操作
        static string ConnectionString = "Host=localhost;Port=5432;Username=postgres;Password=123456;Database=yyfz;"; //AppSetting.app(new string[] { "AppSettings", "ConnectionStrings" });

        /// <summary>
        /// 查询并返回结果集DataTable（无参数）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable ExecuteQuery(string sql)
        {
            //string connStr = "Host=10.21.1.153;Port=5432;Username=postgres;Password=123456;Database=yyfz";
            NpgsqlConnection sqlConn = new NpgsqlConnection(ConnectionString);
            DataTable ds = new DataTable();
            try
            {
                using (NpgsqlDataAdapter sqldap = new NpgsqlDataAdapter(sql, sqlConn))
                {
                    sqldap.Fill(ds);
                }
                return ds;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

  

        public static List<T> TableToListModel<T>(DataTable dt) where T : new()
        {
            // 定义集合    
            List<T> ts = new List<T>();

            // 获得此模型的类型   
            Type type = typeof(T);
            string tempName = "";

            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                // 获得此模型的公共属性      
                PropertyInfo[] propertys = t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;  // 检查DataTable是否包含此列    

                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter      
                        if (!pi.CanWrite) continue;

                        object value = dr[tempName];
                        if (value != DBNull.Value)
                            pi.SetValue(t, value, null);
                    }
                }
                ts.Add(t);
            }
            return ts;
        }
    }
}

主代码

从数据库查询waypoint点集列表，每三个点进行一次处理，然后重新插入。

using System.Collections.Generic;
using ConsoleApp1;
using Microsoft.VisualBasic;
using Npgsql;

string conStr = "Host=localhost;Port=5432;Username=postgres;Password=123456;Database=yyfz";
NpgsqlConnection conn = new NpgsqlConnection(conStr);

string selectSql = "SELECT id, remarks, \"wayPointSet\", \"startingPoint\", \"endPoint\", \"creationTime\", \"updateTime\", type\r\n\tFROM public.\"aircraftTrailTable\";";

conn.Open();
List<AircraftTrailTable> aircraftTrailTable = CommRatePgSql.TableToListModel<AircraftTrailTable>(CommRatePgSql.ExecuteQuery(selectSql));

int affectedRows = 0;
for (int i = 0; i < aircraftTrailTable.Count; i++)
{
    Boolean flag = false;
    string trailName = aircraftTrailTable[i].remarks;

    string[]  wayPointSet = aircraftTrailTable[i].wayPointSet.ToArray();
    List<String> pointSetList = new List<String>(wayPointSet);

    int index = 0;
    while(index< pointSetList.Count-1)
    {
        if(index <=0)
        {
            index++;
          continue;
        }

        String[] a = pointSetList[index - 1].Split(',');
        String[] b = pointSetList[index].Split(',');
        String[] c = pointSetList[index + 1].Split(',');

        //String[] a = "2,0,0".Split(',');
        //String[] b = "0,0,0".Split(',');
        //String[] c = "2,2,0".Split(',');

        double Ax = Convert.ToDouble(b[0]) - Convert.ToDouble(a[0]);
        double Ay = Convert.ToDouble(b[1]) - Convert.ToDouble(a[1]);

        double Bx = Convert.ToDouble(c[0]) - Convert.ToDouble(b[0]);
        double By = Convert.ToDouble(c[1]) - Convert.ToDouble(b[1]);

        double cos = (Ax * Bx + Ay * By) / (Math.Sqrt(Ax * Ax + Ay * Ay) * Math.Sqrt(Bx * Bx + By * By));
        double angle = Math.Acos(cos);


        if (angle>=Math.PI/2)
        {
            //夹角移除
            pointSetList.RemoveAt(index);
            flag = true;
        }
        else
        {
            //高度修改
            if (Convert.ToDouble(b[2]) < Convert.ToDouble(a[2]) && Convert.ToDouble(b[2]) < Convert.ToDouble(c[2])|| Convert.ToDouble(b[2]) > Convert.ToDouble(a[2]) && Convert.ToDouble(b[2]) > Convert.ToDouble(c[2]))
            {
                b[2] = Convert.ToString((Convert.ToDouble(a[2])+ Convert.ToDouble(c[2]))/4);
                pointSetList[index] = b[0]+","+b[1]+","+b[2];
            }
            index++;
            flag = true;
        }
    }

    if (flag)
    {
        affectedRows++;
    }
    String insertWay = "{";
    pointSetList.ForEach(x => { insertWay += '"'+ x + '"'+','; });
    insertWay = insertWay.TrimEnd(',');
    insertWay = insertWay + "}";

    String updateSql = "UPDATE public.\"aircraftTrailTable\" SET \"wayPointSet\"= '" + insertWay + "'WHERE remarks ='"+ trailName +"';";
    CommRatePgSql.ExecuteQuery(updateSql);
}


Console.WriteLine(affectedRows);

python爬虫

从FlightAware网站上直接复制的轨迹数据是飞机运行的真实轨迹，因而会有折返和白点出现。可以用python抓取航班预定的轨迹线路，都是平滑的。

url拼接

1.拼接携带航班代码的url，获取历史起止机场。

url = f"https://flightaware.com/live/flight/{planecode}/history"

2.从url的标签页面中获取飞机起止机场，并拼接到url1中。

 url1 = "https://flightaware.com"
        page = requests.get(url)
        soup = BeautifulSoup(page.content, 'html.parser')
        tr_tags = soup.find_all('tr')
        for tag in tr_tags:
            if('data-target' in tag.attrs):
                count = tag.attrs.get("data-target").count('/')-1
                index = 0
                while index< len(tag.attrs.get("data-target")):
                    if(tag.attrs.get("data-target")[index]=='/'):
                      count = count-1
                      index = index+1
                      if(count ==0):
                          url1 = url +"/" +tag.attrs.get("data-target")[index:len(tag.attrs.get("data-target"))]
                          break
                    else:
                        index = index+1

3.从url1标签页面“trackpollGlobals”关键字中获取token，拼接得到含有完整预定航线的api_url。解析获得完整数据。

        page = requests.get(url1)
        soup = BeautifulSoup(page.content, 'html.parser')
        script_tags = soup.find_all('script')
        for tag in script_tags:
          if "trackpollGlobals" in tag.text:
            
            match = re.search('"TOKEN":"(.*?)"', tag.text)
        
            token = match.group(1)
        
            api_url = f"https://flightaware.com/ajax/trackpoll.rvt?token={token}&locale=en_US&summary=1"
        
            page = requests.get(api_url)
        
            data = json.loads(page.text)

获取航班号列表

FlightAware主网站上有大量航班号，抓取形成航班号列表；通过循环语句多次调用爬虫代码，实现批量抓取和存Excel。

航班号抓取代码

import requests
import json


url = "https://flightaware.com/ajax/vicinity_aircraft.rvt?&minLon=-122.86468863487244&minLat=37.6171875&maxLon=-114.70250129699707&maxLat=90&token=2613fc20c59cbfc105c8bd09abb8b79fb41bf15b"

page = requests.get(url)
data = json.loads(page.text)

res = list()

#for i in range(len(data.get("features"))):
for i in range(100):
    res.append(data.get("features")[i].get("properties").get("ident"))

print(res)

整体代码

# -*- coding: gbk -*-
from asyncio.windows_events import NULL
from re import findall
import requests
import json
import response
import re
from bs4 import BeautifulSoup
import xlwt


def trailpachong(traillist):
    flag = 0
    workBook = xlwt.Workbook(encoding='utf-8')
    for i in range(len(traillist)):
        planecode = traillist[i]
        url = f"https://flightaware.com/live/flight/{planecode}/history"
        
        url1 = "https://flightaware.com"
        page = requests.get(url)
        soup = BeautifulSoup(page.content, 'html.parser')
        tr_tags = soup.find_all('tr')
        for tag in tr_tags:
            if('data-target' in tag.attrs):
                count = tag.attrs.get("data-target").count('/')-1
                index = 0
                while index< len(tag.attrs.get("data-target")):
                    if(tag.attrs.get("data-target")[index]=='/'):
                      count = count-1
                      index = index+1
                      if(count ==0):
                          url1 = url +"/" +tag.attrs.get("data-target")[index:len(tag.attrs.get("data-target"))]
                          break
                    else:
                        index = index+1
                   
        
        page = requests.get(url1)
        soup = BeautifulSoup(page.content, 'html.parser')
        script_tags = soup.find_all('script')
        for tag in script_tags:
          if "trackpollGlobals" in tag.text:
            
            match = re.search('"TOKEN":"(.*?)"', tag.text)
        
            token = match.group(1)
        
            api_url = f"https://flightaware.com/ajax/trackpoll.rvt?token={token}&locale=en_US&summary=1"
        
            page = requests.get(api_url)
        
            data = json.loads(page.text)
            index1 = page.text.find("flights")
            index2 = page.text.find("activityLog")
            flightname = page.text[index1+11:index2-4]

        if(data.get("flights").get(flightname).get("origin").get("friendlyLocation")is None):
            origin = data.get("flights").get(flightname).get("origin").get("icao")
        else:
            origin = data.get("flights").get(flightname).get("origin").get("friendlyLocation")+"_"+data.get("flights").get(flightname).get("origin").get("icao")

        if(data.get("flights").get(flightname).get("destination").get("friendlyLocation")is None):
            destination = data.get("flights").get(flightname).get("destination").get("icao")
        else:
            destination = data.get("flights").get(flightname).get("destination").get("friendlyLocation")+"_"+data.get("flights").get(flightname).get("destination").get("icao")

        if(len(data.get("flights").get(flightname).get("waypoints"))<1):
            continue
        waypoints = data.get("flights").get(flightname).get("waypoints")
        flag = 1
        #插入Excel
        sheet = workBook.add_sheet(f"sheet{i+1}")
        head = [len(waypoints)+3,'纬度','经度','航向','节','公里/小时','米','爬升率','报告设施']
        head2 = [origin,'NULL','NULL','NULL','NULL','NULL',0,'NULL','NULL']
        tail = [destination,'NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL']
        tail2 = [origin+'--'+destination,'NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL']

        for i in head:
            sheet.write(0,head.index(i),i)

        for i in range(9):
            sheet.write(1,i,head2[i])

        colc = len(waypoints) -1
        
        for i in range(colc):
         sheet.write(i+2,0,1111)
         sheet.write(i+2,3,1111)
         sheet.write(i+2,4,1111)
         sheet.write(i+2,5,1111)
         sheet.write(i+2,7,1111)
         sheet.write(i+2,8,1111)

        for i in range(colc):
            jingdu = waypoints[i][0]
            weidu = waypoints[i][1]
            sheet.write(i+2,1,weidu)
            sheet.write(i+2,2,jingdu)

        for i in range(colc):
            if i == 0:
              sheet.write(i+2,6,0)
            elif i == 1:
              sheet.write(i+2,6,500)
            elif i == 2:
              sheet.write(i+2,6,1000)
            elif i == 3:
              sheet.write(i+2,6,5000)
            elif i == colc-4:
              sheet.write(i+2,6,5000)
            elif i == colc-3:
              sheet.write(i+2,6,1000)
            elif i == colc-2:
              sheet.write(i+2,6,500)
            elif i == colc-1:
              sheet.write(i+2,6,0)
            else:
              sheet.write(i+2,6,10000)

        for i in range(9):
            sheet.write(len(waypoints) +1,i,tail[i])

        for i in range(9):
            sheet.write(len(waypoints) +2,i,tail2[i])

    if(flag==0):
        print("数据为空，导入失败")
        return
    savePath = 'C:\\Users\\10412\\Desktop\\trail\\trailnew.xlsx'
    workBook.save(savePath)
    print("导入完成")

traillist = ['WJA1554', 'SWA734', 'EVA691', 'EJA237', 'ASA97', 'N80168', 'SKW3429']


trailpachong(traillist)