全国航班数据与可视化分析

https://flight.qunar.com/touch/api/domestic/wbdflightlist?departureCity=%E5%8C%97%E4%BA%AC&arrivalCity=%E6%B7%B1%E5%9C%B3&departureDate=2018-06-12&ex_track=&__m__=09de50bb5812f2686e2db9b0f49185d0&sort=

CREATE TABLE domestic_flight
(
  departure_city character varying(30),
  departure_cy double precision,
  departure_cx double precision,
  landing_city character varying(30),
  landing_cy double precision,
  landing_cx double precision,
  mileage character varying(255),
  flight_schedules character varying(255),
  airlines character varying(255),
  aircraft_models character varying(255),
  departure_time character varying(255),
  landing_time character varying(255),
  departure_airport character varying(255),
  departure_y double precision,
  departure_x double precision,
  landing_airport character varying(255),
  landing_y double precision,
  landing_x double precision,
  punctuality_rate character varying(255),
  average_delayed character varying(255),
  is_mon smallint,
  is_tue smallint,
  is_wed smallint,
  is_thr smallint,
  is_fri smallint,
  is_sat smallint,
  is_sun smallint
)

CREATE TABLE flight_airport
(
   id serial, 
   name character varying(30), 
   province character varying(30), 
   airport_x numeric, 
   airport_y numeric, 
   CONSTRAINT pkey_air_airporty PRIMARY KEY (id)
) 

INSERT INTO flight_airport (NAME, airport_x, airport_y)(
    SELECT DISTINCT
        departure_airport AS airport,
        departure_x AS airport_x,
        departure_y AS airport_y
    FROM
        domestic_flight
)
UNION
    (
        SELECT DISTINCT
            landing_airport AS airport,
            to_number(landing_x, '999.999999999') AS airport_x,
            to_number(landing_y, '999.999999999') AS airport_y
        FROM
            domestic_flight
    )

UPDATE flight_airport
SET province = (
    SELECT
        NAME
    FROM
        province
    WHERE
        st_within (
            st_point (
                flight_airport.airport_x,
                flight_airport.airport_y
            ),
            province.geom
        )
)

SELECT
    province,
    COUNT (1) AS airport_num
FROM
    flight_airport
GROUP BY
    province
ORDER BY
    airport_num DESC

SELECT
    airlines as name,
    COUNT (1) AS num
FROM
    domestic_flight
GROUP BY
    airlines
ORDER BY
    num DESC

SELECT
    aircraft_models as name,
    COUNT (1) AS num
FROM
    domestic_flight
GROUP BY
    aircraft_models
ORDER BY
    num DESC