Analyse med DuckDB 🦆 - Sensorjournalistikk: Fra signaler til skup

Sanntidsdata, som vi allerede har sett på, er et svært nyttig verktøy for å orientere seg om øyeblikksbildet – hva som skjer her og nå. På samme måte er det fint å kunne spole tilbake i tid for å se hvordan situasjonen så ut for fem, ti og femten minutter siden – eller i går, forrige uke og kanskje i fjor sommer.

Samtidig er det situasjoner hvor vi kanskje vet hva vi ser etter, eller hvor vi skal se, men ikke nødvendigvis vet når det riktige tidspunktet er. Vi har kanskje hex-koden til et fly vi vet har passert over Norge, men ikke tidspunktet. Hvor har politihelikopteret flydd den siste uken? Og hvilke helikoptre har blitt observert i et gitt geografisk område?

For å kunne jobbe effektivt med denne typen spørsmål og problemstillinger, trenger vi et verktøy som kan håndtere store mengder historiske data effektivt. Til dette skal vi bruke DuckDB.

Hva er DuckDB?¶

DuckDB er en lettvekts, relasjonell analytisk database optimalisert for analyse av store datamengder. Den er designet med fokus på ytelse, enkelhet og fleksibilitet, noe som gjør den svært velegnet for datajournalistisk analyse – for eksempel av store skips- og flytrafikkdatasett.

DuckDB kjører lokalt på datamaskinen din, noe som betyr at du ikke trenger å sette opp en kompleks server for å håndtere dataene. Hvis du allerede har gjort de tekniske forberedelsene tidligere i dette kompendiet, har du alt du trenger på datamaskinen din for å komme i gang.

DuckDB kan integreres direkte med verktøy som Python og R, noe som gir oss muligheten til å kombinere SQL-spørringer med andre dataanalyseverktøy og biblioteker. Dette er svært nyttig for å lage datadrevne historier som involverer statistisk analyse eller visualiseringer. Og hvis du trives i Excel, er det enkelt å eksportere et filtrert datasett direkte til dette verktøyet.

DuckDB, Parquet og historiske data¶

Så hvordan får vi historiske data inn i DuckDB? I disse øvelsene tar vi utgangspunkt i Medieklyngens ADS-B-server, som tilrettelegger dataene i et format som enkelt lar seg importere inn i DuckDB uten ytterligere bearbeiding.

Medieklyngens ADS-B-server lagrer alle data fra radarene som deler data med fellesskapet. Hver natt genereres optimaliserte databasefiler med siste døgns bevegelser. Filene lagres i det standardiserte Parquet-formatet, som er utviklet for å håndtere store datamengder effektivt gjennom optimalisering og komprimering.

I motsetning til radbaserte formater som CSV og Excel, lagrer Parquet data kolonnevis. Dette gir flere fordeler ved analyse av store datasett, siden det blir raskere å hente ut og komprimere spesifikke kolonner. For fly- og skipsdata, som ofte inneholder mange attributter som posisjon, hastighet og høyde, gjør Parquet det enkelt å trekke ut og behandle relevante felter, noe som gir betydelige ytelsesforbedringer sammenlignet med radbaserte formater.

Parquet-filene er hive-partisjonert, noe som betyr at de er delt inn i mapper basert på år, måned og dag. Dette gjør det enklere å filtrere og hente ut spesifikke data, og forbedrer spørringsytelsen ved at man kun leser de nødvendige partisjonene i stedet for hele datasettet.

Parquet-filer kan leses av mange ulike verktøy og programmeringsspråk, noe som gir stor fleksibilitet. For eksempel kan Parquet-filer behandles i verktøy som Apache Spark, Hadoop, Presto, Amazon Athena og DuckDB. Dette gir en robust og skalerbar løsning som er klargjort for fremtidig vekst i datamengdene. Den gode nyheten er at du ikke trenger å forholde deg til denne kompleksiteten med mindre du ønsker å gå i dybden på et senere tidspunkt.

Spørrespråket SQL¶

DuckDB benytter det veletablerte spørrespråket SQL. SQL har røtter tilbake til 1970-tallet, da det ble utviklet av IBM for å håndtere data i relasjonsdatabaser. Språket er intuitivt bygget opp og relativt enkelt å lære seg på et grunnleggende nivå.

I DuckDBs SQL-dialekt bruker du enkle kommandoer for å hente, manipulere og analysere data fra tabeller. Den grunnleggende strukturen består av kommandoer som SELECT, som brukes for å hente spesifikke kolonner fra en tabell, og FROM, som angir hvilken tabell (datakilde) du henter data fra. Du kan også bruke WHERE for å filtrere dataene basert på bestemte kriterier.

Skjematisk forklart:

SELECT [kolonner]
FROM [tabell]
WHERE [betingelser];

For eksempel kan en spørring se slik ut:

SELECT * FROM flights WHERE altitude > 10000;

Analyse i DuckDB¶

Min erfaring er at det er enklere å forstå logikken i SQL ved å utføre spørringene selv – først helt enkelt, deretter bygge på med mer avanserte filtre og sorteringer. Men først må vi logge inn og sette opp alt. Her er det bare å «hamre inn» linje for linje, så kommer vi snart til det spennende og nyttige. De følgende instruksjonene forutsetter at du har installert DuckDB på maskinen din. Hvis du har lastet ned DuckDB som en fil i en mappe i stedet for å installere det, er det viktig at du navigerer til riktig mappe før du begynner å kjøre disse kommandoene.

Forberedelser¶

Lag en tom mappe på valgfri plassering på datamaskinen din.
Start Terminal (Mac og Linux) eller Poweshell (Windows), og naviger til mappen du akkurat opprettet.
Start DuckDB med følgende kommando:

duckdb sensorkurs-tirsdag-11-03-25.db

Dette starter DuckDB med en tom databasefil kalt sensorkurs-tirsdag-11-03-25.db. Filen lagres i mappen du kjører kommandoen fra.

DuckDB svarer med et prompt som ser omtrent slik ut:

v1.2.1 8e52ec4395
Enter ".help" for usage hints.
D

Installer og last inn utvidelser og innstillinger¶

Installer følgende utvidelser ved å skrive inn disse kommandoene i prompten (dette gjøres kun ved første gangs kjøring):

INSTALL httpfs;
INSTALL spatial;
INSTALL h3 FROM community;

Last inn utvidelsene du akkurat installerte ved hjelp av følgende kommandoer (dette må gjøres hver gang, eller automatiseres med et script, se under):

LOAD httpfs;
LOAD spatial;
LOAD h3;

Legg inn tilgangsnøkler til serveren som lagrer de historiske dataene:

CREATE SECRET (
    TYPE R2,
    KEY_ID '9030e0f90a86af08b08b6e2a1222a778',
    SECRET '2fe64ae1c22869400f577bb9421602f0f81a83a2f658cea6bdd556f4fc65064b',
    ACCOUNT_ID 'bca3475a0f4afeb0640daafc17ec2b18'
);

DuckDB bekrefter med:

┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘

Last inn data i DuckDB¶

Nå begynner moroa! La oss starte med å laste ned alle kjente flybevegelser for en spesifikk dag, for eksempel lørdag 8. mars 2025. Kjør følgende kommando:

CREATE TABLE flights_08_03_25 AS
    SELECT * FROM read_parquet('r2://medieklyngen-radar-data/adsb/history/*/*/*/*.parquet', hive_partitioning = true)
    WHERE year = 2025 AND month = 03 AND day = 08;

Dette oppretter en ny tabell i databasen kalt flights_08_03_25 og fyller den med data fra alle Parquet-filer som samsvarer med datoen 8. mars 2025.

Når du trykker Enter for å kjøre kommandoen, tar det litt tid før du får respons. DuckDB laster ned dataene fra Medieklyngens server, pakker dem ut og importerer dem til databasen. Når det er ferdig, ser det forhåpentligvis slik ut:

100% ▕████████████████████████████████████████████████████████████▏

Tell antall posisjoner i datasettet¶

Nå har vi hentet ned dataene for en dag, men hvor mye data dreier det seg om? La oss telle dem:

SELECT count(*) FROM flights_08_03_25;

Dette gir følgende svar:

┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    2805044     │
│ (2.81 million) │
└────────────────┘

2,81 millioner posisjoner! Det gir oss litt å jobbe med.

Bli kjent med datastrukturen¶

La oss ta en nærmere titt på dataene og den underliggende strukturen ved å kjøre en enkel spørring. LIMIT 1 begrenser oss til ett treff – det første:

SELECT * FROM flights_08_03_25 LIMIT 1;

