flight-data-hsqldb

FAA flight data set in hsqldb format

This project contains the FAA flight data set as an HSQLDB database.

It contains the following tables, each backed by a data set in a .csv file.

table	row count
accidents	70,777
aircraft	359,928
aircraft_engine_types	8
aircraft_engines	761
aircraft_models	60,461
aircraft_types	6
airport_remarks	69,708
airports	19,795
carriers	21
exceptions	702
ontime	38,483,559
states	58
zipcodes	29,467

The ontime table is split into 51 files, each with approximately 400,000 rows, and because of time/memory considerations only the first is loaded. Therefore ontime has only 399,999 rows when queried via SQL.

To connect and read data

Add the following to your Maven pom.xml:

<dependencies>
  <dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.3.1</version>
  </dependency>
  <dependency>
    <groupId>net.hydromatic</groupId>
    <artifactId>flight-data-hsqldb</artifactId>
    <version>0.2</version>
  </dependency>
</dependencies>

Connect to the database via the URL, user name and password in the FlightHsqldb class:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import net.hydromatic.flight.data.hsqldb;

Connection connection =
  DriverManager.getConnection(FlightHsqldb.URI, FlightHsqldb.USER, FlightHsqldb.PASSWORD);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from \"airports\"");

Using SQLLine

Connect from the command line using the SQLLine shell:

$ ./sqlline
sqlline version 1.12.0
sqlline> !connect jdbc:hsqldb:res:flight FLIGHT TIGER
0: jdbc:hsqldb:res:flight> select * from "aircraft_types";
+------------------+-----------------------------+
| aircraft_type_id | description                 |
+------------------+-----------------------------+
| 1                | Glider                      |
| 2                | Balloon                     |
| 3                | Blimp/Dirigible             |
| 4                | Fixed wing, single engine   |
| 5                | Fixed wing, multiple engine |
| 6                | Rotorcraft                  |
+------------------+-----------------------------+
6 rows selected (0.001 seconds)

0: jdbc:hsqldb:res:flight> select count(*) from "airports";
+--------+
| C1     |
+--------+
| 19795  |
+--------+
1 row selected (0.002 seconds)

You may need to edit the sqlline or sqlline.bat launcher script, adding flight-data-hsqldb.jar to your class path.

Get flight-data-hsqldb

From Maven

Get flight-data-hsqldb from Maven Central:

<dependency>
  <groupId>net.hydromatic</groupId>
  <artifactId>flight-data-hsqldb</artifactId>
  <version>0.2</version>
</dependency>

Download and build

Java version 8 or higher.

Check out and build:

$ git clone git://github.com/julianhyde/flight-data-hsqldb.git
$ cd flight-data-hsqldb
$ ./mvnw install

On Windows, the last line is

> mvnw install

See also

Similar data sets:

• chinook-data-hsqldb
• foodmart-data-hsqldb
• scott-data-hsqldb
• steelwheels-data-hsqldb

More information

• License: Apache License, Version 2.0
• Author: Julian Hyde (@julianhyde)
• Project page: https://www.hydromatic.net/flight-data-hsqldb
• API: https://www.hydromatic.net/flight-data-hsqldb/apidocs
• Source code: https://github.com/julianhyde/flight-data-hsqldb
• Distribution: Maven Central
• Developers list: dev at calcite.apache.org (archive, subscribe)
• Issues: https://github.com/julianhyde/flight-data-hsqldb/issues
• Release notes and history