Versions Compared

Old Version 2

changes.mady.by.user Peter Konopatzky

Saved on

compared with

New Version 3

changes.mady.by.user Peter Konopatzky

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: minor update in section on join files: only geometry col excluded

...

A CSVT file, consisting of exactly one line with column types for exactly all columns specified in the _layer_def.csv file, seperated by commas (,). Supported column types are the following

Column TypeExplanationComment
Integer(Boolean)booleanValues need to be 0 (meaning false) or 1 (meaning true).
Integernatural number
Realdecimal numeral
Stringstring/textCould contain lists/dictionaries formatted as strings (see examples)
DateTimetimestamp (UTC)
WKTgeometry in WKT format

Basic Data Files

Not every O2A GeoCSV file needs to contain all columns (except for those mandatory) of the target layer (as described in the Layer Definition Files). All unmentioned columns will hold null values for all rows of O2A GeoCSV data files not containing them.

Column Name Conventions

column
type/group		column
header		column
data type		column+value
mandatory?		descriptionexample values
space/time
metadata

date_time_start

DateTime

yesDate and time of data point in ISO 8601 format notation, using UTC time zone, Or start of time range.

valid:
2019-02-28T15:50:00
valid:    2019-02-28T15:50:00+00
valid: 2019-02-28T15:50:00.000+00

invalid: 2019-02-28 15:50:00

 

date_time_end

DateTime


End of time range of measurement(s) in ISO 8601 format notation, using UTC time zone.

see above
elevation

Real


Elevation in meter. A negative value means below sea level, while positive value means above sea level. See Pangaea Geocode definition.

Note: This is not the height/depth of the measurement/observation (unless it's taken on earth's surface) but the topographical elevation at the lon/lat position.

valid: 200.1
valid: -200

invalid: -200m

z_value

Real


Vertical position of the measurement, in meter (third spatial dimension).
z_type

String

yes,
if z_value
is given		Pangaea Geocode to describe the type of z_value.

valid: "DEPTH, water"
valid: "DEPTH, sediment/rock"
valid: "HEIGHT above ground"

invalid: "HEIGHT above aeroplane"

geometry

WKT

yes

Geometry in WKT notation without third/vertical spatial dimension. The reference system needs to be EPSG:4326 and the unit decimal degrees. Longitude first, latitude second. The geometry type can be chosen freely. However, a simple POINT is usually the best choice.

valid: POINT (123.45678 -20.12345)
valid: MULTILINESTRING ((8.58 53.55, 8.58 53.56, 8.57 53.55), (8.0 53.0, 9.0 54.0, 8.0 54.0))

invalid: POINT (-20.12345 123.45678)

data, other metadata ** 
no

Arbitrary amount of data and/or metadata columns. For some metadata columns specific vocabulary needs to be used (see section).

valid: "pressure"
valid: "pressure_unit"
valid: "license"
valid: "sample_type"
valid: "exposure_time"

join
functionality		csv_join
noneno

Used to hold join keys for the use of join files. Must not appear in layer defintion files!

 

Metadata Vocabulary

This is a list of compulsory and encouraged vocabulary to use for column headers (if columns with corresponding meaning are used). Compulsory vocabulary does not mean that the column itself is compulsory. Some vocabulary is encouraged to foster consistency among AWI-hosted OWS. but may be deviated from (e.g. basis instead of platform).

metadata
category		column
vocabulary		vocabulary
compulsory?		comment
space/time
metadata

date_time_start

compulsory 

date_time_end

compulsory 

elevation

compulsory 

z_value

compulsory 

z_type

compulsory 

geometry

compulsory 
acquisition/parameter
metadata

expedition

encouraged
(aka "Richtlinie")		see https://wiki.pangaea.de/wiki/Campaign

event

encouragedsee https://wiki.pangaea.de/wiki/Event

platform

encouragedsee https://wiki.pangaea.de/wiki/Basis

method

