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

数据获取

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; }

///

/// 创建时间

///

public DateTimeOffset creationTime { get; set; }

///

/// 更新时间

///

public DateTimeOffset updateTime { get; set; }

///

/// 备注

///

public string remarks { get; set; }

///

/// 途径点集合

///

public List wayPointSet = new List();

///

/// 起点

///

public string startingPoint { get; set; }

///

/// 终点

///

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" });

///

/// 查询并返回结果集DataTable（无参数）

///

///

///

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 TableToListModel(DataTable dt) where T : new()

{

// 定义集合

List ts = new List();

// 获得此模型的类型

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 = CommRatePgSql.TableToListModel(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 pointSetList = new List(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)

数据部署

导出数据表脚本，进行部署和转存。