4. Convert GIS formats

4.4. Convert Comma Separated Values (CSV)

Sometimes you want to reproject coordinates in an ASCII file, e.g. which has been saved to text in a spreadsheet program. Here we will convert the coordinates in a comma separated ASCII file locations.csv to a new ASCII file locations_reprojected.csv.

It is good practice to first view the contents of a CSV file and to verify (1) the coordinates, and (2) the column separator. The separator is not always a comma and sometimes depends on the language settings used while exporting from a spreadsheet programme.

1. View the contents of locations.csv . You can use the type command from the command prompt as you learned in the Command Line tutorial.

We can see that coordinates are in lat/lon, which means that we can use EPSG:4326. The column separator is a comma. The last column gives objects with a string in quotes.

To change the projection of the CSV, we first have to create a virtual data source by creating an XML control file.

2. On your windows computer open notepad and type/copy the XML code given below. Use indentations of three spaces.

<OGRVRTDataSource>
   <OGRVRTLayer name="locations">
      <SrcDataSource>locations.csv</SrcDataSource>
      <GeometryType>wkbPoint</GeometryType>
      <LayerSRS>EPSG:4326</LayerSRS>
      <GeometryField encoding="PointFromColumns" x="lon" y="lat"/>
   </OGRVRTLayer>
</OGRVRTDataSource>

3. Save the file as locations.vrt in the gdal_exercises folder.

Some explanation about the XML file:
  • <OGRVRTLayer name="locations"> should correspond with the <SrcDataSource>locations.csv</SrcDataSource>
  • <LayerSRS>EPSG:4326</LayerSRS> should correspond with the EPSG code of the coordinate columns
  • <GeometryField encoding="PointFromColumns" x="lon" y="lat"/> indicates the columns with the coordinates that you want to convert.
4. Execute the following command:
ogr2ogr -t_srs EPSG:28992 -f "CSV" locations_reprojected.csv locations.vrt -lco GEOMETRY=AS_XY

In this example locations.csv with lat/lon WGS-84 coordinates is converted to locations_projected.csv with Amersfoort/RD New projection.

5. Use notepad to check locations_reprojected.csv. What is saved in each column?

In the same way we can convert the comma separated file to a shapefile.

6. Execute the following command:
ogr2ogr -f "ESRI Shapefile" -t_srs EPSG:28992 locations.shp locations.vrt

7. Check the result with ogrinfo.

8. Visualize the shapefile in QGIS by plotting the locations over the DEM, road map and Delft community border. Make a nice map.

9. Convert the locations file to Google KML, open in Google Earth and find out what the object locations are.