-------------------------------------------------------
-- find airlines.
-------------------------------------------------------

SELECT
    airlines.name
FROM
    airlines
WHERE
    upper(airlines.airline_id) LIKE upper('%iber%')


-------------------------------------------------------
-- airport runways.
-------------------------------------------------------

SELECT DISTINCT airports.name, airport_runways.direction, 
    airport_runways.ft, airport_runways.m, airport_runways.surface 
FROM
    airports
JOIN
    airport_runways
ON
    airport_runways.airport_id = airports.airport_id
WHERE
    airports.name LIKE '%a'
ORDER BY
    airports.name


-------------------------------------------------------
-- latitude and longitude
-------------------------------------------------------

SELECT
    airports.name, airports.latitude, airports.longitude
FROM
    airports
WHERE
    upper(airports.airport_id) LIKE upper('%vale%')


-------------------------------------------------------
-- airline destinations
-------------------------------------------------------

SELECT 
    airline_destinations.airport_id, airlines.name
FROM
    airline_destinations
 JOIN 
    airlines
   ON
    airline_destinations.airline_id = airlines.airline_id
-- here the problem comes with the id, 
-- not all the airlines have been saved 
-- and not all the destinations have been saved with the same id we have for the airlines.
WHERE
    upper(airlines.name)
LIKE 
    upper('%iber%')


-------------------------------------------------------
-- cities by airline
-------------------------------------------------------

SELECT 
    airline_destinations.airport_id, airlines.name, airport_cities.city_id
FROM
    airline_destinations
 JOIN 
    airlines
   ON
    airline_destinations.airline_id = airlines.airline_id
JOIN
    airports
ON
    airports.airport_id = airline_destinations.airport_id
JOIN
    airport_cities
ON
    airports.airport_id = airport_cities.airport_id
WHERE
    upper(airlines.name)
LIKE 
    upper('%vuel%')


-------------------------------------------------------
-- airline iata
-------------------------------------------------------

SELECT
    airlines.iata
FROM
    airlines
WHERE
    UPPER (airlines.airline_id)
LIKE
    UPPER ('%vuel%')


------------------------------------------------------- 
-- airline logo
-------------------------------------------------------

SELECT
    airlines.logo_url
FROM
    airlines
WHERE
    UPPER (airlines.airline_id)
LIKE
    UPPER ('%vuel%')


------------------------------------------------------- 
-- decimal coordinates
-------------------------------------------------------

SELECT
    airports.name,
    round(dms2dd(airports.latitude),9) AS latitude,
    round(dms2dd(airports.longitude),9) AS longitude    
FROM
    airports
WHERE
    upper(airports.airport_id) LIKE upper('%valenc%')

------------------------------------------------------- 
-- GEO: convert coordinates to decimal.
-------------------------------------------------------

-- Sample:
SELECT round(dms2dd('43° 0''50.60"S'),9) AS latitude,
       round(dms2dd('147°12''18.20"E'),9) AS longitude;

--- with our actual coordinates.

SELECT round(dms2dd('53°35′05″N'),9) AS latitude,
       round(dms2dd('014°54′08″E'),9) AS longitude;


------------------------------------------------------- 
-- GEO: earth_distance.
-------------------------------------------------------

SELECT earth_distance(ll_to_earth(0, 0), ll_to_earth(0, 1));

------------------------------------------------------- 
-- install function dms2dd from file 
-- spike/sql_queries/functions.sql
-------------------------------------------------------


-- psql aviation -f ./spike/sql_queries/functions.sql


------------------------------------------------------- 
-- drop function dms2dd
-------------------------------------------------------

DROP FUNCTION DMS2DD(strDegMinSec varchar);


------------------------------------------------------- 
-- earth dist with dms2dd
-------------------------------------------------------

SELECT
    airports.name, 
    earth_distance(
        ll_to_earth(0, 0), 
        ll_to_earth(
            round(dms2dd(airports.latitude),9),
            round(dms2dd(airports.longitude),9)
        )
    ) AS distance
FROM
    airports
WHERE
    UPPER (airports.name)
LIKE
    UPPER ('%valencia%')


------------------------------------------------------- 
-- earth_box radius from fixed position.
-------------------------------------------------------

SELECT 
    airports.airport_id,
    airports.name,
    airports.latitude,
    airports.longitude
FROM
    airports
WHERE
    UPPER (airports.name)
LIKE
-- one of the airports that can be filter with vale is causing an error.
    UPPER ('%valencia%')
AND
    earth_box(ll_to_earth( 40, -1 ), 1000000) @> 
     ll_to_earth(
       round(dms2dd(airports.latitude),9),
       round(dms2dd(airports.longitude),9)
     )


