STRM/STRC

STRC: A coded version of STR

Standardisation in progress

University of Groningen and the TRESOAR archive have standardised ports of departure, destination and shipmasters home port. By end of 2015 about 50.000 spelling variations of port names have been coded into a 6 letter alfanumeric code.

Standardisation using SQL

Loading main DATA

Passages

If passages is downloaded from STRO using the query interface a CSV-file called passages_Year_=_YYYY.csv (e.g. YYYY=1754) will be created. The file consist of the selected passsages, first line includes field names, each field is seperated with comma and values are qouted using dobble-qoute ". Exported data is encoded in "Latin1". By mailing the STRO-group it is also possible to get larger chucks of data, however it is encoded in UTF-8, lacks field names and each field are seperated by semi-colon. The field name of all data is doorvaarten.csv. Therefore two import routines have been made, one for each of the supplied data format. Please note that the table definition are the same.

Import the complete data of passages

-- Table structure for table 'doorvaarten'
--
-- 
DROP TABLE IF EXISTS "doorvaart";

CREATE TABLE "doorvaart" (
  "id_doorvaart" INTEGER PRIMARY KEY NOT NULL,
  "dag"  INTEGER,
  "maand"  INTEGER,
  "jaar"  INTEGER,
  "volgnummer"  VARCHAR(255),
  "schipper_voornamen"  VARCHAR(255),
  "schipper_patroniem"  VARCHAR(255),
  "schipper_tussenvoegsel"  VARCHAR(255),
  "schipper_achternaam"  VARCHAR(255),
  "schipper_plaatsnaam"  VARCHAR(255),
  "soort_korting"  VARCHAR(255),
  "korting_daalder"  VARCHAR(255),
  "korting_skilling"  VARCHAR(255),
  "subtotaal1_daalder"  VARCHAR(255),
  "subtotaal1_skilling"  VARCHAR(255),
  "subtotaal2_daalder"  VARCHAR(255),
  "subtotaal2_skilling"  VARCHAR(255),
  "totaal_rosenobel"  VARCHAR(255),
  "totaal_daalder"  VARCHAR(255),
  "totaal_skilling"  VARCHAR(255),
  "andere_muntsoort1"  VARCHAR(255),
  "andere_muntsoort1_totaal"  VARCHAR(255),
  "andere_muntsoort2"  VARCHAR(255),
  "andere_muntsoort2_totaal"  VARCHAR(255),
  "privilege"  VARCHAR(255),
  "opmerking_bron"  VARCHAR(500),
  "opmerking_invoerder"  VARCHAR(255),
  "opmerking_controle"  VARCHAR(255),
  "controle_1"  VARCHAR(255),
  "controle_2"  VARCHAR(255),
  "controle_3"  VARCHAR(255),
  "naam_invoerder"  VARCHAR(255),
  "datum_opgevoerd"  VARCHAR(255),
  "dirnummer"  VARCHAR(255),
  "tonnage"  VARCHAR(255),
  "belasting_korting"  VARCHAR(255),
  "images"  VARCHAR(255),
  "hulp"  VARCHAR(255),
  "hulp1"  VARCHAR(255),
  "korting_ort"  VARCHAR(255),
  "subtotaal1_ort"  VARCHAR(255),
  "subtotaal2_ort"  VARCHAR(255),
  "totaal_ort"  VARCHAR(255),
  "hulp2" VARCHAR(255)
);

COPY doorvaart FROM '/home/arne/STRM/data/doorvaarten.csv' DELIMITER ',' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';

Import selected and downloaded passages

The following code in Postgresql (SQL of the DBMS Postgres) imports the downloaded data into a table (relation) called doorvart. The complete code for importing all three tables, passages, cargoes and taxes can be downloaded here.

-- Table structure for table 'doorvaart'
--
--  "id_doorvaart";"dag";"maand";"jaar";"volgnummer";"schipper_voornamen";"schipper_patroniem";"schipper_tussenvoegsel";"schipper_achternaam";"schipper_plaatsnaam";"soort_korting";"korting_daalder";"korting_skilling";"subtotaal1_daalder";"subtotaal1_skilling";"subtotaal2_daalder";"subtotaal2_skilling";"totaal_rosenobel";"totaal_daalder";"totaal_skilling";"andere_muntsoort1";"andere_muntsoort1_totaal";"andere_muntsoort2";"andere_muntsoort2_totaal";"privilege";"opmerking_bron";"opmerking_invoerder";"opmerking_controle";"controle_1";"controle_2";"controle_3";"naam_invoerder";"datum_opgevoerd";"dirnummer";"tonnage";"belasting_korting";"images";"hulp";"hulp1";"korting_ort";"subtotaal1_ort";"subtotaal2_ort";"totaal_ort";"hulp2"
-- "410987";"29";"6";"1754";"373";"Gerrit";"";"";"Hendrichs";"Workum";"";"";"";"";"";"";"";"";"2";"";"";"";"";"";"";"";"";"";"J";"N";"N";"ERICB";"31-03-10";"226";"";"";"";"";"";"";"";"";"";""

DROP TABLE IF EXISTS "doorvaart";

CREATE TABLE "doorvaart" (
  "id_doorvaart" INTEGER PRIMARY KEY NOT NULL,
  "dag"  INTEGER,
  "maand"  INTEGER,
  "jaar"  INTEGER,
  "volgnummer"  VARCHAR(255),
  "schipper_voornamen"  VARCHAR(255),
  "schipper_patroniem"  VARCHAR(255),
  "schipper_tussenvoegsel"  VARCHAR(255),
  "schipper_achternaam"  VARCHAR(255),
  "schipper_plaatsnaam"  VARCHAR(255),
  "soort_korting"  VARCHAR(255),
  "korting_daalder"  VARCHAR(255),
  "korting_skilling"  VARCHAR(255),
  "subtotaal1_daalder"  VARCHAR(255),
  "subtotaal1_skilling"  VARCHAR(255),
  "subtotaal2_daalder"  VARCHAR(255),
  "subtotaal2_skilling"  VARCHAR(255),
  "totaal_rosenobel"  VARCHAR(255),
  "totaal_daalder"  VARCHAR(255),
  "totaal_skilling"  VARCHAR(255),
  "andere_muntsoort1"  VARCHAR(255),
  "andere_muntsoort1_totaal"  VARCHAR(255),
  "andere_muntsoort2"  VARCHAR(255),
  "andere_muntsoort2_totaal"  VARCHAR(255),
  "privilege"  VARCHAR(255),
  "opmerking_bron"  VARCHAR(255),
  "opmerking_invoerder"  VARCHAR(255),
  "opmerking_controle"  VARCHAR(255),
  "controle_1"  VARCHAR(255),
  "controle_2"  VARCHAR(255),
  "controle_3"  VARCHAR(255),
  "naam_invoerder"  VARCHAR(255),
  "datum_opgevoerd"  VARCHAR(255),
  "dirnummer"  VARCHAR(255),
  "tonnage"  VARCHAR(255),
  "belasting_korting"  VARCHAR(255),
  "images"  VARCHAR(255),
  "hulp"  VARCHAR(255),
  "hulp1"  VARCHAR(255),
  "korting_ort"  VARCHAR(255),
  "subtotaal1_ort"  VARCHAR(255),
  "subtotaal2_ort"  VARCHAR(255),
  "totaal_ort"  VARCHAR(255),
  "hulp2" VARCHAR(255)
);

COPY doorvaart FROM '/home/arne/STRM/data/passages_Year_=_1754.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'latin1' NULL '';

Loading aux DATA

In order to manage

Classification systems by Nina Bang

• Nina E. Bangs classification of commodities converted into a database table.
  • Download the classification system as XLSX. Download as DBF.
  • Download the list of products as XLS. Download as DBF.
• Nina E. Bangs coding of ports (departure, port of intended destination, shipmaster home port)
  • Converted into a database table. Download as XLS. Download as DBF.
  • Download the list of ports as geocoded GIS file (ArcGIS). Download as DBF