Converting csv to kml/geojson using ogr2ogr or equivalent command line tools? [duplicate]

Question

I've just unsuccessfully tried to use ogr2ogr to convert CSV into KML like so:

ogr2ogr -f "KML" output.kml input.csv

The csv has "latitude" and "longitude" columns.

The attributes are all loaded but if you inspect the KML's text, the Coordinate elements are not being added so its not working.

I feel like this will work if I can inform gdal to interpret certain columns ("latitude", "longitude") as coordinates.

How might I do this?

Answer 1

According to the ogr2ogr csv documentation and also this answer, you need to specify which fields contain the geometry in a VRT file:

<OGRVRTDataSource>
    <OGRVRTLayer name="test">
        <SrcDataSource>test.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="Longitude" y="Latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource> 

Save this as a file with VRT extension and use it as the source:

ogr2ogr -f KML output.kml input.vrt

The csv is specified in <SrcDataSource>test.csv</SrcDataSource>. So for this example:

1. open a text editor and save the first code block as input.vrt
2. put your csv (test.csv), in the same directory
3. open a console or command window, change to that same directory and run the ogr2ogr command shown above.

The same steps apply for different output formats, e.g. shapefile, geojson, etc.

Answer 2

Starting with GDAL 2.1, it is possible to directly specify the potential names of the columns that can contain X/longitude and Y/latitude with the X_POSSIBLE_NAMES and Y_POSSIBLE_NAMES open option.

From the gdal csv format documentation section "Reading CSV containing spatial information > Building point geometries"

So your code would be

ogr2ogr -f "KML" output.kml input.csv \ 
   -oo X_POSSIBLE_NAMES=Longitude \
   -oo Y_POSSIBLE_NAMES=Latitude

You'll likely want to add -oo KEEP_GEOM_COLUMNS=NO to prevent Latitude and Longitude fields being written to your kml file.

Answer 3

Circumventing ogr2ogr for the first conversion, I've found a unix tool that will allow me to do this (https://github.com/mapbox/csv2geojson)

csv2geojson -lat "latitude" -lon "longitude" input.csv > intermediatefile.geojson

I use a constant name for the output file so it gets just overwritten a bunch of times, but now I can convert to kml

ogr2ogr -f KML output.kml intermediatefile.geojson

That works. Still interested in learning how to do this with just ogr2ogr.

Answer 4

If you don't like creating of VRT file mentioned above, you can use MyGeoData Converter - the import tool will create the VRT file automatically. Coordinate column is detected if the attribute name of X coordinate is:

x, xcoord, xcoordinate, coordx, coordinatex, longitude, long

or the attribute name contains:

x_*, *_x

Similar for Y coordinate:

y, ycoord, ycoordinate, coordy, coordinatey, latitude, lat

or the attribute name contains:

y_*, *_y

After then you can convert your CSV file to KML, GeoJSON or to almost any vector format...

Answer 5

You could use the command line utility csvkit by doing:

csvjson --lat lat_column --lon lng_column data.csv | ogr2ogr -f "KML" data.kml /vsistdin/

The command csvjson reads in a CSV file and when passed parameters for columns containing latitude and longitude values outputs GeoJSON. The output can then be piped directly to ogr2ogr via its /vsistdin/ parameter.