Sanntidsdata er et svært nyttig verktøy for å orientere seg om øyeblikksbildet – hva som skjer her og nå. Det er også 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? Det finnes en rekke kilder tilgjengelig, både åpne og gratis og betalte og lukkede. For flytrafikk har f.eks. Medieklyngen et stort og avansert datasett som oppdateres i sanntid som tilbys til redaksjoner og journalister.
Siden vi har begrenset tid i dag, fokuserer vi på AIS-data levert av Kysverkets HAIS – Historisk AIS. Men teknikkene vi bruker fungerer akkurat like bra på flydata, og andre store datasett.
Dataene vi skal benytte oss av, er lagret i Parquet-formatet. Dette er et spesialisert dataformat 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-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.
Grunnstrukturen er:
SELECT [kolonner]
FROM [tabell]
WHERE [betingelser];For eksempel kan en spørring se slik ut:
SELECT * FROM flights WHERE altitude > 10000;Hente historiske data¶
HAIS tilbyr flere måter å søke etter fartøy på – enten geografisk eller basert på fartøyinformasjon. Før vi starter, må vi ta stilling til:
Tidsperiode (fra–til)
Tidsoppløsning (full oppløsning, hvert femte minutt, hver time)
Dataformat (CSV eller (Geo)Parquet – velg (Geo)Parquet)
E-postadresse for mottak av data
Figur 1: Kystverket HAIS.
Når disse valgene er gjort, kan vi begynne søket.
Skipstype¶
Hvis du kjenner skipstypen du vil filtrere på, men ikke fartøyets navn, kan du bruke skipstype-feltet. Du kan filtrere på blant annet:
Dykking
Fiskebåter
Forurensningsbekjempelse
Fritidsbåt
Havnebåt
Høyhastighetsfartøy
Lasteskip
Losbåt
Medisinsk transport
Militær
Mudring/undervannsoperasjoner
Passasjerskip
Politi/lovhåndhevelse
Søk og redning (SAR)
Slepebåt
Taubåt
Tankskip
Figur 2: Søk på skipstype i HAIS.
Skip¶
Hvis du kjenner navnet på fartøyet, eller har MMSI, IMO eller kallesignal, kan du søke direkte. Skriver du inn Richard With, får du opp relevante treff. Du kan filtrere på flere fartøy samtidig eller laste opp en CSV-liste.
Figur 3: Søk på skip i HAIS.
Kartutsnitt¶
Du kan avgrense søket geografisk ved å tegne et område i kartverktøyet, eller ved å laste opp en WKT-fil med polygon. Tjenesten tilpasser automatisk området til dekningen som er tilgjengelig.
Figur 4: Søk på geografisk område.
Kjøring av søk¶
Når du er fornøyd med søkeparametrene, trykker du Send inn. Systemet validerer søket basert på geografisk område og tidsrom. Hvis du får feilmelding, har du trolig søkt for bredt – snevre inn søket i tid eller rom.
Godkjente søk blir satt i kø. Du mottar en e-post når dataene er klare. Tiden det tar, varierer etter søkets størrelse og trafikk på systemet.
Analyse av dataene¶
Etter en stund får du e-post med lenker til zip-filer som inneholder Parquet-filer. Pakk dem ut og legg dem i en lett tilgjengelig mappe.
Neste steg er å importere dataene i et verktøy som DuckDB. Dette minner om arbeidsflyten vi brukte for ADS-B-data, med noen mindre forskjeller i datastruktur og import.
Datastruktur¶
Kystverket benytter spesifikasjonen GeoParquet 1.0.0 til de historiske dataene. Her er Kystverkets egen beskrivelse datastrukturen:
| Kolonner | Meldingstype | Datatype | Beskrivelse |
|---|---|---|---|
| date_time_utc | 1,2,3,5,18,24,27 | DateTime | Tidsstempel i UTC |
| mmsi | 1,2,3,5,18,27 | Integer | Maritime Mobile Service Identity-nummer (ID på fartøyets transponder) |
| longitude | 1,2,3,18,27 | Float | Lengdegrad |
| latitude | 1,2,3,18,27 | Float | Breddegrad |
| status | 1,2,3,18,27 | Integer | Skipets navigasjonsstatus (les mer) |
| course_over_ground | 1,2,3,18,27 | Float | Fartøyets kurs over bakken i henhold til kompassnord. |
| true_heading | 1,2,3,18,27 | Integer | Den retningen fartøyet peker i henhold til kompassnord (0-359 grader) |
| speed_over_ground | 1,2,3,18,27 | Float | Fartøyets fart i knop |
| rate_of_turn | 1,2,3,18,27 | Float | Rotasjonsrate |
| maneuvre | 1,2,3,18,27 | Integer | Manøverinformasjon |
| imo | 5,24 | Integer | Skrogets IMO-nummer (satt av International Maritime Organization). |
| callsign | 5,24 | String | Radiokallesignal |
| ship_name | 5,24 | String | Skipets navn |
| ship_type | 5,24 | Integer | Skipstype fra AIS |
| length | 5,24 | Float | Lengde på skipet |
| draught | 5,24 | Float | Skipets dypgang |
| data_source | Generert kolonne | String | Datakilde (g: bakkestasjon, s: satelitt) |
| date_utc | Generert kolonne | Date | Dato-kolonne for filtrering på kun dato X |
| geometry | Generert kolonne | String | Geometri-kolonne i WKB-format generert av ST_Point(lon, lat) |
| hex_14 | Generert kolonne | Integer | H3-id for meldingens posisjon i oppløsning 14 |
Her er det mye som minner oss om det vi har sett på tidligere. Vi merker oss felter for data/tid, registreringsnummer (mmsi), posisjon, status, og flere av de aktuelle punktene vi har sett på tidligere kapitler. Dette er nyttig. Vi ser også at det er en egen kolonne for geometri, og H3-id med oppløsning 14. Dette er kjent materie for oss nå, og noe vi kan dra nytte av med det samme.
Analyse i DuckDB¶
Åpne Terminal (Mac/Linux) eller PowerShell (Windows), og naviger til mappen der du pakket ut Parquet-filene.
Start DuckDB med følgende kommando:
duckdb geoparquet-hais.db
DuckDB starter med et prompt som ser noenlunde slik ut:
v1.2.1 8e52ec4395
Enter ".help" for usage hints.
DInstaller og last inn utvidelser¶
Dersom du ikke allerede har gjort det i tidligere kapitler, installerer du nødvendige utvidelser slik:
INSTALL httpfs;
INSTALL spatial;
INSTALL h3 FROM community;Last dem deretter inn:
LOAD httpfs;
LOAD spatial;
LOAD h3;Last inn data¶
Siden filene ligger lokalt, bruker vi følgende kommando for å opprette tabellen haisdata:
CREATE TABLE haisdata AS SELECT * FROM '*.parquet';Dette leser inn alle .parquet-filer i mappen og legger dem i én tabell.
Utforske datasettet¶
Sjekk datastrukturen med:
DESCRIBE haisdata;Du vil se kolonner for tid, posisjon, kurs, fart, skipstype, navn og andre kjente parametre – inkludert geometri og H3-ID.
┌────────────────────┬───────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├────────────────────┼───────────────┼─────────┼─────────┼─────────┼─────────┤
│ date_time_utc │ TIMESTAMP │ YES │ │ │ │
│ mmsi │ INTEGER │ YES │ │ │ │
│ longitude │ DOUBLE │ YES │ │ │ │
│ latitude │ DOUBLE │ YES │ │ │ │
│ status │ TINYINT │ YES │ │ │ │
│ course_over_ground │ DOUBLE │ YES │ │ │ │
│ true_heading │ SMALLINT │ YES │ │ │ │
│ speed_over_ground │ DOUBLE │ YES │ │ │ │
│ rate_of_turn │ SMALLINT │ YES │ │ │ │
│ maneuvre │ SMALLINT │ YES │ │ │ │
│ imo │ INTEGER │ YES │ │ │ │
│ callsign │ VARCHAR │ YES │ │ │ │
│ ship_name │ VARCHAR │ YES │ │ │ │
│ ship_type │ TINYINT │ YES │ │ │ │
│ length │ SMALLINT │ YES │ │ │ │
│ draught │ DECIMAL(10,2) │ YES │ │ │ │
│ data_source │ VARCHAR │ YES │ │ │ │
│ ais_class │ VARCHAR │ YES │ │ │ │
│ hex_7 │ BIGINT │ YES │ │ │ │
│ hex_14 │ BIGINT │ YES │ │ │ │
│ geometry │ BLOB │ YES │ │ │ │
├────────────────────┴───────────────┴─────────┴─────────┴─────────┴─────────┤
│ 21 rows 6 columns │
└────────────────────────────────────────────────────────────────────────────┘Antall datapunkter¶
Tell antall rader i datasettet:
SELECT count(*) FROM haisdata;┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 236934 │
└──────────────┘Eksempelrad¶
Se ett datapunkt:
SELECT * FROM haisdata LIMIT 1;┌─────────────────────┬───────────┬────────────┬──────────┬────────┬────────────────────┬──────────────┬───────────────────┬──────────────┬──────────┬─────────┬──────────┬──────────────────────┬───────────┬────────┬───────────────┬─────────────┬───────────┬────────────────────┬────────────────────┬────────────────────────────────────────────────────────────────┐
│ date_time_utc │ mmsi │ longitude │ latitude │ status │ course_over_ground │ true_heading │ speed_over_ground │ rate_of_turn │ maneuvre │ imo │ callsign │ ship_name │ ship_type │ length │ draught │ data_source │ ais_class │ hex_7 │ hex_14 │ geometry │
│ timestamp │ int32 │ double │ double │ int8 │ double │ int16 │ double │ int16 │ int16 │ int32 │ varchar │ varchar │ int8 │ int16 │ decimal(10,2) │ varchar │ varchar │ int64 │ int64 │ blob │
├─────────────────────┼───────────┼────────────┼──────────┼────────┼────────────────────┼──────────────┼───────────────────┼──────────────┼──────────┼─────────┼──────────┼──────────────────────┼───────────┼────────┼───────────────┼─────────────┼───────────┼────────────────────┼────────────────────┼────────────────────────────────────────────────────────────────┤
│ 2025-03-12 19:51:43 │ 257600000 │ 4.02070333 │ 60.85677 │ 0 │ 182.2 │ 178 │ 13.3 │ 14 │ 0 │ 9854478 │ LAGH7 │ SYDSTRAUM │ 80 │ 129 │ 6.00 │ G │ A │ 608156020115505151 │ 639681217504175135 │ \x00\x00\x00\x00\x01@\x10\x153@\xF5\x12\xBC@Nm\xAA\xA3\xAD\x… │
└─────────────────────┴───────────┴────────────┴──────────┴────────┴────────────────────┴──────────────┴───────────────────┴──────────────┴──────────┴─────────┴──────────┴──────────────────────┴───────────┴────────┴───────────────┴─────────────┴───────────┴────────────────────┴────────────────────┴────────────────────────────────────────────────────────────────┘Antall og oversikt over fartøy¶
Finn unike fartøy i datasettet:
SELECT DISTINCT(mmsi), callsign, ship_name FROM haisdata ORDER BY ship_name;┌───────────┬──────────┬──────────────────────┐
│ mmsi │ callsign │ ship_name │
│ int32 │ varchar │ varchar │
├───────────┼──────────┼──────────────────────┤
│ 235076245 │ 2CVX9 │ EDZARD SCHULTE │
│ 247302900 │ ICPE │ AIDASOL │
│ 244120000 │ PCYH │ ANTEOS │
│ 244120000 │ P C Y H │ ANTEOS │
│ 636020363 │ D5YW3 │ AQUASMERALDA │
│ 636023947 │ 5LQF3 │ ASPEN │
│ 246598000 │ PBTQ │ BEATRIX │
│ 209356000 │ 5BBK5 │ BORIS DAVYDOV │
│ 255806224 │ CQAN6 │ BOTHNIA │
│ 257619000 │ LFGV │ CAPRICE │
│ 255805753 │ CQCC │ CHRISTIAN ESSBERGER │
│ 215349000 │ 9HA2707 │ CSL TRIMNES │
│ 311023800 │ C6XW3 │ DEEP ARCTIC │
│ 310767000 │ ZCEC8 │ DEEPSEA STAVANGER │
│ 257089140 │ LAIX8 │ EAGLE BLANE │
│ 538006249 │ V7LA9 │ ECO ROYALTY │
│ 259665000 │ JWMZ3 │ EDDA FAUNA │
│ 231700000 │ OZ2077 │ ELDBORG │
│ 305530000 │ V2ER8 │ FITNES │
│ 220151000 │ OWQD │ FRIDA AMALIE │
│ · │ · │ · │
│ · │ · │ · │
│ · │ · │ · │
│ 257297000 │ LLUO │ REM STAR │
│ 310805000 │ ZCEZ8 │ RENAISSANCE │
│ 311000627 │ C6DB3 │ RUDOLF SAMOYLOVICH │
│ 231850000 │ OZ2076 │ SAEBORG │
│ 232419000 │ MZHR7 │ SEVEN NAVICA │
│ 257286000 │ LDZH │ SIEM PEARL │
│ 209190000 │ 5BXG4 │ SIF W │
│ 253309000 │ LXUB │ SIMON STEVIN │
│ 259888000 │ LAHA7 │ STAVFJORD │
│ 538010467 │ V7A6081 │ STI MYSTERY │
│ 538006343 │ V7MB8 │ STI SPIGA │
│ 246695000 │ PCKX │ STORNES │
│ 258527000 │ JWRE │ STRIL MAR │
│ 257129000 │ LGLA │ STRIL MERKUR │
│ 257600000 │ LAGH7 │ SYDSTRAUM │
│ 241456000 │ SVCL8 │ THOMAS ZAFIRAS │
│ 258390000 │ LLVY │ VIKING ENERGY │
│ 257271000 │ LACZ8 │ VOLANTIS │
│ 257970000 │ LAUR5 │ XANTHIA │
│ 311000634 │ C6DC2 │ YAKOV GAKKEL │
├───────────┴──────────┴──────────────────────┤
│ 75 rows (40 shown) 3 columns │
└─────────────────────────────────────────────┘Se hvor mange datapunkter hvert fartøy har:
SELECT mmsi, callsign, ship_name, COUNT(*) AS count
FROM haisdata
GROUP BY ALL
ORDER BY count DESC;┌───────────┬──────────┬──────────────────────┬───────┐
│ mmsi │ callsign │ ship_name │ count │
│ int32 │ varchar │ varchar │ int64 │
├───────────┼──────────┼──────────────────────┼───────┤
│ 258390000 │ LLVY │ VIKING ENERGY │ 24621 │
│ 257129000 │ LGLA │ STRIL MERKUR │ 17671 │
│ 257105000 │ LMEL │ G.O.SARS │ 16976 │
│ 257271000 │ LACZ8 │ VOLANTIS │ 16387 │
│ 231700000 │ OZ2077 │ ELDBORG │ 11514 │
│ 636023947 │ 5LQF3 │ ASPEN │ 9821 │
│ 220151000 │ OWQD │ FRIDA AMALIE │ 9787 │
│ 538006343 │ V7MB8 │ STI SPIGA │ 7860 │
│ 259014300 │ JXQO │ REBEKKA L │ 6115 │
│ 258906000 │ LDLA │ JUANITA │ 5990 │
│ 259665000 │ JWMZ3 │ EDDA FAUNA │ 5765 │
│ 231099000 │ OZ2088 │ HAV NES │ 5487 │
│ 636020363 │ D5YW3 │ AQUASMERALDA │ 5288 │
│ 219597000 │ OZOI │ L56 ANNA LISE │ 4758 │
│ 257438000 │ LCCC │ HAVILA HEROY │ 4239 │
│ 257600000 │ LAGH7 │ SYDSTRAUM │ 3430 │
│ 245974000 │ PARE │ NATO WARSHIP A900 │ 3422 │
│ 231850000 │ OZ2076 │ SAEBORG │ 3396 │
│ 258895000 │ LDAD │ NORTH POMOR │ 3378 │
│ 257089140 │ LAIX8 │ EAGLE BLANE │ 2968 │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ 310767000 │ ZCEC8 │ DEEPSEA STAVANGER │ 961 │
│ 258277000 │ LAVS7 │ HANNE KNUTSEN │ 934 │
│ 538010467 │ V7A6081 │ STI MYSTERY │ 898 │
│ 230688000 │ OJTF │ JATULI │ 890 │
│ 258152000 │ LAWE7 │ NORDSTRAUM │ 846 │
│ 255806224 │ CQAN6 │ BOTHNIA │ 768 │
│ 257297000 │ LLUO │ REM STAR │ 768 │
│ 311023800 │ C6XW3 │ DEEP ARCTIC │ 684 │
│ 538006249 │ V7LA9 │ ECO ROYALTY │ 629 │
│ 248221000 │ 9HA2267 │ KEY SOUTH │ 628 │
│ 266235000 │ SJLF │ FURE WEST │ 619 │
│ 205771000 │ ONJY │ JOAN │ 541 │
│ 310805000 │ ZCEZ8 │ RENAISSANCE │ 428 │
│ 259827000 │ LGPE │ NORMAND SIGMA │ 407 │
│ 211210150 │ DRAA │ GERMAN WARSHIP F219 │ 166 │
│ 636092960 │ D5XR2 │ GW ELENI │ 162 │
│ 257286000 │ LDZH │ SIEM PEARL │ 38 │
│ 257619000 │ LFGV │ CAPRICE │ 36 │
│ 236111791 │ ZDKS2 │ KEY BREEZE │ 13 │
│ 253309000 │ LXUB │ SIMON STEVIN │ 8 │
├───────────┴──────────┴──────────────────────┴───────┤
│ 75 rows (40 shown) 4 columns │
└─────────────────────────────────────────────────────┘
Søk i geografisk område¶
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. Men Kystverket har vært så elskverdige at de allerede har gjort denne jobben for oss. Det betyr at vi enkelt kan søke etter trafikk i en gitt H3-celle. Merk at H3-ID-ene i HAIS lagres som tallverdier, så vi må konvertere dem til heksadesimal. Det gode nyheten er at vi kan gjøre det direkte i spørringen.
Den observante deltaker merket seg kanskje at vi installerte og lastet inn H3-utvidelsen i starten av dette kapittelet med følgende kommandoer:
INSTALL h3 FROM community;
LOAD h3;Siden dette tillegget allerede er 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(ettertrace.h3_15), mens heksagon-ID-en settes til slutt på samme linje.
HAIS støtter H3-rammeverket, slik vi kjenner det fra tidligere kapitler. Det betyr at vi enkelt kan søke etter trafikk i en gitt H3-celle. Merk at H3-ID-ene i HAIS lagres som tallverdier, så vi må konvertere dem til heksadesimal.
Finn ønsket H3-celle ved hjelp av et verktøy som H3 Viewer.
Bruk for eksempel
8409851ffffffffmed oppløsning4. (Pass på å velge et område som er tilgjengelig i datasettet. Bruker du eksempelfilen fra tidligere, er den nevnte IDen et godt utgangspunkt.)Kjør denne spørringen i DuckDB:
SELECT *
FROM haisdata
WHERE h3_cell_to_parent(to_hex(hex_14), 4) = '8409851ffffffff';Du vil da få opp alle datapunkter som befinner seg innenfor den gitte H3-cellen:
┌─────────────────────┬───────────┬────────────┬─────────────┬────────┬────────────────────┬──────────────┬───────────────────┬──────────────┬──────────┬─────────┬──────────┬──────────────────────┬───────────┬────────┬───────────────┬─────────────┬───────────┬────────────────────┬────────────────────┬─────────────────────────────────────────────────────────────┐
│ date_time_utc │ mmsi │ longitude │ latitude │ status │ course_over_ground │ true_heading │ speed_over_ground │ rate_of_turn │ maneuvre │ imo │ callsign │ ship_name │ ship_type │ length │ draught │ data_source │ ais_class │ hex_7 │ hex_14 │ geometry │
│ timestamp │ int32 │ double │ double │ int8 │ double │ int16 │ double │ int16 │ int16 │ int32 │ varchar │ varchar │ int8 │ int16 │ decimal(10,2) │ varchar │ varchar │ int64 │ int64 │ blob │
├─────────────────────┼───────────┼────────────┼─────────────┼────────┼────────────────────┼──────────────┼───────────────────┼──────────────┼──────────┼─────────┼──────────┼──────────────────────┼───────────┼────────┼───────────────┼─────────────┼───────────┼────────────────────┼────────────────────┼─────────────────────────────────────────────────────────────┤
│ 2025-03-12 23:59:57 │ 636020363 │ 3.99394833 │ 60.451285 │ 0 │ 193.8 │ 266 │ 2.1 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758256391 │ \x00\x00\x00\x00\x01@\x0F\xF3\x9B.\x9A\x1CK@N9\xC3\xB4\xF… │
│ 2025-03-12 23:59:47 │ 636020363 │ 3.99399666 │ 60.45135666 │ 0 │ 195.8 │ 266 │ 2.0 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758258871 │ \x00\x00\x00\x00\x01@\x0F\xF3\xB4\x85XD\x0F@N9\xC6\x0E\x1… │
│ 2025-03-12 23:59:38 │ 636020363 │ 3.994055 │ 60.45143333 │ 0 │ 194.9 │ 267 │ 1.9 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758258927 │ \x00\x00\x00\x00\x01@\x0F\xF3\xD3\x1B\x9Bf\xF9@N9\xC8\x91… │
│ 2025-03-12 23:59:27 │ 636020363 │ 3.9941 │ 60.45150833 │ 0 │ 195.1 │ 267 │ 2.1 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758234911 │ \x00\x00\x00\x00\x01@\x0F\xF3\xEA\xB3g\xA0\xF9@N9\xCB\x06… │
│ 2025-03-12 23:59:25 │ 636020363 │ 3.99410333 │ 60.45151666 │ 0 │ 195.9 │ 267 │ 2.0 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758234663 │ \x00\x00\x00\x00\x01@\x0F\xF3\xECrY\x8E\xBD@N9\xCBLD&K │
│ 2025-03-12 23:59:23 │ 636020363 │ 3.99412333 │ 60.451565 │ 0 │ 201.1 │ 268 │ 1.3 │ -127 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758234975 │ \x00\x00\x00\x00\x01@\x0F\xF3\xF6\xEE\xB4S.@N9\xCC\xE1\xC… │
│ 2025-03-12 23:59:18 │ 636020363 │ 3.99415166 │ 60.45158166 │ 0 │ 200.1 │ 269 │ 1.9 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758233751 │ \x00\x00\x00\x00\x01@\x0F\xF4\x05\xC9\x17\xB6\x80@N9\xCDm… │
│ 2025-03-12 23:59:14 │ 636020363 │ 3.99418 │ 60.45161666 │ 0 │ 194.0 │ 269 │ 1.9 │ -127 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758233743 │ \x00\x00\x00\x00\x01@\x0F\xF4\x14\xA4\xD2\xB2\xC0@N9\xCE\… │
│ 2025-03-12 23:59:13 │ 636020363 │ 3.994185 │ 60.45163166 │ 0 │ 196.2 │ 269 │ 1.6 │ -127 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758233815 │ \x00\x00\x00\x00\x01@\x0F\xF4\x17C\xE9c\xDC@N9\xCF\x10\xF… │
│ 2025-03-12 23:59:08 │ 636020363 │ 3.99420833 │ 60.45165666 │ 0 │ 200.5 │ 270 │ 1.8 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758233823 │ \x00\x00\x00\x00\x01@\x0F\xF4#\x7F6\x16\x12@N9\xCF\xE2\xA… │
│ 2025-03-12 23:59:05 │ 636020363 │ 3.99423166 │ 60.45167166 │ 0 │ 194.7 │ 270 │ 2.0 │ -127 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758231911 │ \x00\x00\x00\x00\x01@\x0F\xF4/\xBA\x82\xC8G@N9\xD0`\x80\x… │
│ 2025-03-12 23:59:04 │ 636020363 │ 3.994235 │ 60.451685 │ 0 │ 194.7 │ 271 │ 2.0 │ -127 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758234519 │ \x00\x00\x00\x00\x01@\x0F\xF41z\xCCN\xF9@N9\xD0\xD0g\x8C\… │
│ 2025-03-12 23:58:58 │ 636020363 │ 3.994265 │ 60.45174333 │ 0 │ 195.9 │ 271 │ 1.9 │ -127 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758234119 │ \x00\x00\x00\x00\x01@\x0F\xF4A5Tu\xA3@N9\xD2\xB9\xB6D\xA0 │
│ 2025-03-12 23:58:56 │ 636020363 │ 3.99427833 │ 60.45174333 │ 0 │ 193.8 │ 271 │ 2.1 │ -127 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758234119 │ \x00\x00\x00\x00\x01@\x0F\xF4H2s\xC5\xA0@N9\xD2\xB9\xB6D\… │
│ 2025-03-12 23:58:47 │ 636020363 │ 3.994325 │ 60.45182 │ 0 │ 196.8 │ 272 │ 2.0 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758234191 │ \x00\x00\x00\x00\x01@\x0F\xF4`\xAAd\xC2\xF8@N9\xD5<\xDD\x… │
│ 2025-03-12 23:58:38 │ 636020363 │ 3.99437666 │ 60.451905 │ 0 │ 200.8 │ 273 │ 1.7 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758239087 │ \x00\x00\x00\x00\x01@\x0F\xF4{\xC0\x14\xD8\x7F@N9\xD8\x05… │
│ 2025-03-12 23:58:27 │ 636020363 │ 3.99444166 │ 60.45198666 │ 0 │ 204.8 │ 273 │ 2.0 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758237927 │ \x00\x00\x00\x00\x01@\x0F\xF4\x9D\xD4;\xD6\xF1@N9\xDA\xB2… │
│ 2025-03-12 23:58:18 │ 636020363 │ 3.994515 │ 60.45207333 │ 0 │ 207.1 │ 273 │ 1.7 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758238495 │ \x00\x00\x00\x00\x01@\x0F\xF4\xC4G\xC3\x0D0@N9\xDD\x89\xF… │
│ 2025-03-12 23:58:08 │ 636020363 │ 3.99458 │ 60.45214833 │ 0 │ 207.2 │ 273 │ 1.7 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619757651607 │ \x00\x00\x00\x00\x01@\x0F\xF4\xE6[\xEA\x0B\xA2@N9\xDF\xFF… │
│ 2025-03-12 23:57:58 │ 636020363 │ 3.99466166 │ 60.452235 │ 0 │ 205.4 │ 273 │ 1.8 │ 0 │ 0 │ 9884801 │ D5YW3 │ AQUASMERALDA │ 80 │ 182 │ 8.40 │ G │ A │ 608153422381711359 │ 639678619758284135 │ \x00\x00\x00\x00\x01@\x0F\xF5\x11,\x22G\xD3@N9\xE2\xD6#\x… │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 2025-03-13 19:16:16 │ 219023236 │ 3.751666 │ 60.536666 │ 7 │ 97.0 │ │ 2.0 │ │ │ 9807786 │ OXAB │ MYGGENES │ 30 │ 33 │ 0.00 │ S │ LRIT │ 608153424898293759 │ 639678622280122647 │ \x00\x00\x00\x00\x01@\x0E\x03iv\xBC\x1F\x00@ND\xB1x\xB3p\… │
│ 2025-03-13 19:16:14 │ 219023236 │ 3.751666 │ 60.536666 │ 7 │ 97.0 │ │ 2.0 │ │ │ 9807786 │ OXAB │ MYGGENES │ 30 │ 33 │ 0.00 │ S │ LRIT │ 608153424898293759 │ 639678622280122647 │ \x00\x00\x00\x00\x01@\x0E\x03iv\xBC\x1F\x00@ND\xB1x\xB3p\… │
│ 2025-03-13 00:08:05 │ 209356000 │ 3.958333 │ 60.433333 │ 0 │ 174.0 │ │ 13.0 │ │ │ 9768394 │ 5BBK5 │ BORIS DAVYDOV │ 84 │ 299 │ 12.10 │ S │ LRIT │ 608153422868250623 │ 639678620250946615 │ \x00\x00\x00\x00\x01@\x0F\xAA\xAA}\xEDk\xA9@N7wt\xAB\xA3\… │
│ 2025-03-13 00:05:06 │ 209356000 │ 3.956666 │ 60.445 │ 0 │ 173.0 │ │ 13.0 │ │ │ 9768394 │ 5BBK5 │ BORIS DAVYDOV │ 84 │ 299 │ 12.10 │ S │ LRIT │ 608153422868250623 │ 639678620245629839 │ \x00\x00\x00\x00\x01@\x0F\xA7@\x80\xF9\x8F\xA3@N8\xF5\xC2… │
│ 2025-03-13 00:05:06 │ 209356000 │ 3.956666 │ 60.445 │ 0 │ 173.0 │ │ 13.0 │ │ │ 9768394 │ 5BBK5 │ BORIS DAVYDOV │ 84 │ 299 │ 12.10 │ S │ LRIT │ 608153422868250623 │ 639678620245629839 │ \x00\x00\x00\x00\x01@\x0F\xA7@\x80\xF9\x8F\xA3@N8\xF5\xC2… │
│ 2025-03-13 23:59:20 │ 219022165 │ 3.81623666 │ 60.42955666 │ 7 │ 212.6 │ 326 │ 0.2 │ -127 │ 0 │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ A │ 608153419546361855 │ 639678616932387447 │ \x00\x00\x00\x00\x01@\x0E\x87\xA7\x16\x03\xF8]@N6\xFB\xB6… │
│ 2025-03-13 22:25:48 │ 219022165 │ 3.83790833 │ 60.37293666 │ 7 │ 356.9 │ 354 │ 2.6 │ 0 │ 0 │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ A │ 608153421962280959 │ 639678619351091791 │ \x00\x00\x00\x00\x01@\x0E\xB4\x09HS*\xF5@N/\xBCcs\x16\xF9 │
│ 2025-03-13 22:25:48 │ 219022165 │ 3.83790833 │ 60.37293666 │ 7 │ 356.9 │ 354 │ 2.6 │ 0 │ 0 │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ A │ 608153421962280959 │ 639678619351091791 │ \x00\x00\x00\x00\x01@\x0E\xB4\x09HS*\xF5@N/\xBCcs\x16\xF9 │
│ 2025-03-13 23:57:29 │ 219022165 │ 3.815 │ 60.428333 │ 7 │ 330.0 │ │ 0.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153419546361855 │ 639678616922679031 │ \x00\x00\x00\x00\x01@\x0E\x85\x1E\xB8Q\xEB\x85@N6\xD3\x9D… │
│ 2025-03-13 23:57:28 │ 219022165 │ 3.815 │ 60.428333 │ 7 │ 330.0 │ │ 0.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153419546361855 │ 639678616922679031 │ \x00\x00\x00\x00\x01@\x0E\x85\x1E\xB8Q\xEB\x85@N6\xD3\x9D… │
│ 2025-03-13 23:54:29 │ 219022165 │ 3.816666 │ 60.428333 │ 7 │ 318.0 │ │ 0.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153419546361855 │ 639678616922525039 │ \x00\x00\x00\x00\x01@\x0E\x88\x88/\x0E\x0A\x85@N6\xD3\x9D… │
│ 2025-03-13 22:27:28 │ 219022165 │ 3.836666 │ 60.373333 │ 7 │ 359.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153421962280959 │ 639678619351051575 │ \x00\x00\x00\x00\x01@\x0E\xB1}\xF1\x9Df\xAE@N/\xC9`0\xC2@ │
│ 2025-03-13 22:24:27 │ 219022165 │ 3.836666 │ 60.371666 │ 7 │ 355.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153421962280959 │ 639678619351067943 │ \x00\x00\x00\x00\x01@\x0E\xB1}\xF1\x9Df\xAE@N/\x92\xC0a\x… │
│ 2025-03-13 21:06:28 │ 219022165 │ 3.851666 │ 60.315 │ 7 │ 340.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153422046167039 │ 639678619429798647 │ \x00\x00\x00\x00\x01@\x0E\xD06C\x88\xEB\xCC@N(Q\xEB\x85\x… │
│ 2025-03-13 21:03:27 │ 219022165 │ 3.853333 │ 60.311666 │ 7 │ 339.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153422046167039 │ 639678619430290455 │ \x00\x00\x00\x00\x01@\x0E\xD3\xA0@|\xC7\xD2@N\x27\xE4\xAB… │
│ 2025-03-13 21:03:27 │ 219022165 │ 3.853333 │ 60.311666 │ 7 │ 339.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153422046167039 │ 639678619430290455 │ \x00\x00\x00\x00\x01@\x0E\xD3\xA0@|\xC7\xD2@N\x27\xE4\xAB… │
│ 2025-03-13 20:54:27 │ 219022165 │ 3.856666 │ 60.306666 │ 7 │ 345.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153422046167039 │ 639678619433830087 │ \x00\x00\x00\x00\x01@\x0E\xDAs\xB4,\xC2\xD7@N\x27@\xD4\xD… │
│ 2025-03-13 20:54:27 │ 219022165 │ 3.856666 │ 60.306666 │ 7 │ 345.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153422046167039 │ 639678619433830087 │ \x00\x00\x00\x00\x01@\x0E\xDAs\xB4,\xC2\xD7@N\x27@\xD4\xD… │
│ 2025-03-13 20:54:27 │ 219022165 │ 3.856666 │ 60.306666 │ 7 │ 345.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153422046167039 │ 639678619433830087 │ \x00\x00\x00\x00\x01@\x0E\xDAs\xB4,\xC2\xD7@N\x27@\xD4\xD… │
│ 2025-03-13 20:51:28 │ 219022165 │ 3.858333 │ 60.303333 │ 7 │ 345.0 │ │ 2.0 │ │ │ 0 │ OWBS │ HM228 PONDUS │ 30 │ 28 │ 5.00 │ S │ LRIT │ 608153422046167039 │ 639678619434559663 │ \x00\x00\x00\x00\x01@\x0E\xDD\xDD\xB1 \x9E\xDC@N&\xD3\x9D… │
├─────────────────────┴───────────┴────────────┴─────────────┴────────┴────────────────────┴──────────────┴───────────────────┴──────────────┴──────────┴─────────┴──────────┴──────────────────────┴───────────┴────────┴───────────────┴─────────────┴───────────┴────────────────────┴────────────────────┴─────────────────────────────────────────────────────────────┤
│ 28451 rows (40 shown) 21 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT * FROM haisdata WHERE h3_cell_to_parent(to_hex(hex_14), 4) LIKE '8409851ffffffff';
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.
Hvilket format skal jeg velge?¶
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 spørring som filtrerer ut loggen til et spesifikt fartøy – Juanita (MMSI: 258906000). Deretter bruker vi COPY, som rammer inn spørringen vår i parenteser.
CSV-eksempel¶
COPY (
SELECT date_time_utc, mmsi, ship_name, longitude, latitude, speed_over_ground, true_heading
FROM haisdata
WHERE mmsi = '258906000'
ORDER BY date_time_utc
) TO 'juanita.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 5: CSV-data åpnet i Numbers på Mac.
JSON-eksempel¶
La oss prøve det samme med JSON. Det er bare en ørliten justering som skal til.
COPY (
SELECT mmsi, ship_name, ST_MakeLine(ARRAY_AGG(ST_POINT(longitude, latitude) ORDER BY date_time_utc)) AS geom
FROM haisdata
WHERE mmsi = '258906000'
GROUP BY mmsi, ship_name
) TO 'juanita.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-eksempel¶
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 ship_name, ST_MakeLine(ARRAY_AGG(ST_POINT(longitude, latitude) ORDER BY date_time_utc)) AS geom
FROM haisdata
WHERE mmsi = '258906000'
GROUP BY ship_name
) TO 'juanita.geojson' WITH (FORMAT gdal, DRIVER 'GeoJSON');Åpne filen i GeoJSON.io for å se skipets rute visualisert på kart.
Bruk av DuckDBs webgrensesnitt¶
DuckDB har nylig lansert et webgrensesnitt, som lar oss bygge og kjøre spørringer i nettleseren. Dette gjør det enklere å mer intuitivt og jobbe med spørringer, og man kan også lagre dem i notebooks. Du synes kanskje det er rart at jeg forteller om dette nå, og ikke til å begynne med, men det er av en grunn: Det er lettere å mestre webgrensesnittet når man har prøvd kommandolinjen først, enn omvendt. Og grensesnittet er ganske nytt, og ikke fullstendig stabilt enda. Så bruk den arbeidsflyten som passer best for deg. Her kan du også plukke og mikse.
Hvis DuckDB allerede kjører i terminalen din, lukker du det ved å skrive
.exitÅpne Terminal (Mac/Linux) eller PowerShell (Windows), og naviger til mappen der du pakket ut Parquet-filene.
Start DuckDB med følgende kommando:
duckdb geoparquet-hais.db --ui
Nå vil det åpnes en ny fane i nettleseren din, og du vil kunne opprette notebooks der du kan kjøre spørringene akkurat slik vi har gjort i terminalen frem til nå. Når du har åpnet DuckDB på denne måten, kan du faktisk bruke webgrensesnittet og terminalen side om side.