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.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *