{"cells":[{"cell_type":"markdown","source":["# Instalación y datos"],"metadata":{"id":"qggw3E9rygd2"}},{"cell_type":"markdown","source":["## Instalación de postgres y carga de datos"],"metadata":{"id":"Muxt3i7yyjnn"}},{"cell_type":"code","execution_count":null,"metadata":{"id":"xN9MrCqDyMym"},"outputs":[],"source":["# install\n","!apt update\n","!apt install postgresql postgresql-contrib &>log\n","!service postgresql start\n","!sudo -u postgres psql -c \"CREATE USER root WITH SUPERUSER\"\n","# pin package versions for google colab compatibility\n","!pip install SQLAlchemy==1.4.46\n","!pip install ipython-sql==0.4.1\n","# set connection\n","%load_ext sql\n","%config SqlMagic.feedback=False \n","%config SqlMagic.autopandas=True\n","%sql postgresql+psycopg2://@/postgres\n","%sql create schema control2;\n","%sql set search_path to control2;\n","\n","from google.colab import output\n","output.clear()"]},{"cell_type":"markdown","source":["### Airports"],"metadata":{"id":"TVrwXHErPm5D"}},{"cell_type":"code","source":["%%sql\n","\n","CREATE TABLE airports(\n","    id bigint,\n","    name varchar(128),\n","    city varchar(128),\n","    country varchar(64),\n","    iata_code varchar(3),\n","    icao_code varchar(4),\n","    latitude decimal(9, 6),\n","    longitude decimal(9, 6),\n","    altitude float,\n","    timezone decimal(4, 1),\n","    dst varchar(1),\n","    tz varchar(64),\n","    airport_type varchar(8),\n","    source varchar(16)\n",");"],"metadata":{"id":"wr6vVQavPo0L"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["!wget https://raw.githubusercontent.com/jpatokal/openflights/master/data/airports.dat"],"metadata":{"id":"ejFG-2g1RHQh"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["%%sql\n","\n","COPY airports FROM '/content/airports.dat' WITH (FORMAT CSV, DELIMITER(','), NULL '\\N' )"],"metadata":{"id":"Is_cbXSSXQU7"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["### Airlines"],"metadata":{"id":"38Gxmwh6YXvG"}},{"cell_type":"code","source":["%%sql\n","\n","CREATE TABLE airlines(\n","    id bigint,\n","    name varchar(128),\n","    alias varchar(64),\n","    iata_code varchar(4),\n","    icao_code varchar(8),\n","    callsign varchar(128),\n","    country varchar(64),\n","    active varchar(1)\n",");"],"metadata":{"id":"6aU38EA7YZ9u"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["!wget https://raw.githubusercontent.com/jpatokal/openflights/master/data/airlines.dat"],"metadata":{"id":"kGQYa15CaRV1"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["%%sql\n","\n","COPY airlines FROM '/content/airlines.dat' WITH (FORMAT CSV, DELIMITER(','), NULL '\\N' )"],"metadata":{"id":"xyQKSWiBaXwv"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["### Routes"],"metadata":{"id":"O-tpPXSsKkbA"}},{"cell_type":"code","source":["%%sql\n","\n","CREATE TABLE routes(\n","    airline_code varchar(3),\n","    airline_id bigint,\n","    source_airport_code varchar(4),\n","    source_airport_id bigint,\n","    destination_airport_code varchar(4),\n","    destination_airport_id bigint,\n","    vcodeshare varchar(1),\n","    codeshare boolean,\n","    stops int,\n","    equipment varchar(128)\n",");"],"metadata":{"id":"VyBiSVoNFDag"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["!wget https://raw.githubusercontent.com/jpatokal/openflights/master/data/routes.dat"],"metadata":{"id":"XLT4lcKPF9y3"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["%%sql\n","\n","COPY routes(\n","    airline_code,\n","    airline_id,\n","    source_airport_code,\n","    source_airport_id,\n","    destination_airport_code,\n","    destination_airport_id,\n","    vcodeshare,\n","    stops,\n","    equipment\n",") FROM '/content/routes.dat' WITH (FORMAT CSV, DELIMITER(','), NULL '\\N' );\n","\n","UPDATE routes\n","SET codeshare = FALSE\n","WHERE vcodeshare = '';\n","\n","UPDATE routes\n","SET codeshare = TRUE\n","WHERE vcodeshare = 'Y';\n","\n","ALTER TABLE routes DROP COLUMN vcodeshare;"],"metadata":{"id":"rJ0_AJsGHeqn"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["# Esquema"],"metadata":{"id":"smqiNf_vyx69"}},{"cell_type":"markdown","source":["## Esquema\n","\n","La base de datos tiene 3 tablas: `airlines`, `airports` y `routes`. Puedes revisar el esquema en detalle [en esta página](https://openflights.org/data.html) de dónde sacamos los datos.\n","\n","* `airlines(id, name, alias, iata_code, icao_code, callsign, country, active)`\n","* `airports(id, name, city, country, iata_code, icao_code, latitude, longitude, altitude, timezone, dst, tz, airport_type, source)`\n","* `routes(airline_code, airline_id, source_airport_code, source_airport_id, destination_airport_code, destination_airport_id, codeshare, stops, equipment)`"],"metadata":{"id":"32zhhNPCy2w3"}},{"cell_type":"markdown","source":["# Preguntas\n","\n","Haga consultas en SQL para obtener la siguiente información:"],"metadata":{"id":"x8YY4UVnJ04V"}},{"cell_type":"markdown","source":["### 1. Indique los pares de aeropuertos con más vuelos de uno al otro\n"],"metadata":{"id":"Y9aV0_iZMFMD"}},{"cell_type":"code","source":[],"metadata":{"id":"nFK4eV9mMMUT"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["### 2. Enumere todos las ciudades que conectan Santiago con Frankfurt\n"],"metadata":{"id":"aWq9oOWtueHk"}},{"cell_type":"code","source":[],"metadata":{"id":"IlZJLIMkvCuA"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["### 3. ¿Cuál es son los nombres de las aerolineas más grande? (La que tiene la mayor cantidad de rutas). Liste las 10 más grandes."],"metadata":{"id":"4f0ejcaTliuj"}},{"cell_type":"code","source":[],"metadata":{"id":"XGDXHlJOl5yX"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["### 4. ¿Cuáles son los 5 aeropuertos más importantes? (Aeropuertos de los cuáles llegan y salen más rutas)"],"metadata":{"id":"D3vDPJaMdCCp"}},{"cell_type":"code","source":[],"metadata":{"id":"_T1kFWBUdIpl"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["### 5. Entregue todas las rutas (sin repetición) que van desde New York hacia Berlin y pasan por exactamente tres aeropuertos. Para esto entregue tres columnas con los códigos de cada uno de los aeropuertos involucrados."],"metadata":{"id":"5QaDTXZy3V4w"}},{"cell_type":"code","source":[],"metadata":{"id":"U3jeSGC32oIB"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["### 6. Entregue los 10 aeropuertos más cercanos al aeropuerto 'Pdte. Carlos Ibañez del Campo Airport' (sin incluirlo), ordenados en distancia de forma ascendente."],"metadata":{"id":"UWNZNBAKAWcd"}},{"cell_type":"markdown","source":["Para calcular la distancia, puede utilizar la siguiente fórmula:\n","\n","$$\n","distancia(p1, p2) = \\sqrt{(p2.lat - p1.lat)^{2} + (p2.long - p1.long)^{2}}\n","$$\n","\n","Es importante mencionar que esta fórmula no es precisa, pues no considera la curvatura de la tierra 😉"],"metadata":{"id":"I4LcwoLvlBDR"}},{"cell_type":"code","source":[],"metadata":{"id":"UKdDBPnhAXc8"},"execution_count":null,"outputs":[]}],"metadata":{"colab":{"provenance":[],"collapsed_sections":["Muxt3i7yyjnn","Y9aV0_iZMFMD","aWq9oOWtueHk","4f0ejcaTliuj","D3vDPJaMdCCp","5QaDTXZy3V4w","UWNZNBAKAWcd"]},"kernelspec":{"display_name":"Python 3 (ipykernel)","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.10.4"}},"nbformat":4,"nbformat_minor":0}