Mois : janvier 2016

Spreadsheet to GIS

Sooner or later, getting the spatial data you asked for, you’ll be provided with a… .xls spreadsheet file, which might look like this:

longitude | latitude | name
-52.31018 | 4.19783  | Saut Mapaou
-53.05015 | 5.09345  | Crique Grégoire

Let’s call it poi.xls.

Assuming coordinates are in a homogeneous decimal format, and that the coordinate reference system is known, here is how to use this file with the GIS you like, thanks to GDAL:

Write a VRT file poi.vrt describing:

  • the desired layer name ;
  • the spreadsheet file name ;
  • the geometry type ;
  • the reference system EPSG code ; and
  • the column names the geometry will be built from.
<OGRVRTDataSource>
  <OGRVRTLayer name="Points">
    <SrcDataSource>poi.xls</SrcDataSource>
    <GeometryType>wkbPoint</GeometryType>
    <LayerSRS>EPSG:4326</LayerSRS>
    <GeometryField encoding="PointFromColumns"
     x="longitude" y="latitude" />
  </OGRVRTLayer>
</OGRVRTDataSource>

You can now open poi.vrt with QGIS or convert it to any GDAL supported vector format. For example, reproject and import it to PostGIS:

 ogr2ogr -t_srs EPSG:2972 -f PostgreSQL PG:dbname=survey poi.vrt -lco GEOMETRY_NAME=geom -nln poi_xls

In case GDAL complains about not finding the spreadsheet file, provide the absolute path as the SrcDataSource.

This procedure should also work with .xlsx files, although not tested personally.