[GIS] loading a CSV with an embedded GeoJSON polygon geometry in QGIS

csvgeojsonpolygonwell-known-text

This open data portal offers download as CSV file. For larger datasets, that's the only way to download the whole dataset in one go. The CSV format is pretty creative: the polygon geometry is in GeoJSON in the "geometry" column.

If I drag and drop this file to my QGIS 2.18.1, it does not render as something geometrical. If however, I change the name of the "geometry" column to "WKT", QGIS does in fact render it. It only works with the WKT header, which is ironical. If I try to add the same info with the "Add CSV layer", it does not recognize the geometry column, as you can only feed that dialog window with WKT format, not GeoJSON.

Is this somehow normal behavior or is it a QGIS bug? Is it a valid option to offer this kind of format to the world on an open data website, or should I raise an issue to change the geometry data to WKT format?

Direct link to example data. Data structure:

"id";"objectid";"geometry";"gisid";"naam";"shape";"shape_length";"shape_area"

"1";"800";"{""type"":""Polygon"",""coordinates"":[[[4.4322857553331,51.236057528816],…[4.4322857553331,51.236057528816]]]}";"LEZ_01";"Lage-emissiezone (LEZ)";"";"1015.8237876604";"29420.558095414"

Best Answer

The "geometry" column as shown contains a list of complete and valid GeoJSON objects consistent with examples shown here: http://geojson.org/geojson-spec.html#appendix-a-geometry-examples.
CSV as such has no specification, so it is not technically invalid to have GeoJSON objects stored in a column. There is a GeoCSV specification, with which the supplied data does not comply (non-point geometry should be in WKT format). In general, CSVs containing geometry use WKT.
QGIS not recognising a column called "geometry"containing GeoJSON objects is not a bug, although possibly it could be considered a limitation.
QGIS recognising a column called "WKT" containing GeoJSON objects could be considered a bug as this is counter-intuitive.

I would recommend filing a request to QGIS to support GeoJSON objects within a column called "geometry" and an option for recognising GeoJSON in the add delimited layer dialog. Support for GeoJSON in a column named "WKT" should be removed.

I would also recommend that the data supplier create CSVs that are usable by the majority of GIS software i.e. storing data in WKT format. Alternatively, they could provide the data as standalone .geojson files containing both attributes and geometries.

Related Solutions

[GIS] Converting shapefile to CSV including attributes and geometry

Here's a simple script that uses the OGR python bindings:

import ogr,csv,sys

shpfile=r'C:\Temp\test.shp' #sys.argv[1]
csvfile=r'C:\Temp\test.csv' #sys.argv[2]

#Open files
csvfile=open(csvfile,'wb')
ds=ogr.Open(shpfile)
lyr=ds.GetLayer()

#Get field names
dfn=lyr.GetLayerDefn()
nfields=dfn.GetFieldCount()
fields=[]
for i in range(nfields):
    fields.append(dfn.GetFieldDefn(i).GetName())
fields.append('kmlgeometry')
csvwriter = csv.DictWriter(csvfile, fields)
try:csvwriter.writeheader() #python 2.7+
except:csvfile.write(','.join(fields)+'\n')

# Write attributes and kml out to csv
for feat in lyr:
    attributes=feat.items()
    geom=feat.GetGeometryRef()
    attributes['kmlgeometry']=geom.ExportToKML()
    csvwriter.writerow(attributes)

#clean up
del csvwriter,lyr,ds
csvfile.close()

EDIT: and another script to convert from your CSV to KML

import ogr,csv,sys,os
ogr.UseExceptions()

csvfile=r'C:\temp\test.csv' #sys.argv[1]
kmlfile=r'C:\temp\test.kml' #sys.argv[2]

csvreader=csv.reader(open(csvfile,'rb'))
headers=csvreader.next()

ds = ogr.GetDriverByName('KML').CreateDataSource(kmlfile)
lyr = ds.CreateLayer(os.path.splitext(os.path.basename(kmlfile))[0])

for field in headers[:-1]: #skip kmlgeometry
    field_def = ogr.FieldDefn(field)
    print lyr.CreateField(field_def)

for rec in csvreader:
    feat = ogr.Feature(lyr.GetLayerDefn())
    for i,field in enumerate(headers[:-1]): #skip kmlgeometry
        feat.SetField(field, rec[i])
    feat.SetGeometry(ogr.CreateGeometryFromGML(rec[-1]))
    lyr.CreateFeature(feat)

del lyr,ds

[GIS] Creating CSV with geometry as WKT in QGIS (with choosing field delimiter)

I was able to export to CSV, using other than a comma, by separating the layer creation options in the Save As.. dialog with linebreaks.

Neither comma, nor space-separating them (even when they were in quotes) worked, but the linebreaks did the trick. To emphasize..

THIS APPROACH WORKED (linebreak-separated):

GEOMETRY=AS_WKT
SEPARATOR=SEMICOLON
LINEFORMAT=CRLF

But these approaches did NOT work...

(space-separated options):

GEOMETRY=AS_WKT SEPARATOR=SEMICOLON

(comma-separated options):

GEOMETRY=AS_WKT, SEPARATOR=SEMICOLON

(quoted, and either space or comma-separated LCOs):

"GEOMETRY=AS_WKT", "SEPARATOR=SEMICOLON"

As @underdark noted, there are some additional options discussed on the OGR CSV driver page you may want to be aware of. Specifically, the SEPARATOR parameter will accept either SEMICOLON or TAB in addition to COMMA. Also, you may want to experiment with the LINEFORMAT parameter, which supports either CRLF (dos/windows) or LF (unix).

A picture says a thousand words, so here's a screen grab. :)

enter image description here

Best Answer

Related Solutions

[GIS] Converting shapefile to CSV including attributes and geometry

[GIS] Creating CSV with geometry as WKT in QGIS (with choosing field delimiter)

Related Question