aviation-pg

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