encouragedMethod used for measuring (see https://wiki.pangaea.de/wiki/Method#2._Methods)

device

encouragedInstrument used for measuring (see https://wiki.pangaea.de/wiki/Method#1._Instruments)

registry_uri

compulsory

Was "sensor_uri" before.

Handles, not URNs!

yes: https://hdl.handle.net/10013/sensor.d3360ba2-df00-44c3-a4b5-6af6ee2bb301

no: vessel:polarstern:gyrocompass

abstract
metadata

citation

compulsorycitation string

license

compulsorylicense name (not license text)

data_url

compulsoryURL, pointing to original data source

metadata_url

compulsoryURL, pointing to (standard-compliant) metadata

sop_url

compulsoryURL, pointing to documented Standard Operating Procedures

doi_url

compulsoryURL, pointing to according DOI
join
functionality

csv_join

compulsory
not exactly data or metadata, more of a helper column to add data from a join file to the data file.

Join Files (optional)

Basic relations between files are supported. For each data file (*.sdi.csv) a second CSV (*.sdi.join.csv) file can be joined using the column csv_join to prevent redundancy in data exchange to a certain level. Both files will be joined during database ingest using the following statement. Join Files can hold arbitrary data and metadata columns (but not the space/time columns, marked with yellow-background in above tables geometry colum). For details, see the example section.

...

Code Block
languagebash
titledata files
linenumberstrue
collapsetrue
# data-0.sdi.csv
date_time_start	geometry	temperature
2023-06-28T00:00:00+00	POINT(8.5 53.5)	28.76
2023-06-28T01:00:00+00	POINT(8.5 53.5)	27.65

# data-1.sdi.csv
date_time_start	geometry	temperature
2023-06-28T02:00:00+00	POINT(8.5 53.5)	26.54
2023-06-28T03:00:00+00	POINT(8.5 53.5)	25.43

Resulting Layer Table

date_time_startgeometrytemperature

2023-06-28T00:00:00+00

POINT(8.5 53.5)

28.76

2023-06-28T01:00:00+00

POINT(8.5 53.5)

27.65

2023-06-28T02:00:00+00

POINT(8.5 53.5)

26.54

2023-06-28T03:00:00+00

POINT(8.5 53.5)

25.43

Minimal Example #2

Input Files

...

Code Block
languagebash
titledata files
linenumberstrue
collapsetrue
# data-0.sdi.csv
date_time_start	geometry	platform	temperature
2023-06-28T00:00:00+00	POINT(8.5 53.5)		28.76
2023-06-28T01:00:00+00	POINT(8.5 53.5)	Black Pearl	27.65

# data-1.sdi.csv
date_time_start	geometry	salinity	platform
2023-06-28T02:00:00+00	POINT(8.5 53.5)	26.54	Flying Dutchman
2023-06-28T03:00:00+00	POINT(8.5 53.5)	25.43

Resulting Layer Table

date_time_startgeometrytemperaturesalinityplatform

2023-06-28T00:00:00+00

POINT(8.5 53.5)

28.76



2023-06-28T01:00:00+00

POINT(8.5 53.5)

27.65


Black Pearl

2023-06-28T02:00:00+00

POINT(8.5 53.5)


26.54

Flying Dutchman

2023-06-28T03:00:00+00

POINT(8.5 53.5)


25.43


List/Dictionary Example

Input Files

...

Code Block
languagebash
titledata files
linenumberstrue
collapsetrue
# data-0.sdi.csv
date_time_start	geometry	people_dict	people_list
2023-06-28T00:00:00+00	POINT(8.5 53.5)	{"Alice": {"age": 18, "favorite_colour": "blue"}, "Bob": {"age": 19, "favorite_colour": "pink"}}
2023-06-28T01:00:00+00	POINT(8.5 53.5)	    ["Alice", "Bob", "Charlie"]

Resulting Layer Table

date_time_startgeometrypeople_dictpeople_list

2023-06-28T00:00:00+00

POINT(8.5 53.5)


{"Alice": {"age": 18, "favorite_colour": "blue"}, "Bob": {"age": 19, "favorite_colour": "pink"}}

 


2023-06-28T01:00:00+00

POINT(8.5 53.5)

 

["Alice", "Bob", "Charlie"]

Join File Example

Input Files

...

Code Block
languagebash
titledata files
linenumberstrue
collapsetrue
# people.sdi.csv
date_time_start	geometry	name	phone	csv_join
2015-01-01T01:00:00+00	POINT(8.5 53.5)	Andreas	-1744	awi-se
2019-08-01T01:00:00+00	POINT(8.5 53.5)	Robin		awi-rz-se
2019-11-01T00:00:00+00	POINT(8.5 53.5)	Kono	-2362	awi-se 
2020-01-01T00:00:00+00	POINT(8.5 53.5)	Christopher    	awi-se
2020-01-01T00:00:00+00	POINT(8.5 53.5)	Max	-2561	awi-dls
2021-01-01T00:00:00+00	POINT(10.12 54.2)	Felix	    geomar
2000-01-01T00:00:00+00	POINT(8.5 53.5)	Antje	    awi-dir

# people.sdi.join.csv
csv_join    institute    department	division	group
awi-se	AWI	Computing & Data Centre	DATA	Software Engineering
awi-dls	AWI	Computing & Data Centre	DATA	Data Logistics Support
geomar	GEOMAR
awi-dir	AWI	Board of Directors

Resulting Layer Table

date_time_startgeometrynameinstitutedepartmentdivisiongroupphone

2015-01-01T01:00:00+00

POINT(8.5 53.5)

Andreas

AWI

Computing & Data Centre

DATA

Software Engineering

-1744

2019-08-01T01:00:00+00

POINT(8.5 53.5)

Robin

AWI

Computing & Data Centre

DATA

Software Engineering


2019-11-01T00:00:00+00

POINT(8.5 53.5)

Kono

AWI

Computing & Data Centre

DATA

Software Engineering

-2362

2020-01-01T00:00:00+00

POINT(8.5 53.5)

Christopher

AWI

Computing & Data Centre

DATA

Software Engineering


2020-01-01T00:00:00+00

POINT(8.5 53.5)

Max

AWI

Computing & Data Centre

DATA

Data Logistics Support

-2561

2021-01-01T00:00:00+00

POINT(10.12 54.2)

Felix

GEOMAR





2020-01-01T00:00:00+00

POINT(8.5 53.5)

Antje

AWI

Board of Directors


Chunking Example

Beispieldaten von wetterkontor.de.

...

Code Block
languagebash
titledata files
linenumberstrue
collapsetrue
# bremerhaven@2022.sdi.csv
date_time_start date_time_end   geometry        precipitation
2022-01-01T00:00:00+00  2022-02-01T00:00:00+00  POINT(8.57 53.54)       54.9
2022-02-01T00:00:00+00  2022-03-01T00:00:00+00  POINT(8.57 53.54)       129.5
2022-03-01T00:00:00+00  2022-04-01T00:00:00+00  POINT(8.57 53.54)       24.2
2022-04-01T00:00:00+00  2022-05-01T00:00:00+00  POINT(8.57 53.54)       57.7
2022-05-01T00:00:00+00  2022-06-01T00:00:00+00  POINT(8.57 53.54)       76.1
2022-06-01T00:00:00+00  2022-07-01T00:00:00+00  POINT(8.57 53.54)       78.4
2022-07-01T00:00:00+00  2022-08-01T00:00:00+00  POINT(8.57 53.54)       60.1
2022-08-01T00:00:00+00  2022-09-01T00:00:00+00  POINT(8.57 53.54)       21.0
2022-09-01T00:00:00+00  2022-10-01T00:00:00+00  POINT(8.57 53.54)       170.9
2022-10-01T00:00:00+00  2022-11-01T00:00:00+00  POINT(8.57 53.54)       24.6
2022-11-01T00:00:00+00  2022-12-01T00:00:00+00  POINT(8.57 53.54)       47.8
2022-12-01T00:00:00+00  2023-01-01T00:00:00+00  POINT(8.57 53.54)       67.1

#bremerhaven@2023.sdi.csv
date_time_start date_time_end   geometry        precipitation
2023-01-01T00:00:00+00  2023-02-01T00:00:00+00  POINT(8.57 53.54)       93.0
2023-02-01T00:00:00+00  2023-03-01T00:00:00+00  POINT(8.57 53.54)       41.9
2023-03-01T00:00:00+00  2023-04-01T00:00:00+00  POINT(8.57 53.54)       96.1
2023-04-01T00:00:00+00  2023-05-01T00:00:00+00  POINT(8.57 53.54)       71.0
2023-05-01T00:00:00+00  2023-06-01T00:00:00+00  POINT(8.57 53.54)       14.8
2023-06-01T00:00:00+00  2023-07-01T00:00:00+00  POINT(8.57 53.54)       48.4

Resulting Layer Table

date_time_startdate_time_endgeometryprecipitation
2022-01-01T00:00:00+002022-02-01T00:00:00+00POINT(8.5 53.5)54.9
2022-02-01T00:00:00+002022-03-01T00:00:00+00POINT(8.5 53.5)

129.5

2022-03-01T00:00:00+002022-04-01T00:00:00+00POINT(8.5 53.5)24.2
2022-04-01T00:00:00+002022-05-01T00:00:00+00POINT(8.5 53.5)57.7
2022-05-01T00:00:00+002022-06-01T00:00:00+00POINT(8.5 53.5)76.1
2022-06-01T00:00:00+002022-07-01T00:00:00+00POINT(8.5 53.5)78.4
2022-07-01T00:00:00+002022-08-01T00:00:00+00POINT(8.5 53.5)60.1
2022-08-01T00:00:00+002022-09-01T00:00:00+00POINT(8.5 53.5)21.0
2022-09-01T00:00:00+002022-10-01T00:00:00+00POINT(8.5 53.5)170.9
2022-10-01T00:00:00+002022-11-01T00:00:00+00POINT(8.5 53.5)24.6
2022-11-01T00:00:00+002022-12-01T00:00:00+00POINT(8.5 53.5)47.8
2022-12-01T00:00:00+002023-01-01T00:00:00+00POINT(8.5 53.5)67.1
2023-01-01T00:00:00+002023-02-01T00:00:00+00POINT(8.5 53.5)93.0
2023-02-01T00:00:00+002023-03-01T00:00:00+00POINT(8.5 53.5)41.9
2023-03-01T00:00:00+002023-04-01T00:00:00+00POINT(8.5 53.5)96.1
2023-04-01T00:00:00+002023-05-01T00:00:00+00POINT(8.5 53.5)71.0
2023-05-01T00:00:00+002023-06-01T00:00:00+00POINT(8.5 53.5)14.8
2023-06-01T00:00:00+002023-07-01T00:00:00+00POINT(8.5 53.5)48.4

Complex Example

Input Files

...

Code Block
languagebash
titledata files
linenumberstrue
collapsetrue
# bremerhaven@2000-2004.sdi.csv
date_time_start date_time_end   geometry        population      csv_join
2000-01-01T00:00:00+00  2001-01-01T00:00:00+00  POINT(8.54 53.54)       120822  bhv
2001-01-01T00:00:00+00  2002-01-01T00:00:00+00  POINT(8.54 53.54)       118701  bhv
2002-01-01T00:00:00+00  2003-01-01T00:00:00+00  POINT(8.54 53.54)       119111  bhv
2003-01-01T00:00:00+00  2004-01-01T00:00:00+00  POINT(8.54 53.54)       118276  bhv
2004-01-01T00:00:00+00  2005-01-01T00:00:00+00  POINT(8.54 53.54)       117281  bhv

# bremerhaven@2005-2009.sdi.csv
date_time_start date_time_end   geometry        unemployment_%  csv_join
2005-01-01T00:00:00+00  2006-01-01T00:00:00+00  POINT(8.54 53.54)       23.7    bhv
2006-01-01T00:00:00+00  2007-01-01T00:00:00+00  POINT(8.54 53.54)       20.7    bhv
2007-01-01T00:00:00+00  2008-01-01T00:00:00+00  POINT(8.54 53.54)       18.5    bhv
2008-01-01T00:00:00+00  2009-01-01T00:00:00+00  POINT(8.54 53.54)       16.7    bhv
2009-01-01T00:00:00+00  2010-01-01T00:00:00+00  POINT(8.54 53.54)       15.4    bhv

# bremerhaven.sdi.join.csv
csv_join        city    state   area_km²
bhv     Bremerhaven     Bremen  93.8


# nrw.sdi.csv
date_time_start date_time_end   geometry        population      unemployment_%  csv_join	comment
2000-01-01T00:00:00+00  2001-01-01T00:00:00+00  POINT(7.10 51.51)       278695          ge	unemployment data missing
2001-01-01T00:00:00+00  2002-01-01T00:00:00+00  POINT(7.10 51.51)       275835  15.3    ge
2002-01-01T00:00:00+00  2003-01-01T00:00:00+00  POINT(7.10 51.51)       274926  16.0    ge
2003-01-01T00:00:00+00  2004-01-01T00:00:00+00  POINT(7.10 51.51)       273782  17.0    ge
2004-01-01T00:00:00+00  2005-01-01T00:00:00+00  POINT(7.10 51.51)       270109  18.0    ge
2005-01-01T00:00:00+00  2006-01-01T00:00:00+00  POINT(7.10 51.51)       268102  23.4    ge
2006-01-01T00:00:00+00  2007-01-01T00:00:00+00  POINT(7.10 51.51)       266772  20.1    ge
2007-01-01T00:00:00+00  2008-01-01T00:00:00+00  POINT(7.10 51.51)       264765  16.7    ge
2008-01-01T00:00:00+00  2009-01-01T00:00:00+00  POINT(7.10 51.51)       262063  15.2    ge
2009-01-01T00:00:00+00  2010-01-01T00:00:00+00  POINT(7.10 51.51)       259744  15.1    ge
2000-01-01T00:00:00+00  2001-01-01T00:00:00+00  POINT(7.63 51.96)       265609          ms	unemployment data missing 
2001-01-01T00:00:00+00  2002-01-01T00:00:00+00  POINT(7.63 51.96)       267197  6.7     ms
2002-01-01T00:00:00+00  2003-01-01T00:00:00+00  POINT(7.63 51.96)       268945  7.3     ms
2003-01-01T00:00:00+00  2004-01-01T00:00:00+00  POINT(7.63 51.96)       269579  7.8     ms
2004-01-01T00:00:00+00  2005-01-01T00:00:00+00  POINT(7.63 51.96)       270038  8.3     ms
2005-01-01T00:00:00+00  2006-01-01T00:00:00+00  POINT(7.63 51.96)       270868  9.1     ms
2006-01-01T00:00:00+00  2007-01-01T00:00:00+00  POINT(7.63 51.96)       272106  8.4     ms
2007-01-01T00:00:00+00  2008-01-01T00:00:00+00  POINT(7.63 51.96)       272951  7.1     ms
2008-01-01T00:00:00+00  2009-01-01T00:00:00+00  POINT(7.63 51.96)       273875  6.4     ms
2009-01-01T00:00:00+00  2010-01-01T00:00:00+00  POINT(7.63 51.96)       275543  6.4     ms

# nrw.sdi.join.csv
csv_join	city    state	area_km²
ge	Gelsenkirchen	Nordrhein-Westfalen	302.9
ms	Münster	Nordrhein-Westfalen	104.8

Resulting Layer Table

date_time_startdate_time_endgeometrystatecityarea_km²populationunemployment_%

2000-01-01T00:00:00+00

2001-01-01T00:00:00+00

POINT(8.54 53.54)

BremenBremerhaven93.8

120822


2001-01-01T00:00:00+00

2002-01-01T00:00:00+00

POINT(8.54 53.54)BremenBremerhaven93.8

118701


2002-01-01T00:00:00+00

2003-01-01T00:00:00+00

POINT(8.54 53.54)BremenBremerhaven93.8

119111


2003-01-01T00:00:00+00

2004-01-01T00:00:00+00

POINT(8.54 53.54)BremenBremerhaven93.8

118276


2004-01-01T00:00:00+00

2005-01-01T00:00:00+00

POINT(8.54 53.54)BremenBremerhaven93.8

117281


2005-01-01T00:00:00+00

2006-01-01T00:00:00+00

POINT(8.54 53.54)BremenBremerhaven93.8

23.7

2006-01-01T00:00:00+00

2007-01-01T00:00:00+00

POINT(8.54 53.54)BremenBremerhaven93.8

20.7

2007-01-01T00:00:00+00

2008-01-01T00:00:00+00

POINT(8.54 53.54)BremenBremerhaven93.8

18.5

2008-01-01T00:00:00+00

2009-01-01T00:00:00+00

POINT(8.54 53.54)BremenBremerhaven93.8

16.7

2009-01-01T00:00:00+00

2010-01-01T00:00:00+00POINT(8.54 53.54)BremenBremerhaven93.8

15.4

2000-01-01T00:00:00+00

2001-01-01T00:00:00+00

POINT(7.1 51.51)

Nordrhein-Westfalen

Gelsenkirchen

302.9

278695


2001-01-01T00:00:00+00

2002-01-01T00:00:00+00

POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

275835

15.3

2002-01-01T00:00:00+00

2003-01-01T00:00:00+00

POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

274926

16.0

2003-01-01T00:00:00+00

2004-01-01T00:00:00+00

POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

273782

17.0

2004-01-01T00:00:00+00

2005-01-01T00:00:00+00

POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

270109

18.0

2005-01-01T00:00:00+00

2006-01-01T00:00:00+00

POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

268102

23.4

2006-01-01T00:00:00+00

2007-01-01T00:00:00+00

POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

266772

20.1

2007-01-01T00:00:00+00

2008-01-01T00:00:00+00

POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

264765

16.7

2008-01-01T00:00:00+00

2009-01-01T00:00:00+00

POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

262063

15.2

2009-01-01T00:00:00+00

2010-01-01T00:00:00+00POINT(7.1 51.51)Nordrhein-Westfalen

Gelsenkirchen

302.9

259744

15.1

2000-01-01T00:00:00+00

2001-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

265609


2001-01-01T00:00:00+00

2002-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

267197

6.7

2002-01-01T00:00:00+00

2003-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

268945

7.3

2003-01-01T00:00:00+00

2004-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

269579

7.8

2004-01-01T00:00:00+00

2005-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

270038

8.3

2005-01-01T00:00:00+00

2006-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

270868

9.1

2006-01-01T00:00:00+00

2007-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

272106

8.4

2007-01-01T00:00:00+00

2008-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

272951

7.1

2008-01-01T00:00:00+00

2009-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

273875

6.4

2009-01-01T00:00:00+00

2010-01-01T00:00:00+00

POINT(7.63 51.96)

Nordrhein-Westfalen

Münster104.8

275543

6.4

Capacities for Unit Representations

...