<commit_before>-- The orbital database should have 3 tables:
--
--  * Aircraft (imported from a basestation.sqb, see basestation.load)
--  * aircraft_info (defined below)
--  * reports (See ...)

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS reports (
       "timestamp" timestamp with time zone NOT NULL,
       icao VARCHAR(6) NOT NULL,
       registration VARCHAR(20),
       altitude INTEGER,
       lat DOUBLE PRECISION,
       lon DOUBLE PRECISION,
       "position" geometry(Point,4326),
       speed REAL,
       heading REAL,
       squawk VARCHAR(4)
);

CREATE INDEX reports_icao_index on reports (icao);

CREATE VIEW reports_with_distance_from_me AS
       SELECT *,  ST_Distance_Sphere(position, ST_MakePoint(-118.192310, 34.133777)) AS distance
       FROM reports;


CREATE TABLE IF NOT EXISTS aircraft_info (
       icao VARCHAR(6) primary key,
       image text,
       link text,
       photographer text
);

CREATE INDEX aircraft_info_icao_index on aircraft_info (icao);

CREATE TABLE IF NOT EXISTS faa_registration (
       n_number VARCHAR(6),
       serial_number VARCHAR(30),
       mfr_mdl_code VARCHAR(7),
       eng_mfr_mdl VARCHAR(5),
       year_mfr VARCHAR(4),
       type_registrant VARCHAR(1),<commit_msg>Added provenance column to reports table.<commit_after>-- The orbital database should have 3 tables:
--
--  * Aircraft (imported from a basestation.sqb, see basestation.load)
--  * aircraft_info (defined below)
--  * reports (See ...)

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS provenances(
       code INTEGER primary key,
       description VARCHAR(20)
);

INSERT INTO provenances VALUES
       (1, 'planeplotter logs'),
       (2, 'mlat');

CREATE TABLE IF NOT EXISTS reports (
       "timestamp" timestamp with time zone NOT NULL,
       icao VARCHAR(6) NOT NULL,
       registration VARCHAR(20),
       altitude INTEGER,
       lat DOUBLE PRECISION,
       lon DOUBLE PRECISION,
       "position" geometry(Point,4326),
       speed REAL,
       heading REAL,
       squawk VARCHAR(4),
       provenance INTEGER
);

CREATE INDEX reports_icao_index on reports (icao);

CREATE VIEW reports_with_distance_from_me AS
       SELECT *,  ST_Distance_Sphere(position, ST_MakePoint(-118.192310, 34.133777)) AS distance
       FROM reports;


CREATE TABLE IF NOT EXISTS aircraft_info (
       icao VARCHAR(6) primary key,
       image text,
       link text,
       photographer text
);

CREATE INDEX aircraft_info_icao_index on aircraft_info (icao);

CREATE TABLE IF NOT EXISTS faa_registration (
       n_number VARCHAR(6),
       serial_number VARCHAR(30),
       mfr_mdl_code VARCHAR(7),
       eng_mfr_mdl VARCHAR(5),
       year_mfr VARCHAR(4),
       type_registrant VARCHAR(1),
{'fraction_non_alphanumeric': 0.07965242577842144, 'fraction_numerical': 0.02606806661839247, 'mean_word_length': 2.947142857142857, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2036241', 'n_tokens_mistral': 919, 'n_tokens_neox': 826, 'n_words': 265}