------------------------------------------------------- 
-- decimal coordinates for normalized airports.
-------------------------------------------------------

SELECT
    airports.airport_id,
    airports.name, 
    round(dms2dd(airports.latitude),9) AS latitude,
    round(dms2dd(airports.longitude),9) AS longitude

FROM 
    airports
WHERE
    airports.latitude LIKE '%°%′%″%'
ORDER BY
    airports.name


------------------------------------------------------- 
-- not normalized airports.
-------------------------------------------------------

SELECT
    airports.airport_id,
    airports.name,
    airports.latitude,
    airports.longitude
FROM 
    airports
WHERE
    airports.latitude NOT LIKE '%°%′%″%'
AND
    airports.latitude <> ''
ORDER BY
    airports.name


------------------------------------------------------- 
-- decimal coordinates restricted by length.
-------------------------------------------------------

SELECT
    airports.name, 
    round(dms2dd(airports.latitude),9) AS latitude,
    round(dms2dd(airports.longitude),9) AS longitude,
    CHAR_LENGTH (airports.longitude) AS long_length
FROM
    airports
WHERE 
    UPPER (airports.name) 
LIKE 
    UPPER ('v%airport%')
AND
    CHAR_LENGTH (airports.longitude) > 9
AND
    CHAR_LENGTH (airports.latitude) > 7


------------------------------------------------------- 
-- debugging with EXCEPT
-------------------------------------------------------

SELECT
    airports.name,
    airports.latitude,
    airports.longitude
FROM
    airports
WHERE 
    UPPER (airports.name) 
LIKE 
    UPPER ('v%Airport%')
EXCEPT    
SELECT
    airports.name,
    airports.latitude,
    airports.longitude

FROM
    airports
WHERE 
    UPPER (airports.name) 
LIKE 
    UPPER ('v%Airport%')
AND
    CHAR_LENGTH (airports.longitude) > 9
AND
    CHAR_LENGTH (airports.latitude) > 7


------------------------------------------------------- 
-- not normalized decimal coordinates filtered with except
-------------------------------------------------------

SELECT
    airports.name,
    airports.latitude,
    airports.longitude
FROM
    airports
EXCEPT    
SELECT
    airports.name,
    airports.latitude,
    airports.longitude

FROM 
    airports
WHERE
    airports.latitude LIKE '%°%′%″%'

------------------------------------------------------- 
-- conditional discard empty and null.
-------------------------------------------------------


SELECT 
    airports.name,
    (CASE WHEN 
        airports.latitude <> '' 
    THEN
        airports.latitude
    END) AS latitude
FROM
    airports
WHERE
    airports.name <> ''
AND
    latitude IS NOT NULL -- this restriction wasn't working
AND
    latitude <> NULL -- this restriction works
ORDER BY 
    latitude desc


------------------------------------------------------- 
-- earth dist from airport to airport
-------------------------------------------------------

