private static void QueryDestByOriginInternal(string year, string origin, string dest, string airline, string locale, string tableName, List <DestInfo> destList) { NpgsqlConnection conn = null; try { conn = new NpgsqlConnection(ASTDatabase.connStr2); conn.Open(); string where = ASTDatabase.MakeWhere(year, airline, origin, dest); string groupby = " \"GEOM\", " + (origin != "" ? "\"DEST\"" : "\"ORIGIN\""); string fields = (origin != "" ? "\"DEST\"" : "\"ORIGIN\""); fields += ", ST_AsText(\"GEOM\") AS \"GEOM\" "; string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE {2} GROUP BY {3}", fields, tableName, where, groupby); NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { DestInfo destInfo = new DestInfo(); destInfo.Airport = dr[origin != "" ? "DEST" : "ORIGIN"].ToString(); destInfo.TotalPax = null; destInfo.TotalFreight = null; destInfo.RouteGeometry = Utils.ProcessWktGeometryString(dr["GEOM"].ToString()); destInfo.PartialData = false; destInfo.NoData = true; destInfo.DataSource = "ConnectionData"; destList.Add(destInfo); } } catch (NpgsqlException e) { } finally { conn.Close(); } }
public static string QueryByRoute(string year, string origin, string dest, string locale) { NpgsqlConnection conn = null; string res = ""; Airport originAirport = AirportData.Query(origin); Airport destAirport = AirportData.Query(dest); if (originAirport == null || destAirport == null) { return(""); } double distKm = Utils.DistEarth(originAirport.Geometry.x, originAirport.Geometry.y, destAirport.Geometry.x, destAirport.Geometry.y); double distMile = Math.Round(distKm * 0.621371, 0); double distNm = Math.Round(distKm * 0.539957, 0); distKm = Math.Round(distKm, 0); try { conn = new NpgsqlConnection(ASTDatabase.connStr2); conn.Open(); string where = ASTDatabase.MakeWhere(year, "", origin, dest); string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("DEPARTURE"), Utils.DoubleQuoteStr("PAX"), Utils.DoubleQuoteStr("MONTH_DEPARTURE"), Utils.DoubleQuoteStr("MONTH_PAX") }; string fieldStr = String.Join(",", fields); string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE {2}", fieldStr, MetaData.SummaryTableName, where); NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { if (res != "") { res += ","; } Dictionary <string, string> item = new Dictionary <string, string>(); IDataRecord record = ( IDataRecord )dr; for (int i = 0; i < record.FieldCount; i++) { item.Add(record.GetName(i), record[i].ToString()); } Airline carrier = AirlineData.Query(item["AIRLINE"], locale); item.Add("AIRLINE_NAME", carrier.FullName); string json = new JavaScriptSerializer().Serialize(item); res += json; } } catch (NpgsqlException e) { } finally { conn.Close(); } res = "\"routes\":[" + res + "],"; res += "\"distKm\":" + distKm.ToString() + ","; res += "\"distMile\":" + distMile.ToString() + ","; res += "\"distNm\":" + distNm.ToString(); res = "{" + res + "}"; return(res); }
public static List <DestInfo> QueryDestByOrigin(string year, string origin, string dest, string airline, string dataSource, string locale) { HashSet <string> validSrc = new HashSet <string>(dataSource.Split(',')); List <DestInfo> res = new List <DestInfo>(); NpgsqlConnection conn = null; try { conn = new NpgsqlConnection(ASTDatabase.connStr2); conn.Open(); // Query T100 Data string where = ASTDatabase.MakeWhere(year, airline, origin, dest); string groupby = " \"GEOM\", " + (origin != "" ? "\"DEST\"" : "\"ORIGIN\""); string fields = origin != "" ? "\"DEST\"" : "\"ORIGIN\""; fields += ", ST_AsText(\"GEOM\") AS \"GEOM\", SUM(\"PAX\") AS \"SUM_PAX\", SUM(\"FREIGHT\") AS \"SUM_FREIGHT\" "; string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE {2} GROUP BY {3}", fields, MetaData.SummaryTableName, where, groupby); NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { DestInfo destInfo = new DestInfo(); destInfo.Airport = dr[origin != "" ? "DEST" : "ORIGIN"].ToString(); destInfo.TotalPax = Convert.ToInt32(dr["SUM_PAX"].ToString()); destInfo.TotalFreight = Convert.ToInt32(dr["SUM_FREIGHT"].ToString()); // Determine the dataSource of airport Airport airport1 = AirportData.Query(destInfo.Airport); Airport airport2 = AirportData.Query(origin != "" ? origin : dest); destInfo.RouteGeometry = Utils.ProcessWktGeometryString(dr["GEOM"].ToString()); if (airport1.Country != DataSourceRegister.GetDataSrc("T100Data").Country&& airport2.Country != DataSourceRegister.GetDataSrc("T100Data").Country) { destInfo.PartialData = true; destInfo.DataSource = "T100FF"; } else { destInfo.DataSource = "T100Data"; } if (validSrc.Contains(destInfo.DataSource) || dataSource == "") { res.Add(destInfo); } } } finally { conn.Close(); } return(res); }
public static List <DestInfo> QueryDestByOrigin(string year, string origin, string dest, string airline, string locale) { List <DestInfo> res = new List <DestInfo>(); NpgsqlConnection conn = null; try { conn = new NpgsqlConnection(ASTDatabase.connStr2); conn.Open(); string where = ASTDatabase.MakeWhere(year, airline, origin, dest); string fields = origin != "" ? "\"DEST\"" : "\"ORIGIN\""; fields += ", \"PAX\""; fields += ", ST_AsText(\"GEOM\") AS \"GEOM\""; string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE {2} ", fields, MetaData.SummaryTableName, where); NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { DestInfo destInfo = new DestInfo(); destInfo.Airport = dr[origin != "" ? "DEST" : "ORIGIN"].ToString(); destInfo.TotalPax = Convert.ToInt32(dr["PAX"].ToString()); destInfo.TotalFreight = null; destInfo.DataSource = "JapanData"; destInfo.RouteGeometry = Utils.ProcessWktGeometryString(dr["GEOM"].ToString()); destInfo.PartialData = false; // Now we don't support freight data if (destInfo.TotalPax > 0) { res.Add(destInfo); } } } finally { conn.Close(); } return(res); }
private static void QueryByRouteInternal(string tableName, string year, string origin, string dest, ref string jsonResult) { NpgsqlConnection conn = null; try { conn = new NpgsqlConnection(ASTDatabase.connStr2); conn.Open(); string where = " WHERE " + ASTDatabase.MakeWhere(year, "", origin, dest); string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("SEASONAL") }; string fieldStr = String.Join(",", fields); string sql = "SELECT " + fieldStr + " FROM \"" + tableName + "\"" + where; NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { if (jsonResult != "") { jsonResult += ","; } Dictionary <string, string> item = new Dictionary <string, string>(); IDataRecord record = ( IDataRecord )dr; for (int i = 0; i < record.FieldCount; i++) { item.Add(record.GetName(i), record[i].ToString()); } string json = new JavaScriptSerializer().Serialize(item); jsonResult += json; } } catch (NpgsqlException e) { } finally { conn.Close(); } }
static public string QueryRouteAircraftStat(string year, string origin, string dest, string locale) { NpgsqlConnection conn = null; JavaScriptSerializer jsoner = new JavaScriptSerializer(); try { int totalPax = 0; int totalFreight = 0; conn = new NpgsqlConnection(ASTDatabase.connStr2); conn.Open(); string where = ASTDatabase.MakeWhere(year, "", origin, dest); string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("AIRCRAFT_PAX"), Utils.DoubleQuoteStr("AIRCRAFT_FREIGHT"), Utils.DoubleQuoteStr("AIRCRAFT_DEPARTURE"), Utils.DoubleQuoteStr("PAX"), Utils.DoubleQuoteStr("FREIGHT"), Utils.DoubleQuoteStr("DEPARTURE") }; string fieldStr = String.Join(",", fields); string sql = "SELECT " + fieldStr + " FROM \"T100DataSummary\" WHERE " + where; NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataReader dr = command.ExecuteReader(); Dictionary <string, int> paxSummary = new Dictionary <string, int>(); Dictionary <string, int> freightSummary = new Dictionary <string, int>(); Dictionary <string, int> departureSummary = new Dictionary <string, int>(); Dictionary <string, object> dictRouteRes = new Dictionary <string, object>(); Dictionary <string, Aircraft> aircraftDict = new Dictionary <string, Aircraft>(); while (dr.Read()) { Dictionary <string, object> route = new Dictionary <string, object>(); route["airline"] = dr["AIRLINE"].ToString(); Airline carrier = AirlineData.Query(dr["AIRLINE"].ToString(), locale); route["airlineName"] = carrier.FullName; route["aircraftPax"] = ProcessAirlineItem(paxSummary, jsoner.Deserialize <Dictionary <string, int> >(dr["AIRCRAFT_PAX"].ToString())); route["aircraftFreight"] = ProcessAirlineItem(freightSummary, jsoner.Deserialize <Dictionary <string, int> >(dr["AIRCRAFT_FREIGHT"].ToString())); route["aircraftDeparture"] = ProcessAirlineItem(departureSummary, jsoner.Deserialize <Dictionary <string, int> >(dr["AIRCRAFT_DEPARTURE"].ToString())); totalPax += Convert.ToInt32(dr["PAX"]); totalFreight += Convert.ToInt32(dr["FREIGHT"]); Dictionary <string, int> localAircraft = jsoner.Deserialize <Dictionary <string, int> >(dr["AIRCRAFT_DEPARTURE"].ToString()); foreach (KeyValuePair <string, int> item in localAircraft) { string aircraft = item.Key; if (aircraft != "Other") { aircraftDict[aircraft] = AircraftData.Query(aircraft, locale); } } dictRouteRes[dr["AIRLINE"].ToString()] = route; } Dictionary <string, object> total = new Dictionary <string, object>(); total["airline"] = "All"; total["airlineName"] = ""; total["aircraftPax"] = ProcessSummaryDict(paxSummary); total["aircraftFreight"] = ProcessSummaryDict(freightSummary); total["aircraftDeparture"] = ProcessSummaryDict(departureSummary); dictRouteRes["All"] = total; Airport originAirport = AirportData.Query(origin, locale); Airport destAirport = AirportData.Query(dest, locale); Dictionary <string, object> dictRes = new Dictionary <string, object>() { { "routes", dictRouteRes }, { "aircrafts", aircraftDict }, { "origin", originAirport }, { "dest", destAirport }, { "totalPax", totalPax }, { "totalFreight", totalFreight } }; return(jsoner.Serialize(dictRes)); } finally { conn.Close(); } return(""); }
public static string QueryByAirlines(string year, string airline, string region, string locale, int limit = 100) { NpgsqlConnection conn = null; string currentCountry = DataSourceRegister.GetDataSrc("T100Data").Country; try { string res = ""; conn = new NpgsqlConnection(ASTDatabase.connStr2); conn.Open(); JavaScriptSerializer jsoner = new JavaScriptSerializer(); string where = ASTDatabase.MakeWhere(year, airline, "", ""); Airline carrier = AirlineData.Query(airline, locale); if (carrier == null) { return(""); } string flowField = carrier.Type == "Passenger" ? "PAX" : "FREIGHT"; // TODO: Fix table name string[] fields = new string[] { Utils.DoubleQuoteStr("ORIGIN"), Utils.DoubleQuoteStr("DEST"), Utils.DoubleQuoteStr(flowField), Utils.DoubleQuoteStr("DEPARTURE"), @"ST_AsText(""T100DataSummary"".""GEOM"") AS ""GEOM""", @"""AP1"".""COUNTRY"" as ""ORIGIN_COUNTRY""", @"""AP2"".""COUNTRY"" as ""DEST_COUNTRY""" }; string fieldStr = String.Join(",", fields); if (region == "US") { where += string.Format(@" AND ( ""AP1"".""COUNTRY"" = '{0}' AND ""AP2"".""COUNTRY"" = '{1}' ) ", currentCountry, currentCountry); } else if (region == "Intl") { where += string.Format(@" AND ( ""AP1"".""COUNTRY"" <> '{0}' OR ""AP2"".""COUNTRY"" <> '{1}' ) ", currentCountry, currentCountry); } where += " AND \"AP1\".\"CODE\" = \"T100DataSummary\".\"ORIGIN\" AND \"AP2\".\"CODE\" = \"T100DataSummary\".\"DEST\" "; string sql = string.Format(@"SELECT {0} FROM ""T100DataSummary"" , ""CommonData_Airport"" AS ""AP1"", ""CommonData_Airport"" AS ""AP2"" WHERE {1} ORDER BY ""{2}"" DESC LIMIT {3}", fieldStr, where, flowField, limit); NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { Dictionary <string, object> route = new Dictionary <string, object>(); Airport oAirport = AirportData.Query(dr["ORIGIN"].ToString(), locale); Airport dAirport = AirportData.Query(dr["DEST"].ToString(), locale); int flow = Convert.ToInt32(dr[flowField]); if (flow == 0) { continue; } route["origin"] = dr["ORIGIN"].ToString(); route["originCity"] = oAirport.ServeCity[0]; route["originCountry"] = oAirport.Country; route["originGeom"] = oAirport.Geometry; route["dest"] = dr["DEST"].ToString(); route["destCity"] = dAirport.ServeCity[0]; route["destCountry"] = dAirport.Country; route["destGeom"] = dAirport.Geometry; route["flow"] = Convert.ToInt32(dr[flowField]); route["departure"] = dr["DEPARTURE"].ToString(); string geom = dr["GEOM"].ToString(); geom = geom.Replace("MULTILINESTRING", ""); geom = geom.Replace("(", "["); geom = geom.Replace(")", "]"); route["geom"] = geom; if (res != "") { res += ","; } res += jsoner.Serialize(route); } return("[" + res + "]"); } finally { conn.Close(); } return(""); }
/// <summary> /// Query the airport statistics: top destinations and airline share /// </summary> /// <param name="dataSrc">The data source for the query: T100, TwData, KrData, etc.</param> public static string QueryAirportStat(string dataSrc, string year, string airport, string locale) { // Get the meta data of specific data source ADataSourceMetaData metaData = null; if (!DataSourceRegister.Register.TryGetValue(dataSrc, out metaData)) { return(""); } NpgsqlConnection conn = null; string res = ""; try { conn = new NpgsqlConnection(ASTDatabase.connStr2); conn.Open(); string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("DEST"), Utils.DoubleQuoteStr("PAX"), Utils.DoubleQuoteStr("FREIGHT") }; string fieldStr = String.Join(",", fields); string sql = String.Format(@"SELECT {0} FROM ""{1}"" WHERE {2}", fieldStr, metaData.SummaryTableName, ASTDatabase.MakeWhere(year, "", airport, "")); NpgsqlCommand command = new NpgsqlCommand(sql, conn); NpgsqlDataReader dr = command.ExecuteReader(); List <RouteStat> routes = new List <RouteStat>(); while (dr.Read()) { RouteStat route = new RouteStat(); route.Airline = dr["AIRLINE"].ToString(); route.Dest = dr["DEST"].ToString(); route.Pax = metaData.HasPaxData ? Convert.ToInt32(dr["PAX"]) : 0; route.Freight = metaData.HasFreightData ? Convert.ToInt32(dr["FREIGHT"]) : 0; routes.Add(route); } string[] regions = new string[] { "All", "International", "Domestic" }; foreach (string region in regions) { if (res != "") { res += ","; } res += Utils.DoubleQuoteStr(region) + ":" + CreateRankByRegion(routes, region, metaData, locale); } return("{" + res + "}"); } finally { conn.Close(); } return(""); }