Section : 1. Introduction to QGIS

Module : 1.7. Working with Spreadsheets

Adding spreadsheets in QGIS

“GIS Data can be sourced from various formats. Users can digitise , import GPS points and in some cases the data can be captured in a spreadsheet and then imported into a GIS.”

Data exists in various formats, shapefile being the most commonly used data type contains a file with the extension .dbf which is basically a spreadsheet that has information linking it to the geometry. There are various techniques for generating spatial data but the flexibility of GIS software allows users with spreadsheet data to be imported in a GIS as spatial data where the latitude and longitude are converted to their geometry types or none spatial tables where they are flat tables without any geometry columns. In this module, we will look at how to import spreadsheets and use them in QGIS

You try:

Goal: To explore spreadsheet data and use it within QGIS.

  • Navigate to the exercise data.
  • Use Excel to open your spreadsheet. If you are using an operating system other than windows use an appropriate program.
  • In the Manage Layers Toolbar click Add Vector layer to add the spreadsheet into QGIS.
  • In QGIS you will notice that there is a flat table added. Open the attribute table.
  • Notice that we two columns latitude and longitude. These can be converted into point geometry using expressions or algorithms in QGIS.

Method A - Using Processing Toolbox

  • From the Processing Menu Activate the Toolbox.
  • Use the Search bar to locate the algorithm Create Points Layer from Table
  • In the algorithm, select the appropriate values to populate the fields X and Y using either latitude or longitude.
  • Look at the layer generated in QGIS.

Method B - Using Virtual layers

  • Navigate to the Manage Layers Toolbar and select Add virtual layer.
  • In the top menu of the virtual layer give it a reasonable name ie parks_and_protected_land.
  • Import the parks_and_protected_land layer.
  • In the SQL window enter the point expression specified below.

Method C - Using Plugins

  • You can also use plugins that allow you to directly import the spreadsheet and it will convert the latitude and longitude into a point layer. Example of such a plugin is xyToPoint

Name Expectation

Point Layer

Select fid, make_point( "longitude","latitude" ) as geometry from parks_and_protected_land

More about spreadsheet in QGIS

Spreadsheets are a source of data and they are very useful as a lot of people understand them and prefer to work with them rather than GIS. When the data stored in the spreadsheet contains geometry information either as latitude/longitude or wkt_text then it becomes imperative to use a GIS system to create geometry and visualize it. Even if the spreadsheet does not contain geometry information they can still be imported in QGIS and visualized as a none spatial table and this can be linked to other existing tables. QGIS provides expressions to convert the tabular data into spatial data but the process sometimes involves a series of steps as noted by the methods we used in the exercise.

Check your knowledge:

  1. Is it possible to save attribute tables into a spreadsheet from QGIS:

    1. It is possible within QGIS
    2. No, It is not possible without extending the fnctionality of QGIS

  2. What does latitude/longitude represent in the real world:

    1. These are the geographic co ordinates of any particular place on the earth surface
    2. These are scientific names used in GIS to depict type of vector data.
    3. These are terms that are synonymous with raster data.

Download the sample data for the lesson.