SELECT
    airports.name, 
    earth_distance(
        ll_to_earth(
                (SELECT
                    round(dms2dd(airports.latitude),9) AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
                ,
                 (SELECT
                    round(dms2dd(airports.longitude),9) AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
            ), 
        ll_to_earth(
            round(dms2dd(airports.latitude),9),
            round(dms2dd(airports.longitude),9)
        )
    ) AS distance
FROM
    airports
WHERE
    airports.latitude LIKE '%°%′%″%'
AND
    airports.airport_id LIKE '%' -- name of desired airport.


------------------------------------------------------- 
-- earth_box radius from airport with distance.
-------------------------------------------------------

SELECT 
    airports.airport_id,
    airports.name,
    airports.latitude,
    airports.longitude,
    earth_distance(
        ll_to_earth(
                (SELECT
                    round(dms2dd(airports.latitude),9) AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
                ,
                 (SELECT
                    round(dms2dd(airports.longitude),9) AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
            ), 
        ll_to_earth(
            round(dms2dd(airports.latitude),9),
            round(dms2dd(airports.longitude),9)
        )
    ) AS distance
FROM
    airports
WHERE
    airports.latitude LIKE '%°%′%″%'
AND
    earth_box(
        ll_to_earth(
            (SELECT
                round(dms2dd(airports.latitude),9) AS latitude
            FROM
                airports
            WHERE
                UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
            ,
            (SELECT
                round(dms2dd(airports.longitude),9) AS latitude
            FROM
                airports
            WHERE
                UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
            )
        , 100 * 1000 -- this is the radius in meters
    ) @> 
     ll_to_earth(
       round(dms2dd(airports.latitude),9),
       round(dms2dd(airports.longitude),9)
     )
ORDER BY
    airports.airport_id


------------------------------------------------------- 
-- earth_box radius from airport with distance.
-------------------------------------------------------

SELECT 
    airports.airport_id,
    airports.name,
    airports.latitude,
    airports.longitude,
    round (CAST (float8 (earth_distance(
        ll_to_earth(
                (SELECT
                    round(dms2dd(airports.latitude),9) AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
                ,
                 (SELECT
                    round(dms2dd(airports.longitude),9) AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
            ), 
        ll_to_earth(
            round(dms2dd(airports.latitude),9),
            round(dms2dd(airports.longitude),9)
        )
    )) AS NUMERIC ), 2)AS distance
FROM
    airports
WHERE
    airports.latitude LIKE '%°%′%″%'
AND
    earth_box(
        ll_to_earth(
            (SELECT
                round(dms2dd(airports.latitude),9) AS latitude
            FROM
                airports
            WHERE
                UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
            ,
            (SELECT
                round(dms2dd(airports.longitude),9) AS latitude
            FROM
                airports
            WHERE
                UPPER (airports.airport_id) LIKE upper('valencia%')) -- name of origin
            )
        , 100 * 1000 -- this is the radius in meters
    ) @> 
     ll_to_earth(
       round(dms2dd(airports.latitude),9),
       round(dms2dd(airports.longitude),9)
     )
ORDER BY
    distance asc


------------------------------------------------------- 
-- earth_box radius from origin var with distance.
-------------------------------------------------------
--
-- edit parameters at the following line.
--

WITH origin AS (
    SELECT
        'valencia%'::text AS airport, 
        200 * 1000::INT AS distance -- this is the radius in meters
    )

--
 
SELECT 
    airports.airport_id,
    airports.name,
    airports.latitude,
    airports.longitude,
    round (CAST (float8 (earth_distance( -- CAST is used to round decimals. 
        ll_to_earth(
                (SELECT
                    round(dms2dd(airports.latitude),9) AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper((SELECT airport FROM origin))) -- name of origin
                ,
                 (SELECT
                    round(dms2dd(airports.longitude),9) AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper((SELECT airport FROM origin))) -- name of origin
            ), 
        ll_to_earth(
            round(dms2dd(airports.latitude),9),
            round(dms2dd(airports.longitude),9)
        )
    )) AS NUMERIC ), 2)AS distance -- this number means the decimals of the distance.
FROM
    airports
WHERE
    airports.latitude LIKE '%°%′%″%'
AND
    earth_box(
        ll_to_earth(
            (SELECT
                round(dms2dd(airports.latitude),9) AS latitude
            FROM
                airports
            WHERE
                UPPER (airports.airport_id) LIKE upper((SELECT airport FROM origin))) -- name of origin
            ,
            (SELECT
                round(dms2dd(airports.longitude),9) AS latitude
            FROM
                airports
            WHERE
                UPPER (airports.airport_id) LIKE upper((SELECT airport FROM origin))) -- name of origin
            )
        , (SELECT distance FROM origin) 
    ) @> 
     ll_to_earth(
       round(dms2dd(airports.latitude),9),
       round(dms2dd(airports.longitude),9)
     )
ORDER BY
    distance asc





------------------------------------------------------- 
-- earth_box radius using decimal coordinates.
-------------------------------------------------------
--
-- edit parameters at the following line.
--


WITH origin AS (
    SELECT
        'valencia%'::text AS airport, 
        200 * 1000::INT AS distance -- this is the radius in meters
    )

--
 
SELECT 
    airports.airport_id,
    airports.name,
    airports.latitude,
    airports.longitude,
    round (CAST (float8 (earth_distance( -- CAST is used to round decimals. 
        ll_to_earth(
                (SELECT
                    airports.dd_latitude AS latitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper((SELECT airport FROM origin))) -- name of origin
                ,
                 (SELECT
                    airports.dd_longitude AS longitude
                 FROM
                    airports
                WHERE
                    UPPER (airports.airport_id) LIKE upper((SELECT airport FROM origin))) -- name of origin
            ), 
        ll_to_earth(
            airports.dd_latitude,
            airports.dd_longitude
        )
    )) AS NUMERIC ), 2)AS distance -- this number means the decimals of the distance.
FROM
    airports
WHERE

    earth_box(
        ll_to_earth(
            (SELECT
                airports.dd_latitude AS latitude
            FROM
                airports
            WHERE
                UPPER (airports.airport_id) LIKE upper((SELECT airport FROM origin))) -- name of origin
            ,
            (SELECT
                airports.dd_longitude AS longitude
            FROM
                airports
            WHERE
                UPPER (airports.airport_id) LIKE upper((SELECT airport FROM origin))) -- name of origin
            )
        , (SELECT distance FROM origin) 
    ) @> 
     ll_to_earth(
       airports.dd_latitude,
       airports.dd_longitude
     )
ORDER BY
    distance asc