┌─────────┬──────────────────┬─────────┬───┬───────┬─────────┬─────────┐
│ dbFlags │       desc       │  icao   │ … │ year  │   day   │  month  │
│  int64  │     varchar      │ varchar │   │ int64 │ varchar │ varchar │
├─────────┼──────────────────┼─────────┼───┼───────┼─────────┼─────────┤
│    0    │ BOEING 777-200LR │ 040168  │ … │ 2025  │ 08      │ 03      │
├─────────┴──────────────────┴─────────┴───┴───────┴─────────┴─────────┤
│ 1 rows                                          12 columns (6 shown) │
└──────────────────────────────────────────────────────────────────────┘

En Boeing 777-200LR! Og et ICAO-nummer samt en dato. Men vi merker oss også at det står 12 columns (6 shown). Standardvisningen har ikke plass til å vise alt.

Vi kan endre visningen med en dot-kommando kalt .mode. Skriv .help mode for å vise de ulike alternativene.

La oss først prøve alternativet duckbox:

.mode duckbox

Deretter kjører vi spørringen på nytt:

SELECT * FROM flights_08_03_25 LIMIT 1;

...og ser at vi nå får med alle kolonnene.

┌─────────┬──────────────────┬─────────┬─────────┬─────────┬──────────────────────┬─────────┬─────────────────────┬──────────────────────────────┬───────┬─────────┬─────────┐
│ dbFlags │       desc       │  icao   │  ownOp  │    r    │     reg_details      │    t    │      timestamp      │            trace             │ year  │   day   │  month  │
│  int64  │     varchar      │ varchar │ varchar │ varchar │ struct(description…  │ varchar │      timestamp      │ struct(aircraft struct(ale…  │ int64 │ varchar │ varchar │
├─────────┼──────────────────┼─────────┼─────────┼─────────┼──────────────────────┼─────────┼─────────────────────┼──────────────────────────────┼───────┼─────────┼─────────┤
│    0    │ BOEING 777-200LR │ 040168  │ NULL    │ ET-AWE  │ {'description': ge…  │ B77L    │ 2025-03-08 00:00:00 │ {'aircraft': {'alert': 0, …  │ 2025  │ 08      │ 03      │
└─────────┴──────────────────┴─────────┴─────────┴─────────┴──────────────────────┴─────────┴─────────────────────┴──────────────────────────────┴───────┴─────────┴─────────┘

Men ser vi egentlig alt? Se på kolonnene reg_details og trace. Her ser vi at det ligger datastrukturer som fortsatt blir forkortet. Dette er fordi dataene er i JSON-format. Hvis du tar en titt på de ulike tilgjengelige visningene med .help mode, finner vi json som et valg. La oss prøve dette og kjøre spørringen på nytt:

.mode json
SELECT * FROM flights_08_03_25 LIMIT 1;

Nå ser vi den fullstendige strukturen – for ett av de flere millioner datapunktene fra denne dagen.

[{"dbFlags":0,"desc":"BOEING 777-200LR","icao":"040168","ownOp":null,"r":"ET-AWE","reg_details":"{'description': general, 'iso2': ET, 'iso3': ETH, 'nation': Ethiopia}","t":"B77L","timestamp":"2025-03-08 00:00:00","trace":"{'aircraft': {'alert': 0, 'alt_geom': 16600, 'baro_rate': 832, 'category': NULL, 'emergency': none, 'flight': NULL, 'geom_rate': 832, 'gva': 2, 'ias': 319, 'mach': 0.648, 'mag_heading': 51.15, 'nac_p': 11, 'nac_v': 2, 'nav_altitude_fms': NULL, 'nav_altitude_mcp': 25008, 'nav_heading': NULL, 'nav_modes': NULL, 'nav_qnh': 1013.0, 'nic': 8, 'nic_baro': 1, 'oat': NULL, 'rc': 186, 'roll': NULL, 'sda': 2, 'sil': 3, 'sil_type': perhour, 'spi': 0, 'squawk': 7122, 'tas': NULL, 'tat': NULL, 'track': 52.37, 'track_rate': NULL, 'true_heading': 56.23, 'type': adsb_icao, 'version': 2, 'wd': NULL, 'ws': NULL}, 'altitude': 16450, 'flags': 1, 'geometric_altitude': 16600, 'geometric_vertical_rate': 832, 'ground_speed': 407.8, 'h3_15': 8f1fa0af699899b, 'indicated_airspeed': 319, 'lat': 50.670364, 'lon': 6.125528, 'on_ground': false, 'roll_angle': NULL, 'source': adsb_icao, 'timestamp': 2025-03-08 07:04:04.840000, 'track_degrees': 52.4, 'vertical_rate': 832}","year":2025,"day":"08","month":"03"}]

Formatér JSON-data for bedre oversikt¶

JSON kan være vanskelig å lese i sin rå form. For en bedre oversikt kan vi bruke et gratis verktøy for å formatere dataene:

JSON Formatter

En rask copy-paste senere, og dataene er langt mer oversiktlige.

[
   {
      "dbFlags":0,
      "desc":"BOEING 777-200LR",
      "icao":"040168",
      "ownOp":null,
      "r":"ET-AWE",
      "reg_details":"{'description': general, 'iso2': ET, 'iso3': ETH, 'nation': Ethiopia}",
      "t":"B77L",
      "timestamp":"2025-03-08 00:00:00",
      "trace":"{'aircraft': {'alert': 0, 'alt_geom': 16600, 'baro_rate': 832, 'category': NULL, 'emergency': none, 'flight': NULL, 'geom_rate': 832, 'gva': 2, 'ias': 319, 'mach': 0.648, 'mag_heading': 51.15, 'nac_p': 11, 'nac_v': 2, 'nav_altitude_fms': NULL, 'nav_altitude_mcp': 25008, 'nav_heading': NULL, 'nav_modes': NULL, 'nav_qnh': 1013.0, 'nic': 8, 'nic_baro': 1, 'oat': NULL, 'rc': 186, 'roll': NULL, 'sda': 2, 'sil': 3, 'sil_type': perhour, 'spi': 0, 'squawk': 7122, 'tas': NULL, 'tat': NULL, 'track': 52.37, 'track_rate': NULL, 'true_heading': 56.23, 'type': adsb_icao, 'version': 2, 'wd': NULL, 'ws': NULL}, 'altitude': 16450, 'flags': 1, 'geometric_altitude': 16600, 'geometric_vertical_rate': 832, 'ground_speed': 407.8, 'h3_15': 8f1fa0af699899b, 'indicated_airspeed': 319, 'lat': 50.670364, 'lon': 6.125528, 'on_ground': false, 'roll_angle': NULL, 'source': adsb_icao, 'timestamp': 2025-03-08 07:04:04.840000, 'track_degrees': 52.4, 'vertical_rate': 832}",
      "year":2025,
      "day":"08",
      "month":"03"
   }
]

Dette viser oss den fullstendige strukturen på dataene lagret i Parquet-formatet. Vi ser mange datafelter, men også en del nullverdier (NULL), noe som er ganske vanlig. Blant feltene vi bør merke oss:

desc: En beskrivelse av fartøyet.
icao: Fartøyets ICAO-adresse.
r: Registreringsnummer.
nation: Registreringsland.
t: Fartøytype.

Under feltet trace finner vi en ny understruktur med interessante data:

altitude: Høyde.
lat og lon: GPS-posisjon.
ground_speed: Bakkefart.
timestamp: Tidspunkt.

Finn et spesifikt fartøy¶

Datapunktet vi har sett på til nå ble tilfeldig valgt. Vanligvis vil vi lete etter spesifikke fartøy, for eksempel et politihelikopter. I Norge har politihelikoptrene registreringsnumrene LN-ORA, LN-ORB, og LN-ORC. La oss sjekke om vi finner et av dem i datasettet:

SELECT * FROM flights_08_03_25 WHERE r = 'LN-ORA' LIMIT 1;

Dette filtrerer på r (registrering), slik at vi kun får data for LN-ORA, og begrenser treffet til ett datapunkt.

[{"dbFlags":0,"desc":"AGUSTA AW-169","icao":"479c1e","ownOp":null,"r":"LN-ORA","reg_details":"{'description': helicopters, 'iso2': NO, 'iso3': NOR, 'nation': Norway}","t":"A169","timestamp":"2025-03-08 00:00:00","trace":"{'aircraft': {'alert': NULL, 'alt_geom': NULL, 'baro_rate': NULL, 'category': NULL, 'emergency': NULL, 'flight': NULL, 'geom_rate': NULL, 'gva': NULL, 'ias': NULL, 'mach': NULL, 'mag_heading': NULL, 'nac_p': NULL, 'nac_v': NULL, 'nav_altitude_fms': NULL, 'nav_altitude_mcp': NULL, 'nav_heading': NULL, 'nav_modes': NULL, 'nav_qnh': NULL, 'nic': NULL, 'nic_baro': NULL, 'oat': NULL, 'rc': NULL, 'roll': NULL, 'sda': NULL, 'sil': NULL, 'sil_type': NULL, 'spi': NULL, 'squawk': NULL, 'tas': NULL, 'tat': NULL, 'track': NULL, 'track_rate': NULL, 'true_heading': NULL, 'type': NULL, 'version': NULL, 'wd': NULL, 'ws': NULL}, 'altitude': 1050, 'flags': 5, 'geometric_altitude': 1300, 'geometric_vertical_rate': 1216, 'ground_speed': 80.5, 'h3_15': 8f0999771432244, 'indicated_airspeed': NULL, 'lat': 59.809338, 'lon': 10.855534, 'on_ground': false, 'roll_angle': NULL, 'source': adsb_icao, 'timestamp': 2025-03-08 14:05:42.490000, 'track_degrees': 75.6, 'vertical_rate': 1216}","year":2025,"day":"08","month":"03"}]

Se der, ja! Her var det treff på første forsøk. Det ser ut til at det første politihelikopteret har vært ute å flydd. La oss «forfine» dataene litt:

[
  {
    "dbFlags": 0,
    "desc": "AGUSTA AW-169",
    "icao": "479c1e",
    "ownOp": null,
    "r": "LN-ORA",
    "reg_details": "{'description': helicopters, 'iso2': NO, 'iso3': NOR, 'nation': Norway}",
    "t": "A169",
    "timestamp": "2025-03-08 00:00:00",
    "trace": "{'aircraft': {'alert': NULL, 'alt_geom': NULL, 'baro_rate': NULL, 'category': NULL, 'emergency': NULL, 'flight': NULL, 'geom_rate': NULL, 'gva': NULL, 'ias': NULL, 'mach': NULL, 'mag_heading': NULL, 'nac_p': NULL, 'nac_v': NULL, 'nav_altitude_fms': NULL, 'nav_altitude_mcp': NULL, 'nav_heading': NULL, 'nav_modes': NULL, 'nav_qnh': NULL, 'nic': NULL, 'nic_baro': NULL, 'oat': NULL, 'rc': NULL, 'roll': NULL, 'sda': NULL, 'sil': NULL, 'sil_type': NULL, 'spi': NULL, 'squawk': NULL, 'tas': NULL, 'tat': NULL, 'track': NULL, 'track_rate': NULL, 'true_heading': NULL, 'type': NULL, 'version': NULL, 'wd': NULL, 'ws': NULL}, 'altitude': 1050, 'flags': 5, 'geometric_altitude': 1300, 'geometric_vertical_rate': 1216, 'ground_speed': 80.5, 'lat': 59.809338, 'lon': 10.855534, 'on_ground': false, 'timestamp': 2025-03-08 14:05:42.490000, 'track_degrees': 75.6, 'vertical_rate': 1216}"
  }
]

Vi ser umiddelbart at dette er et helikopter av typen Augusta AW-169, og at det har ICAO-adressen 479c1e. Det er mange nullverdier her, men vi ser at både GPS-posisjon, geometrisk høyde og bakkefart er oppgitt. Det er en god start. Men hva om vi ønsker ikke bare å se ett enkelt datapunkt, men alle punktene fra dette helikopteret i det aktuelle datasettet? La oss prøve med en litt mer omfattende spørring:

SELECT * FROM flights_08_03_25 WHERE r = 'LN-ORA';

Ved å fjerne filteret LIMIT 1, får vi mye mer data. Men hva om vi kunne lagre disse dataene til en fil? I DuckDB kan vi bruke COPY():

COPY (
   SELECT * FROM flights_08_03_25 WHERE r = 'LN-ORA'
   ) TO 'ln-ora.json';

Denne filen kan vi nå åpne i en nettleser eller Notisblokk. For å få filen mer håndterlig:

COPY (
  SELECT r, trace.altitude,
     trace.ground_speed,
     trace.lon,
     trace.lat,
     trace.timestamp
   FROM flights_08_03_25
   WHERE r = 'LN-ORA'
   ORDER BY trace.timestamp
   ) TO 'ln-ora.csv' WITH (HEADER);

Nå kan du åpne filen i Excel. Vi har hentet ut registreringsnummer, bakkefart, koordinater (lat og lon) og tidspunkt.

Note

For kartverktøy kan vi eksportere til GeoJSON:

COPY (
   SELECT r, trace.altitude,
      trace.ground_speed,
      ST_POINT(trace.lon, trace.lat) loc,
      trace.timestamp
   FROM flights_08_03_25
   WHERE r = 'LN-ORA'
   ORDER BY trace.timestamp
   LIMIT 5000
) TO 'ln-ora.geojson' WITH (FORMAT gdal, DRIVER 'GeoJSON');

Vi begrenser datasettet til 5000 punkter for å gjøre det mer håndterlig.

Vis fartøy pr. land¶

I datastrukturen legger vi merke til at nation er et felt som angir registreringslandet til de sporede fartøyene. La oss se hvilke land som er representert i datasettet, og hvor mange fly som er registrert per land:

.mode duckbox

SELECT   reg_details.nation,
         COUNT(DISTINCT r)
FROM     flights_08_03_25
WHERE    LENGTH(reg_details.nation)
GROUP BY 1
ORDER BY 2 DESC;

I spørringen over har vi introdusert et par nye konsepter. Vi begynner med å velge ut feltet nation, som ligger under noden reg_details i datastrukturen. Deretter kjører vi en funksjon som teller antall unike fartøy i datasettet og kobler dette antallet mot hver nasjon. Til slutt grupperer vi visningen på land og sorterer etter antall fly per land, synkende.

Vi ser at det er 69 land representert, og at «United Kingdom» ligger på toppen med hele 239 unike observerte fly. Et neste steg kan være å se på fly fra et spesifikt land. La oss prøve oss på Russland med denne spørringen:

SELECT r, trace.altitude,
   trace.ground_speed,
   trace.lon,
   trace.lat,
   reg_details.nation,
   trace.timestamp
FROM flights_08_03_25
WHERE reg_details.nation = 'Russia'
ORDER BY trace.timestamp;

Ser man det! Her har vi fire unike posisjoner:

┌──────────┬──────────┬──────────────┬───────────┬───────────┬─────────┬────────────────────────────┐
│    r     │ altitude │ ground_speed │    lon    │    lat    │ nation  │         timestamp          │
│ varchar  │  int64   │    double    │  double   │  double   │ varchar │          varchar           │
├──────────┼──────────┼──────────────┼───────────┼───────────┼─────────┼────────────────────────────┤
│ RA-73716 │    32000 │        402.1 │ 21.901337 │ 59.239197 │ Russia  │ 2025-03-08 19:39:50.160000 │
│ RA-73716 │    32025 │        403.8 │ 21.549114 │ 59.149366 │ Russia  │ 2025-03-08 19:41:38.690000 │
│ RA-73716 │    32025 │        403.8 │ 21.546178 │ 59.148622 │ Russia  │ 2025-03-08 19:41:39.610000 │
│ RA-73716 │    32000 │        404.3 │ 21.447112 │ 59.119108 │ Russia  │ 2025-03-08 19:42:11.010000 │
└──────────┴──────────┴──────────────┴───────────┴───────────┴─────────┴────────────────────────────┘

Kanskje ikke det mest revolusjonerende funnet til å begynne med, men leter vi lenge nok, dukker det plutselig opp interessante ting.

Nødkoder¶

Vi husker fra tidligere at piloter kan melde fra om nødsituasjoner ved å sette spesielle squawk-koder i transponderen. La oss se om vi finner noen slike koder i dataene.

Vi begynner med å laste inn et litt bredere datasett, med data fra en nesten helt tilfeldig valgt dag i oktober 2024.

CREATE TABLE flights_15_10_24 AS
   SELECT * FROM read_parquet('r2://medieklyngen-radar-data/adsb/history/*/*/*/*.parquet', hive_partitioning = true)
   WHERE year = 2024 AND month = 10 AND day = 15;

Deretter kjører vi følgende spørring for å se etter posisjoner med squawk satt til 7700, som indikerer en nødsituasjon:

SELECT DISTINCT r, FIRST(trace.timestamp) as timestamp
       FROM  flights_15_10_24 WHERE trace.aircraft.squawk::INT = 7700
       GROUP BY 1;

9H-VISTA  2024-10-15 15:49:20.890000

Her får vi opp ett treff! La oss bygge ut spørringen til også å inneholde koordinater og høyde:

SELECT r, trace.timestamp, trace.lat, trace.lon, trace.altitude
      FROM  flights_15_10_24 WHERE trace.aircraft.squawk::INT = 7700
      GROUP BY ALL
      ORDER BY 2 ASC;

┌──────────┬────────────────────────────┬───────────┬──────────┬──────────┐
│    r     │         timestamp          │    lat    │   lon    │ altitude │
│ varchar  │          varchar           │  double   │  double  │  int64   │
├──────────┼────────────────────────────┼───────────┼──────────┼──────────┤
│ 9H-VISTA │ 2024-10-15 15:49:20.890000 │ 52.066315 │ 4.629288 │    13150 │
│ 9H-VISTA │ 2024-10-15 15:49:37.060000 │ 52.087326 │  4.63028 │    12875 │
│ 9H-VISTA │ 2024-10-15 15:49:47.490000 │ 52.100473 │ 4.630973 │    12700 │
│ 9H-VISTA │ 2024-10-15 15:50:00.160000 │ 52.116577 │ 4.631882 │    12475 │
│ 9H-VISTA │ 2024-10-15 15:50:15.500000 │ 52.135803 │ 4.632874 │    12225 │
│ 9H-VISTA │ 2024-10-15 15:50:21.350000 │ 52.142899 │ 4.633179 │    12125 │
│ 9H-VISTA │ 2024-10-15 15:50:27.080000 │ 52.149819 │ 4.633562 │    12025 │
│ 9H-VISTA │ 2024-10-15 15:50:29.100000 │ 52.152328 │ 4.633636 │    11975 │
│ 9H-VISTA │ 2024-10-15 15:50:35.880000 │ 52.160294 │ 4.634018 │    11875 │
│ 9H-VISTA │ 2024-10-15 15:50:42.860000 │ 52.168671 │ 4.634476 │    11750 │
│ 9H-VISTA │ 2024-10-15 15:50:49.860000 │ 52.177002 │ 4.634857 │    11625 │
│ 9H-VISTA │ 2024-10-15 15:50:52.750000 │ 52.180573 │  4.63501 │    11575 │
│ 9H-VISTA │ 2024-10-15 15:50:57.830000 │ 52.186478 │ 4.635239 │    11500 │
│ 9H-VISTA │ 2024-10-15 15:51:00.720000 │ 52.189853 │ 4.635446 │    11450 │
│ 9H-VISTA │ 2024-10-15 15:51:08.420000 │ 52.198883 │ 4.635849 │    11325 │
│ 9H-VISTA │ 2024-10-15 15:51:13.890000 │ 52.205262 │ 4.636152 │    11225 │
│ 9H-VISTA │ 2024-10-15 15:51:17.250000 │ 52.209091 │ 4.636383 │    11175 │
│ 9H-VISTA │ 2024-10-15 15:51:20.930000 │ 52.213257 │ 4.636612 │    11125 │
│ 9H-VISTA │ 2024-10-15 15:51:23.220000 │ 52.216049 │ 4.636688 │    11075 │
│ 9H-VISTA │ 2024-10-15 15:51:28.360000 │ 52.221771 │ 4.636993 │    11000 │
│ 9H-VISTA │ 2024-10-15 15:51:30.110000 │  52.22406 │ 4.637146 │    10950 │
│ 9H-VISTA │ 2024-10-15 15:51:34.280000 │ 52.228771 │ 4.637329 │    10875 │
│ 9H-VISTA │ 2024-10-15 15:51:40.810000 │ 52.236191 │ 4.637604 │    10775 │
│ 9H-VISTA │ 2024-10-15 15:52:08.170000 │  52.26709 │ 4.638443 │    10325 │
│ 9H-VISTA │ 2024-10-15 15:52:55.960000 │ 52.319781 │ 4.639448 │     9525 │
│ 9H-VISTA │ 2024-10-15 15:54:08.700000 │ 52.398605 │  4.64325 │     8250 │
│ 9H-VISTA │ 2024-10-15 15:54:13.730000 │ 52.404053 │ 4.643555 │     8125 │
│ 9H-VISTA │ 2024-10-15 15:54:19.850000 │ 52.410977 │ 4.643921 │     7925 │
│ 9H-VISTA │ 2024-10-15 15:55:58.310000 │ 52.520737 │ 4.650116 │     5925 │
│ 9H-VISTA │ 2024-10-15 15:57:28.100000 │ 52.592484 │ 4.745387 │     4275 │
│ 9H-VISTA │ 2024-10-15 15:58:53.190000 │ 52.581848 │ 4.860611 │     2875 │
│ 9H-VISTA │ 2024-10-15 15:58:58.430000 │ 52.577362 │ 4.860458 │     2775 │
│ 9H-VISTA │ 2024-10-15 16:00:18        │ 52.526001 │ 4.786606 │     1925 │
│ 9H-VISTA │ 2024-10-15 16:02:00.020000 │ 52.454177 │  4.75143 │     1900 │
│ 9H-VISTA │ 2024-10-15 16:02:09.190000 │ 52.447678 │ 4.751434 │     1900 │
│ 9H-VISTA │ 2024-10-15 16:02:16.180000 │ 52.442734 │ 4.751205 │     1900 │
│ 9H-VISTA │ 2024-10-15 16:02:34.970000 │  52.42923 │ 4.749756 │     1875 │
│ 9H-VISTA │ 2024-10-15 16:03:01.130000 │ 52.411743 │ 4.746933 │     1525 │
│ 9H-VISTA │ 2024-10-15 16:03:13.310000 │ 52.404971 │ 4.746015 │     1350 │
│ 9H-VISTA │ 2024-10-15 16:03:21.830000 │ 52.400782 │ 4.745544 │     1250 │
│ 9H-VISTA │ 2024-10-15 16:03:30.950000 │ 52.396266 │ 4.745466 │     1175 │
│ 9H-VISTA │ 2024-10-15 16:03:35.590000 │ 52.393985 │ 4.745466 │     1150 │
│ 9H-VISTA │ 2024-10-15 16:03:38.760000 │ 52.392356 │ 4.745466 │     1125 │
│ 9H-VISTA │ 2024-10-15 16:03:44.880000 │ 52.389267 │ 4.745407 │     1050 │
│ 9H-VISTA │ 2024-10-15 16:03:51.760000 │ 52.385745 │ 4.745152 │     1000 │
│ 9H-VISTA │ 2024-10-15 16:04:01.750000 │ 52.380661 │ 4.744644 │      900 │
│ 9H-VISTA │ 2024-10-15 16:04:03.500000 │ 52.379694 │ 4.744524 │      875 │
│ 9H-VISTA │ 2024-10-15 16:04:09.180000 │ 52.376854 │ 4.744289 │      825 │
│ 9H-VISTA │ 2024-10-15 16:04:14.690000 │ 52.374023 │ 4.743958 │      775 │
│ 9H-VISTA │ 2024-10-15 16:04:18.630000 │ 52.372012 │  4.74374 │      725 │
│ 9H-VISTA │ 2024-10-15 16:04:26.270000 │ 52.367935 │ 4.743423 │      650 │
│ 9H-VISTA │ 2024-10-15 16:04:47.790000 │ 52.356697 │ 4.742327 │      425 │
│ 9H-VISTA │ 2024-10-15 16:05:03.250000 │ 52.348597 │ 4.741699 │      275 │
├──────────┴────────────────────────────┴───────────┴──────────┴──────────┤
│ 53 rows                                                       5 columns │
└─────────────────────────────────────────────────────────────────────────┘

Hvis vi ser nærmere på kolonnen altitude, ser vi at denne faller med tiden, før den ender opp på kun 275 fot. Interessant!
La oss finne ut hvor dette er. Kopier koordinatene 52.348597, 4.741699 og lim dem inn i Google Maps.

Vi ser at flyet befinner seg rett ved Schiphol-lufthavnen i Amsterdam. Det kan se ut som om flyet er på finalen av det som kanskje er en nødlanding. La oss utvide spørringen med flere kolonner.

SELECT r, trace.timestamp, trace.lat, trace.lon, trace.altitude, "desc", t, trace.aircraft.category, trace.aircraft.flight 
       FROM  flights_15_10_24 WHERE trace.aircraft.squawk::INT = 7700
       GROUP BY ALL
       ORDER BY 2 DESC;

Et kjapt Google-søk forteller oss at dette er et privatfly eid av Vista Jet. Flyet landet med nødkoden 7700 aktiv 15. oktober 2024 på Schiphol i Amsterdam. Hadde dette vært i Norge, kunne det vært spennende å undersøke saken videre.
Vi kan også ta med oss rådataene videre til et kartverktøy eller visualisere flyets fart og høyde over tid i en graf dersom vi ønsker å presentere disse dataene til sluttbrukere.

Interessante fartøy¶

Mens mange av sporingsdataene vi har sett på så langt er offentlig tilgjengelig i tjenester som Flightradar24 og Radarbox, er det selvsagt ekstra spennende med informasjon som er vanskeligere tilgjengelig. Her kommer feltet dbFlags til unnsetning. Dataene her stammer fra en database som oppdateres periodisk på serveren. La oss hente inn de tilgjengelige dataene fra hele mars 2025, og deretter kjøre en ny spørring:

CREATE TABLE flights_03_25 AS
   SELECT * FROM read_parquet('r2://medieklyngen-radar-data/adsb/history/*/*/*/*.parquet', hive_partitioning = true)
   WHERE year = 2025 AND month = 03;

.maxrows 400
SELECT r, dbFlags AS flag
     FROM   flights_03_25 WHERE dbFlags > 0
     GROUP BY ALL
     ORDER BY 2 DESC
     LIMIT 1000;

Vi får opp en liste over fartøy som er flagget i databasen som spesielle.

dbFlags = 1 angir at fartøyet er militært.
dbFlags = 2 angir at fartøyet er klassifisert som interessant.
dbFlags = 4 står for PIA: Limiting Aircraft Data Displayed.
dbFlags = 8 står for LADD: Privacy International Civil Aviation Organization (ICAO) Address.

LADD og PIA husker vi fra tidligere. LADD er flagget som skal signalisere til Flightradar24 og andre tjenester at det aktuelle fartøyet bør filtreres ut fra offentlig visning. PIA er et midlertidig ICAO-nummer som man kan søke om å få innvilget fra amerikanske luftfartsmyndigheter.

Disse flaggene kan gi oss svært verdifull informasjon. Det logiske er kanskje å tenke på at hvert fartøy kun kan ha ett av disse flaggene. Da kunne vi ha kjørt føglende spørring for å få ut alle fartøy som har LADD-flagget satt:

SELECT r, dbFlags
     FROM   flights_03_25 WHERE dbFlags = 8
     GROUP BY ALL
     ORDER BY 2 DESC
     LIMIT 1000;

Men sannheten er at hvert fartøy kan ha alle disse flaggene satt på en gang, gjennom en lur teknisk løsning kjent som bitwise.

Alt av data i en datamaskin lagres som biter – det vil si 0-er og 1-ere. Når vi snakker om en heltallsverdi, som dbFlags, består den egentlig bare av en rekke slike biter. Her er et forenklet eksempel:

Binærverdi: 1000  →  desimalverdi: 8

Hver posisjon i dette binærtallet kan representere et “ja/nei”-flagg. Og siden vi kan slå sammen flere slike, kan ett enkelt tall romme flere tilstander samtidig.

Flaggtype	Desimalverdi	Binærverdi
Militært	1	`0001`
Interessant	2	`0010`
PIA	4	`0100`
LADD	8	`1000`

Hvis et fartøy både er markert som PIA og LADD, får det:

4 + 8 = 12  →  Binært: 1100

Med andre ord: dbFlags = 12 forteller oss at begge disse to flaggene er satt.

Hvordan finner vi ut hvilke flagg som er satt?¶

For å sjekke om et bestemt flagg er slått på i en gitt verdi, bruker vi en bitvis AND-operasjon. I SQL kan det se slik ut:

SELECT * FROM flights_03_25 WHERE dbFlags & 8 != 0;

Denne spørringen returnerer alle rader der LADD-flagget er satt. Vi sjekker altså om biten for tallet 8 (som tilsvarer 1000 binært) er skrudd på.

Det samme prinsippet gjelder for de andre flaggene:

-- Alle militære fartøy
SELECT * FROM flights_03_25 WHERE dbFlags & 1 != 0;

-- Alle klassifisert som interessante
SELECT * FROM flights_03_25 WHERE dbFlags & 2 != 0;

Kombinasjoner? Null problem¶

Hvis du vil hente ut fartøy som enten har LADD eller PIA, gjør du slik:

SELECT * FROM flights_03_25 WHERE dbFlags & (8 + 4) != 0;

Hvis vi går tilbake til utgangsspørringen lenger oppe, er det slik den skal se ut hvis vi vil hente ut alle fartøy med LADD-flagget satt:

SELECT r, dbFlags
     FROM   flights_03_25 WHERE dbFlags & 8 != 0
     GROUP BY ALL
     ORDER BY 2 DESC
     LIMIT 1000;

Ønsker vi å bygge ut spørringen til å gi oss alle fartøy som har både PIA (4) og LADD (8) satt, ser det slik ut:

SELECT r, dbFlags
     FROM   flights_03_25 WHERE dbFlags & (8 + 4) != 0
     GROUP BY ALL
     ORDER BY 2 DESC
     LIMIT 1000;

Tekniske flagg¶

Siden vi akkurat har dykket ned i Bitwise, passer det bra å se på et annet flagg i datastrukturen som også benytter seg av det samme konseptet: trace.flags.

trace.flags = 1 angir at posisjonen er foreldet (ingen posisjon mottatt på 20 sekunder før denne).
trace.flags = 2 angir starten på en ny flygning (forsøker å oppdage et skillepunkt mellom landing og avgang som skiller flyvninger).
trace.flags = 4 angir at vertikal hastighet er basert på geometrisk høyde, ikke barometrisk.
trace.flags = 8 angir at høyden er basert på geometrisk måling, ikke barometrisk.

Her er det kanskje flagget 2 som er av størst interesse, da det kan hjelpe oss å identifisere starten på en flygning. La oss prøve:

SELECT r,
        trace.timestamp AS flight_start,
FROM   flights_08_03_25
WHERE  r = 'SE-ROI' and trace.flags & 2 != 0
ORDER BY trace.timestamp;

┌─────────┬────────────────────────────┐
│    r    │        flight_start        │
│ varchar │          varchar           │
├─────────┼────────────────────────────┤
│ SE-ROI  │ 2025-04-01 06:28:48.060000 │
│ SE-ROI  │ 2025-04-01 08:50:32.850000 │
│ SE-ROI  │ 2025-04-01 15:39:22.160000 │
│ SE-ROI  │ 2025-04-01 16:45:08.970000 │
│ SE-ROI  │ 2025-04-01 18:31:13.950000 │
│ SE-ROI  │ 2025-04-01 22:06:13.600000 │
└─────────┴────────────────────────────┘

Voila, her har vi en (estimert) oversikt over fartøyet SE-ROIs avanger 8. mars 2025, i UTC-tid.

Søk i geografiske sektorer¶

DuckDB har en utvidelse kalt spatial som lar oss gjøre geografiske spørringer. Kort fortalt gjør denne modulen DuckDB i stand til å håndtere koordinatsystemer. Vi begynner med å se på tradisjonelle former for geografiske søk, før vi utforsker det enkle og geniale H3-rammeverket for geografisk søk, utviklet av ingen ringere enn Uber.

La oss starte med å hente inn data fra en ny dato, nemlig 16. mars 2025.

CREATE TABLE flights_16_03_25 AS
   SELECT * FROM read_parquet('r2://medieklyngen-radar-data/adsb/history/*/*/*/*.parquet', hive_partitioning = true)
   WHERE year = 2025 AND month = 03 AND day = 16;

Punkt og radius¶

Når man vurderer måter å avgrense et søk geografisk på, dukker kanskje passeren opp i tankene? Eller? I den analoge verdenen starter vi med et geografisk utgangspunkt på kartet, setter passernålen på ønsket posisjon og måler opp en radius. Denne metoden kan vi enkelt replikere i DuckDB. Se følgende spørring:

SELECT r, trace.lon, trace.lat, trace.timestamp
FROM flights_16_03_25
WHERE ST_DWithin(ST_Point(trace.lon, trace.lat), ST_Point(5.332982, 60.385434), 0.01);

Det var en munnfull! Første delen av spørringen er kjent stoff, men etter WHERE begynner det å balle på seg. Her bruker vi geo-funksjonen ST_DWithin, som filtrerer gjennom hvert datapunkt i tabellen og sjekker om det befinner seg innenfor vårt forhåndsdefinerte område.

Filteret vi har satt opp, er: «Alle datapunkter innenfor 0,01 grad fra det geografiske punktet 5.332982, 60.385434». Men hva betyr 0,01 grad? Funksjonen forholder seg til geografiske grader, og 0,01 grad tilsvarer omtrent 1 kilometer. Dette kan være et godt utgangspunkt. Alternativt kan vi prøve 0,1 grader, som tilsvarer rundt 11 kilometer.

Avgrensningsboks¶

En annen måte å definere et geografisk område på er å tegne en avgrensningsboks ved hjelp av fire geografiske koordinater: to lengdegrader og to breddegrader. Ved å definere disse som punkter på et kart og trekke en tenkt linje mellom dem, får vi en sektor i form av et kvadrat eller et rektangel vi kan bruke som et filter.

SELECT r, trace.lon, trace.lat, trace.timestamp
FROM flights_16_03_25
WHERE (trace.lon BETWEEN 5.9 AND 6) AND (trace.lat BETWEEN 59 AND 59.1);

Denne metoden er litt mer lettlest enn den forrige. Her ber vi om alle datapunkter som ligger mellom lengdegradene 5.9 og 6.0, samt mellom breddegradene 59.0 og 59.1.

I tillegg til avgrensningsbokser og punkt-og-radius-metoden er det mulig å gjøre komplekse søk i områder definert via polygoner. For eksempel kan man søke etter alle datapunkter innenfor et spesifikt fylke. Siden dette er mer spesialisert og mindre relevant for fly- og skipstrafikkdata, kommer vi ikke til å gå i dybden på dette her. I stedet skal vi utforske en annen, svært effektiv metode for geografiske søk: H3.

H3¶

H3 er et geografisk rutenett-system utviklet av Uber for å effektivisere og forenkle geografiske analyser. Systemet deler jordkloden inn i sekskantede celler, kjent som heksagoner, i motsetning til tradisjonelle rutemønstre basert på kvadrater. Hver heksagon har en unik identifikator (H3 ID) og kan representere geografiske områder med høy presisjon.

H3-rammeverket gir en mer naturlig tilnærming til romlige analyser sammenlignet med kvadratiske rutenett, som ofte introduserer skjevheter på grunn av jordens krumning. En annen fordel med heksagoner er at de overlapper i svært liten grad, i motsetning til for eksempel sirkler.

H3 opererer med 15 ulike oppløsninger. Lavere oppløsninger dekker større områder, mens høyere oppløsninger gir mer detaljerte inndelinger. Dette gjør det mulig å skalere geografiske analyser etter behov – for eksempel kan man undersøke et helt kontinent med grov oppløsning eller et spesifikt nabolag med høy detaljgrad.

Et heksagon i H3 kan også grupperes med sine «foreldre» på høyere nivåer. Dette betyr at man enkelt kan aggregere data for større områder ved å slå sammen flere små heksagoner til større enheter. Slike hierarkiske strukturer er nyttige for analyser på tvers av forskjellige geografiske skalaer.

Det er mulig å oversette vanlige koordinater til H3-heksagoner direkte i DuckDB ved hjelp av innebygde funksjoner. På Medieklyngens ADS-B-server har vi imidlertid allerede gjort denne jobben, for å sikre mest mulig effektive spørringer.

Vi installerte og lastet inn H3-utvidelsen i starten av dette kapittelet med følgende kommandoer:

INSTALL h3 FROM community;
LOAD h3;

Med dette tillegget på plass, er det bare å gå i gang.

Det første vi må gjøre, er å finne H3-sektoren til området vi ønsker å søke i. Til dette finnes det flere åpne løsninger, for eksempel denne: H3 Viewer.
Finn området du ønsker å undersøke, for eksempel Kristiansand. Etter hvert som du zoomer inn, vil du se at rutenettet av heksagoner endrer størrelse. Velg et passende utsnitt hvor ett heksagon dekker området du vil analysere. Noter Current H3 resolution øverst til høyre i skjermbildet.
Klikk på ønsket heksagon. Denne kopieres da til utklippsboken din.
Kjør følgende spørring, eventuelt tilpasset med din egen heksagon-ID og oppløsning. Oppløsningen spesifiserer du der det nå står 4 (etter trace.h3_15), mens heksagon-ID-en settes til slutt på samme linje.

CREATE TABLE flights_04_08_24 AS
   SELECT * FROM read_parquet('r2://medieklyngen-radar-data/adsb/history/*/*/*/*.parquet', hive_partitioning = true)
   WHERE year = 2024 AND month = 08 AND day = 04;

SELECT r, trace.lat, trace.lon, timestamp
    FROM flights_04_08_24
    WHERE h3_cell_to_parent(trace.h3_15, 4) = '84099e9ffffffff';

┌─────────┬───────────┬──────────┬─────────────────────┐
│    r    │    lat    │   lon    │      timestamp      │
│ varchar │  double   │  double  │      timestamp      │
├─────────┼───────────┼──────────┼─────────────────────┤
│ HB-JMD  │ 58.188714 │ 8.422119 │ 2024-08-04 00:00:00 │
│ HB-JMD  │  58.19067 │ 8.420746 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.192532 │ 8.419373 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.194626 │  8.41792 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.196686 │ 8.416502 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.198444 │ 8.415253 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.201796 │ 8.412872 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.203565 │ 8.411682 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.205246 │ 8.410477 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.207149 │ 8.409119 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.215389 │ 8.403351 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.217422 │ 8.401883 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.219482 │ 8.400465 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.221497 │ 8.399048 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.223236 │ 8.397807 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.227127 │ 8.395149 │ 2024-08-04 00:00:00 │
│ HB-JMD  │  58.23117 │ 8.392273 │ 2024-08-04 00:00:00 │
│ HB-JMD  │  58.25061 │ 8.378581 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.254679 │ 8.375702 │ 2024-08-04 00:00:00 │
│ HB-JMD  │ 58.264176 │ 8.369019 │ 2024-08-04 00:00:00 │
│   ·     │     ·     │     ·    │          ·          │
│   ·     │     ·     │     ·    │          ·          │
│   ·     │     ·     │     ·    │          ·          │
│ SP-LSE  │ 58.239166 │ 8.104278 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.250931 │ 8.074951 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.253422 │ 8.068726 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.271988 │ 8.022589 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.273998 │ 8.017548 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.276749 │ 8.010717 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.284622 │ 7.991136 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.285812 │ 7.988212 │ 2024-08-04 00:00:00 │
│ SP-LSE  │  58.28829 │ 7.981934 │ 2024-08-04 00:00:00 │
│ SP-LSE  │   58.2895 │ 7.979004 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.291763 │ 7.973328 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.292953 │ 7.970404 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.294016 │ 7.967651 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.295226 │ 7.964722 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.297833 │ 7.958221 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.299043 │ 7.955292 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.320876 │ 7.901184 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.322159 │  7.89793 │ 2024-08-04 00:00:00 │
│ SP-LSE  │ 58.325159 │ 7.890472 │ 2024-08-04 00:00:00 │
│ SP-LSE  │  58.32758 │ 7.884521 │ 2024-08-04 00:00:00 │
├─────────┴───────────┴──────────┴─────────────────────┤
│ 25477 rows (40 shown)                      4 columns │
└──────────────────────────────────────────────────────┘

For å få en rask oversikt over hvor mange ganger hvert fartøy er observert i den valgte sektoren, kan vi bruke følgende spørring:

SELECT r, COUNT(trace.timestamp) AS count
    FROM flights_04_08_24
    WHERE h3_cell_to_parent(trace.h3_15, 4) = '84099e9ffffffff'
    GROUP BY 1
    ORDER BY count DESC;

┌─────────┬───────┐
│    r    │ count │
│ varchar │ int64 │
├─────────┼───────┤
│ LN-ORA  │ 11722 │
│ LN-PFD  │   574 │
│ LN-WDK  │   571 │
│ SE-RPE  │   481 │
│ LN-OOX  │   471 │
│ LN-BOG  │   398 │
│ PH-EXW  │   398 │
│ SE-RUC  │   320 │
│ PH-EZX  │   280 │
│ LN-NGM  │   276 │
│ LN-OOU  │   261 │
│ A6-BNG  │   253 │
│ CS-CLA  │   251 │
│ EI-SIU  │   238 │
│ SE-RSO  │   231 │
│ OY-TCG  │   224 │
│ HA-LXI  │   218 │
│ SE-RPF  │   218 │
│ PH-EXP  │   208 │
│ ES-ATC  │   207 │
│   ·     │     · │
│   ·     │     · │
│   ·     │     · │
│ JY-BAC  │    67 │
│ LN-WDN  │    62 │
│ JY-BAB  │    61 │
│ LN-WDI  │    60 │
│ F-GRHF  │    54 │
│ OY-RCM  │    51 │
│ SE-DMR  │    50 │
│ D-AGBB  │    40 │
│ N909AD  │    33 │
│ SE-ROI  │    26 │
│ LN-NII  │    21 │
│ HB-JMD  │    20 │
│ TC-LLR  │    17 │
│ LN-YHA  │    12 │
│ SE-RGV  │    11 │
│ TF-PLA  │    11 │
│ TF-ISO  │    11 │
│ SE-RSC  │     8 │
│ OH-LKL  │     4 │
│ LN-WDM  │     1 │
├─────────┴───────┤
│     93 rows     │
│   (40 shown)    │
└─────────────────┘

Denne spørringen teller antall registreringer per fartøy i den valgte sektoren. Hvis vi ønsker mer detaljerte data, kan vi utvide spørringen for å hente ut høyde, bakkefart og posisjon:

SELECT r, trace.altitude,
  trace.ground_speed,
  ST_POINT(trace.lon, trace.lat) geom,
  trace.timestamp
FROM flights_04_08_24
WHERE h3_cell_to_parent(trace.h3_15, 4) = '84099e9ffffffff' AND r = 'LN-ORA' 
ORDER BY trace.timestamp;

Denne spørringen gir oss en detaljert tidsserie over fartøyets bevegelser i den valgte sektoren.

SELECT r, trace.altitude,
  trace.ground_speed,
  ST_POINT(trace.lon, trace.lat) geom,
  trace.timestamp
FROM flights_04_08_24
WHERE h3_cell_to_parent(trace.h3_15, 4) = '84099e9ffffffff' AND r = 'LN-ORA' AND trace.timestamp > '2024-08-04 13:11:23.710000' AND trace.timestamp < '2024-08-04 16:40:02.77000'
ORDER BY trace.timestamp;

┌─────────┬──────────┬──────────────┬────────────────────────────┬────────────────────────────┐
│    r    │ altitude │ ground_speed │            geom            │         timestamp          │
│ varchar │  int64   │    double    │          geometry          │          varchar           │
├─────────┼──────────┼──────────────┼────────────────────────────┼────────────────────────────┤
│ LN-ORA  │     2475 │         NULL │ POINT (8.176663 58.294006) │ 2024-08-04 13:11:28.130000 │
│ LN-ORA  │     2500 │        125.6 │ POINT (8.132541 58.266632) │ 2024-08-04 13:12:29.570000 │
│ LN-ORA  │     2500 │        124.8 │ POINT (8.131165 58.265805) │ 2024-08-04 13:12:31.480000 │
│ LN-ORA  │     2500 │        124.8 │ POINT (8.124212 58.261459) │ 2024-08-04 13:12:41.370000 │
│ LN-ORA  │     2500 │         NULL │ POINT (8.077332 58.232241) │ 2024-08-04 13:13:48.270000 │
│ LN-ORA  │     2500 │         NULL │ POINT (8.075958 58.231356) │ 2024-08-04 13:13:50.290000 │
│ LN-ORA  │     2500 │        123.4 │ POINT (8.075306 58.230972) │ 2024-08-04 13:13:51.170000 │
│ LN-ORA  │     2500 │        124.1 │ POINT (8.066711 58.225584) │ 2024-08-04 13:14:03.450000 │
│ LN-ORA  │     2575 │        123.3 │ POINT (8.063965 58.223969) │ 2024-08-04 13:14:07.390000 │
│ LN-ORA  │     2800 │        121.9 │ POINT (8.053986 58.218694) │ 2024-08-04 13:14:21.420000 │
│ LN-ORA  │     3275 │         96.2 │ POINT (8.043676 58.214401) │ 2024-08-04 13:14:36.330000 │
│ LN-ORA  │     3550 │         91.2 │ POINT (8.038094 58.212112) │ 2024-08-04 13:14:45.120000 │
│ LN-ORA  │     3600 │         89.0 │ POINT (8.037598 58.211898) │ 2024-08-04 13:14:46        │
│ LN-ORA  │     3625 │         87.6 │ POINT (8.03714 58.211665)  │ 2024-08-04 13:14:46.920000 │
│ LN-ORA  │     3675 │         87.6 │ POINT (8.036233 58.211334) │ 2024-08-04 13:14:48.400000 │
│ LN-ORA  │     3675 │         87.6 │ POINT (8.035967 58.211243) │ 2024-08-04 13:14:48.780000 │
│ LN-ORA  │     3725 │         84.8 │ POINT (8.035436 58.210968) │ 2024-08-04 13:14:49.760000 │
│ LN-ORA  │     3775 │         84.8 │ POINT (8.03421 58.210501)  │ 2024-08-04 13:14:51.840000 │
│ LN-ORA  │     3800 │         83.4 │ POINT (8.033664 58.210281) │ 2024-08-04 13:14:52.770000 │
│ LN-ORA  │     3825 │         82.0 │ POINT (8.033386 58.210175) │ 2024-08-04 13:14:53.150000 │
│   ·     │       ·  │           ·  │             ·              │             ·              │
│   ·     │       ·  │           ·  │             ·              │             ·              │
│   ·     │       ·  │           ·  │             ·              │             ·              │
│ LN-ORA  │     2175 │        135.5 │ POINT (8.274261 58.225025) │ 2024-08-04 15:47:33.300000 │
│ LN-ORA  │     2175 │        135.6 │ POINT (8.277401 58.22699)  │ 2024-08-04 15:47:37.230000 │
│ LN-ORA  │     2175 │        135.6 │ POINT (8.27873 58.227814)  │ 2024-08-04 15:47:39.140000 │
│ LN-ORA  │     2175 │        135.0 │ POINT (8.280059 58.228683) │ 2024-08-04 15:47:41        │
│ LN-ORA  │     2200 │        135.0 │ POINT (8.280856 58.229187) │ 2024-08-04 15:47:41.930000 │
│ LN-ORA  │     2200 │        135.0 │ POINT (8.281565 58.229645) │ 2024-08-04 15:47:42.860000 │
│ LN-ORA  │     2175 │        135.0 │ POINT (8.282185 58.230057) │ 2024-08-04 15:47:43.570000 │
│ LN-ORA  │     2175 │        134.3 │ POINT (8.282867 58.230472) │ 2024-08-04 15:47:44.720000 │
│ LN-ORA  │     2175 │        135.1 │ POINT (8.284223 58.23143)  │ 2024-08-04 15:47:46.570000 │
│ LN-ORA  │     2175 │        135.1 │ POINT (8.284666 58.231705) │ 2024-08-04 15:47:47.120000 │
│ LN-ORA  │     2175 │        135.2 │ POINT (8.285729 58.232391) │ 2024-08-04 15:47:48.590000 │
│ LN-ORA  │     2175 │        135.9 │ POINT (8.28717 58.233312)  │ 2024-08-04 15:47:50.500000 │
│ LN-ORA  │     2200 │        135.9 │ POINT (8.287944 58.233856) │ 2024-08-04 15:47:51.380000 │
│ LN-ORA  │     2175 │        135.9 │ POINT (8.288299 58.234039) │ 2024-08-04 15:47:51.980000 │
│ LN-ORA  │     2175 │        135.9 │ POINT (8.289368 58.234801) │ 2024-08-04 15:47:53.450000 │
│ LN-ORA  │     2200 │        135.9 │ POINT (8.294403 58.238106) │ 2024-08-04 15:48:00.280000 │
│ LN-ORA  │     2200 │        135.9 │ POINT (8.296007 58.23912)  │ 2024-08-04 15:48:02.300000 │
│ LN-ORA  │     2200 │        135.9 │ POINT (8.298222 58.240585) │ 2024-08-04 15:48:05.140000 │
│ LN-ORA  │     2200 │        135.9 │ POINT (8.298931 58.241089) │ 2024-08-04 15:48:06.400000 │
│ LN-ORA  │     2200 │        135.9 │ POINT (8.302031 58.243103) │ 2024-08-04 15:48:10.600000 │
├─────────┴──────────┴──────────────┴────────────────────────────┴────────────────────────────┤
│ 8076 rows (40 shown)                                                              5 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────┘

Eksport av data¶

Når vi eksporterer data fra DuckDB for videre analyse, er det viktig å velge riktig format basert på hva vi ønsker å gjøre med informasjonen. De tre mest relevante formatene i denne sammenhengen er CSV, JSON og GeoJSON, som hver har sine styrker og svakheter avhengig av bruksområdet.

CSV¶

CSV (Comma-Separated Values) er det mest tilgjengelige formatet og fungerer utmerket når vi ønsker å analysere data i regnearkprogrammer som Excel. Det er et enkelt, tekstbasert format der verdier skilles med komma, noe som gjør det lett å åpne i en rekke verktøy. I Datawrapper kan vi for eksempel laste opp en CSV-fil for å lage interaktive grafer og diagrammer, mens Everviz gir muligheter for videre visualisering. CSV er spesielt godt egnet til numeriske analyser og tabellbasert behandling, men har begrensninger når det gjelder å håndtere komplekse datastrukturer eller geografiske koordinater utover enkle lat/lon-verdier.

JSON¶

Hvis vi trenger et format som støtter mer strukturert og hierarkisk data, er JSON (JavaScript Object Notation) et bedre valg. JSON er mye brukt i nettbaserte løsninger og API-er, fordi det er lett å tolke i programmeringsspråk som Python og JavaScript. Dette formatet egner seg godt for videre databehandling og analyse i programmeringsmiljøer og gir fleksibilitet når vi skal håndtere store og sammensatte datasett. Til forskjell fra CSV kan JSON håndtere nestede datastrukturer, men det kan være vanskeligere å lese manuelt uten et spesialisert verktøy.

GeoJSON¶

Når vi jobber med geografiske data, er GeoJSON et naturlig valg. Dette formatet er en utvidelse av JSON, spesialtilpasset geospatiale data, slik at det kan lagre informasjon om punkter, linjer, polygoner og andre geografiske objekter. GeoJSON er ideelt for kartvisualiseringer og kan brukes i verktøy som GeoJSON.io for en rask visuell fremstilling eller i mer avanserte løsninger som MapTiler og GIS-programmer som QGIS. Fordi GeoJSON kan inneholde både attributter og geometriske data i samme fil, er det spesielt nyttig når vi ønsker å visualisere flytrafikk, for eksempel ved å plotte posisjonene til et spesifikt fly i løpet av en dag. Sammenlignet med CSV er GeoJSON mer detaljert, men dette fører også til større filstørrelser og mer kompleksitet.

Dataeksport i DuckDB¶

Valget av eksportformat avhenger av hva slags analyse vi ønsker å utføre. Skal vi jobbe med tall og enkle tabeller, er CSV det mest effektive. Trenger vi et fleksibelt format for videre programmering og datahåndtering, er JSON bedre egnet. Når det kommer til geografiske analyser og kartvisualiseringer, er GeoJSON uten tvil det beste valget. Uansett hvilket format vi velger, finnes det verktøy som kan hjelpe oss med å få mest mulig ut av dataene – enten det er Excel, Datawrapper, GeoJSON.io eller spesialiserte GIS-systemer.

La oss se på hvordan vi kan eksportere dataene i disse tre formatene fra DuckDB. Vi tar utgangspunkt i en (litt forenklet) versjon av vår forrige spørring, men med en liten endring: Vi bruker COPY, som rammer inn spørringen vår i parenteser.

CSV¶

COPY (
  SELECT r, trace.altitude, trace.ground_speed, trace.lon, trace.lat, trace.timestamp
  FROM flights_04_08_24
  WHERE h3_cell_to_parent(trace.h3_15, 4) = '84099e9ffffffff'
    AND r = 'LN-ORA'
    AND trace.timestamp > '2024-08-04 13:11:23.710000'
    AND trace.timestamp < '2024-08-04 16:40:02.770000'
  ORDER BY trace.timestamp
) TO 'ln-ora.csv' WITH (HEADER);

Filen ln-ora.csv dukker nå opp i mappen der du startet DuckDB, og kan åpnes i valgfritt program – for eksempel Excel eller Numbers.

Figur 1: CSV-data åpnet i Numbers på Mac.

JSON¶

La oss prøve det samme med JSON. Det er bare en ørliten justering som skal til.

COPY (
  SELECT r, trace.altitude, trace.ground_speed, trace.lon, trace.lat, trace.timestamp
  FROM flights_04_08_24
  WHERE h3_cell_to_parent(trace.h3_15, 4) = '84099e9ffffffff'
    AND r = 'LN-ORA'
    AND trace.timestamp > '2024-08-04 13:11:23.710000'
    AND trace.timestamp < '2024-08-04 16:40:02.770000'
  ORDER BY trace.timestamp
) TO 'ln-ora.json';

Når vi åpner JSON-filen i en teksteditor, ser vi at den ikke har standard JSON-format. Dette er JSONL (JSON Lines), et format velegnet for store datamengder i sekvens. JSONL er like enkelt å tolke programmatisk som standard JSON. Du kan lese mer om formatet på jsonlines.org.

GeoJSON¶

For eksport av geografiske data som punkter, linjer og polygoner bruker vi GeoJSON. For å eksportere våre geografiske punkter som en linje (LineString), benytter vi en aggregeringsfunksjon i spørringen:

COPY (
  SELECT r, ST_MakeLine(ARRAY_AGG(ST_POINT(trace.lon, trace.lat) ORDER BY trace.timestamp)) AS geom
  FROM flights_04_08_24
  WHERE h3_cell_to_parent(trace.h3_15, 4) = '84099e9ffffffff'
    AND r = 'LN-ORA'
    AND trace.timestamp > '2024-08-04 13:11:23.710000'
    AND trace.timestamp < '2024-08-04 16:40:02.770000'
  GROUP BY r
) TO 'ln-ora.geojson' WITH (FORMAT gdal, DRIVER 'GeoJSON');

Visualisering av geodata¶

Når dataene er eksportert som CSV, JSON eller GeoJSON, er det enkelt å visualisere dem i grafer eller kart. For grafer anbefaler vi det norske verktøyet everviz, som bygger på teknologien fra Highsoft – et selskap fra Vik i Sogn. Datawrapper er også et godt alternativ. Når vi lager grafer, er CSV det beste utgangsformatet.

GeoJSON.io¶

Hvis du vil visualisere data på et kart, er gratisverktøyet GeoJSON.io et godt sted å starte. La oss teste det ut:

Gå til GeoJSON.io
Klikk på Open, bla til mappen der DuckDB er startet fra, og velg en fil i GeoJSON-format fra datamaskinen din.
Filen lastes inn i grensesnittet, og du kan utforske den nærmere på kartet.

Figur 2: Data visualisert i GeoJSON.io.

Datawrapper¶

Datawrapper er et mye brukt verktøy blant datajournalister for å visualisere data i grafer og kart. Med Datawrapper kan du enkelt importere data i GeoJSON-format og legge til punkter (features) og linjer (linestrings) som et lag over kartet.

Mange norske redaksjoner har tilgang til Datawrapper. Hvis du ikke jobber i en av dem, kan du likevel teste ut funksjonaliteten gratis.

Nå har du fått en grunnleggende innføring i noen av mulighetene som ligger i DuckDB. Ved å kombinere disse funksjonene kan du utføre avanserte søk – begrenset kun av fantasien. La oss prøve noen oppgaver:

Åpne Datawrappers veiviser for kart
Velg Locator Map. Du får nå opp en firestegs prosess.
Under Add markers huker du av for Import line and area markers nederst i skjermbildet.
Klikk Import markers from file, bla til GeoJSON-filen din, og trykk OK.

Figur 3: Last opp GeoJSON-filen i Datawrapper.

Klikk Fit map view to markers for å tilpasse kartet til dataene dine.
For å endre farge på dataene, trykk på den horisontale markøren Add region as area marker.

Figur 4: Endre farge på dataene.

Klikk More options, og kryss av for Show exact line (slower loading time).
Endre eventuelt Marker name.
Når du er fornøyd, trykk Proceed.
I steget Design maps velger du kartdesign og kan aktivere målestokk, utheve en region og mer.

Figur 5: Velg kartdesign.

Når du er fornøyd, trykk Proceed.
I steg tre legger du til tittel, beskrivelse, kilde og eventuelle kommentarer.
Når du er fornøyd, trykk Proceed.

Figur 6: Legg inn tittel, beskrivelse, kilde og eventuelle kommentarer.

Nå er du nesten i mål! På neste skjermbilde klikker du på Publish now. Deretter får du en embed-kode som du kan bruke til å legge kartet inn i en artikkel.

Oppgaver¶

Sensorjournalistikk: Fra signaler til skup

Identifikasjon av luftfartøy 🏷️

Sensorjournalistikk: Fra signaler til skup

Visualisering i notebooks 🗺️