aviation-pg.

Description

This project uses the aviation data provided by the aviation-json project.

It will create a database (using versioned migrations) and keep it up to date with the JSON files provided by aviation-json by running a single command make.

A PostgreSQL database named 'aviation' will be created and it will be rebuilt with the latest aviation information.

Keep in mind that the migrations will drop the tables and will re-generate them on every sync. This strategy is only temporally until we complete the aviation frontend app that this data backs up. Once we release v.1 of this module the migrations will not destroy existing tables. They will migrate :)

Instructions

Before running a command you must have PostgreSQL installed download it. If you are a mac user the Postgress.app will simplify it.

To start run the command make

Configuration:

The configuration is at the database.json file

{
 "default": "pg",
 "pg": {
    "driver": "pg",
    "user": "aviator",
    "host": "localhost",
    "database": "aviation"
 }
}

The Makefile gets the username and database from this location.

Initially the user created for the db has no password, if you wish to set a password, you need to do it manually, you need to establish the password at the database.json file and once you've created the db and the user, set the password with the following command:

psql -U aviator -d aviation -c '\\password'

Add the password to your config file:

{
    "password": "your_password"
}

if you set the password on the config file before creating the user and the db, the Make workflow will fail.

Main Make commands:

• make will perform the following operations:

  • npm install
  • check for db, if doesn't exist, it will create it as well as the superuser
    • NOTE: creating a sql superuser will request your admin password
  • apply a patch to comment a warning on db-migrate.
    • [WARN] The function "insert" is deprecated.
  • perform the first up migration.

• make migration-up:

  • Performs the up migrations.

• make migration-down:

  • Performs the down migrations.

• make db-clean:

  • drops the database.
  • drops the user.

Database structure.

Inside the aviation db creates the following tables:

• airports

airport_id | latitude | longitude | name | nickname | iata | icao -----------|----------|-----------|------|----------|------|------ Abu_Musa_Airport | 25°52′32″N | 055°01′58″E | Abu Musa Airport | Abumusa Airport | AEU | OIBA

• airlines

airline_id | logoLink | iata | icao | callsign | website -----------|----------|------|------|----------|-------- Vueling | //upload....230px-Logo_Vueling.svg.png | VY | VLG | VUELING | http://www.vueling.com

• airline_hubs

airline_id | airport_id ---|--- Vueling | Barcelona%E2%80%93El_Prat_Airport Vueling | Leonardo_da_Vinci%E2%80%93Fiumicino_Airport

• airline_destinations

airline_id | airport_id ---|--- Adria_Airways | Amsterdam_Airport_Schiphol Adria_Airways | Berlin_Tegel_Airport

• airport_airlines

airport_id | airline_id ---|--- Amsterdam_Airport_Schiphol | Adria Airways Amsterdam_Airport_Schiphol | Aegean Airlines

• cities

  todo: add cities.

| city_id | |---| | Amsterdam (wiki path) |

• airport_cities (1:1)

airport_id | city_id ---|---|--- Amsterdam_Airport_Schiphol | (/wiki/)Amsterdam Berlin_Tegel_Airport | (/wiki/)Berlin

• airport_runways

airport_id | direction | ft | m | surface ---|---|---|---|--- Apalachicola_Regional_Airport | 6/24 | 5,271 | 1,607 | Concrete Apalachicola_Regional_Airport | 13/31 | 5,251 | 1,601 | Concrete Apalachicola_Regional_Airport | 18/36 | 5,251 | 1,601 | Concrete

• city_airports (1:m)

city_id | airport_id ---|--- Amsterdam | Amsterdam_Airport_Schiphol Berlin | Berlin_Tegel_Airport