GeoCSV: Unterschied zwischen den Versionen

Version vom 2. Mai 2015, 13:36 Uhr

Specification of the tabular file format CSV (Comma Separated Values) with a geometry extension!

Date of last modification: see bottom, Author: Stefan. (For notes and discussion see Diskussion:GeoCSV).

Introduction

GeoCSV is an extension of the "human readable", tabular file format CSV (Comma Separated Values). CSV is a well-known but spartanic format with possible information loss. Think about using more capable and elegant formats for desktop file exchange like e.g. GeoPackage.

One the other hand it some potential since it's quite more capable as e.g. a Shapefile. See also TheShapefileChallenge.

This format has following drawbacks:

• cluttered files, like .csvt and .prj
• no layer name - except for the file name (which can be changed easily by others...).

CSV file format specification

Files (extensions, encoding and compression):

• CSV:
  • Contains the actual (geo-)data.
  • File extension is .CSV (or .csv).
  • Encoding is either UTF-8 (default) or ANSI.
  • End-of-lines are: CR, LF or CR/LF.
  • Line Breaks in (String) fields are disallowed.
• CSVT:
  • Contains field type information (schema).
  • File extension is .CSVT (or .csvt).
  • See section below.
• PRJ:
  • Contains Coordinate Reference Information (CRS)
  • File extension is .PRJ (or .prj).
  • Contains the EPSG number "EPSG:nnnn" or the .prj format from Shapefile.
• CSVZ (file extension is .CSVZ (or .csvz)) and accompanying files:
  • The CSV file can be accompanied with following two files having the same file base name: .csvt and .prj.
  • Archiving and compressing in format .ZIP (or .zip) is also possible and encouraged.
  • This zipped file includes a .csv and optionally other files described here, like .csvt.

Rows:

• First row contains attribute names separated by a => delimiter.
• Following rows are contains values separated by a => delimiter.
• All rows have same number of attributes.

Fields/columns:

• Field delimiter is semicolon (;) by default.
• Strings are enclosed by parantheses, to allow delimiters inside (e.g. "string").
• Data types (if supported from source or target system): See CSVT file format specification.
• Calculations are possible in fields of type String (like "=A1+B1").

See also CSV.

GeoCSV file format specification

GeoCSV is based on the above CSV specification and comes with two variants: Options easting/northing and Options WKT.

Option "easting/northing" (longitude/latitude, similar to x/y in mathematics):

• Geometry Point type as two neighboring columns of type Float: one containing the easting coordinate, and one containing northing coordinate separated by the common delimiter.
• Example for the two easting/northing columnts "8.8249;47.2274".
• This option supports only Points.

Option WKT:

• It' one single column of type String containing a constructor, like for example: "POINT (8.8249 47.2274)".
• This option supports Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon and even GeometryCollection and ARCs!
• WKT ("Well Known Text") is originally defined by the Open Geospatial Consortium (OGC) and described in their Simple Feature Access specification (also ISO SQL/MM). See e.g. http://en.wikipedia.org/wiki/Well-known_text

Common restrictions:

• Coordinate system is WGS84 (EPSG:4326) by default.
• There is only one geometry column allowed per sheet.
• All geometry values within one table are in the same coordinate reference system (CRS).

CSVT file format specification

CSVT means "CSV Types" and it describes the field types and eventually their properties.

Field/column types, case insensitive (if supported from source or target system):

• Integer
• Real
• String
• Date ("YYYY-MM-DD"), Time ("HH:MM:SS+nn") and DateTime (YYYY-MM-DD HH:MM:SS+nn)
• (Lon/Lat)
• (WKT)

Notes:

• The geometry types are a kind of subtype: easting and northing values are stored as float, option WKT is stored in one column of type String.
• See also http://www.gdal.org/drv_csv.html section with .csvt extension.
• (There could be more properties like "mantatory/optional" or, for strings field lenght, and for numbers precision etc.)

Software

• Desktop
  • LibreOffice / OpenOffice
  • Excel
  • OGR
  • QGIS
• Online:
  • GeoConverter
  • CSV-to-GeoJSON: convertcsv.com, csv2geojson
  • GeoJSON-to-CSV: convertcsv.com

Examples

CSV type file 'example1.csvt':

Integer;String;Real;String;lon;lat

CSV file 'example1.csv - Option easting/northing :

id;name;amount;city;lon;lat
1;Kevin;2.1;Rapperswil;8.8249;47.2274
2;Eva;2.2;Zürich;8.5435;47.3768
3;"Jimmy;Muff";2.3;;7.4397;46.9487

CSV file 'example1.csv - Option easting/northing :

id;name;amount;city;WKT
1;Kevin;2.1;Rapperswil;POINT(8.8249 47.2274)
2;Eva;2.2;Zürich;POINT(8.5435;47.3768)
3;"Jimmy;Muff";2.3;;POINT(7.4397;46.9487)

...can be shown as following table:

Note the remarks string in row 2 and the empty string in row 3.