14 July 2017

Practical Guide to Edit Shapefiles with QGIS Software

Data visualization is mostly made through tables in reports or with the use of charts like bars, pies, lines, etc., but when we talk in data based on location the most common type of visualization is the map.

There are three type of maps in SSRS that can be used, being them basic, bubble or analytical maps. However, when we use the maps, the real challenge is not choosing the type but how to make it.

SSRS and the spatial data sources

A map uses two types of data: spatial and analytical. Spatial data defines the map appearance and they are constituted by a set of coordinates. Each set of coordinates defines an element of the map, for example, a polygon represents the outlines of a county, a line represents a road or a point represents the location of a city.

When we create a map in SSRS, it is necessary to select the spatial data source. SSRS has three types of data sources to be selected:

  • Map gallery: Map gallery installed with SSRS. This gallery, as default, just has the map of the United States and individual maps for each state (it’s possible to add more maps from other countries to the gallery);
  • ESRI Shapefile: This option enables to select a shapefile file from our computer;
  • SQL Server spatial query: It is possible to build a query that uses the SQLGeometry or SQLGeography data types from a relational database.

 

ESRI Shapefiles

An ESRI Shapefile, or just shapefile, is a file that keeps geospatial vector data format and that contains the position, format and attributes of geographic elements. A shapefile must have at least three files associated, being:

> .shp – this file stores the geometry of the elements;

> .shx – this file stores the geometry index;

> .dbf – this file stores all the information about the attributes (city names, etc.).

All files described above must be saved in the same directory with the same name, otherwise the shapefile won’t be loaded.

Being the shapefile a simple file structure, it has as principal advantage, comparing with other data sources, the possibility of edition.

QGIS

As was told above, it is possible to edit this type of files and the software that we are going to use for that is the QGIS application. It is an open source software, which allows the visualization, edition, analysis of georeferenced data, and among other functionalities. For more details about this application you can consult the documentation provided by the website (the version used for this case study was 2.14.7).

On the image below, we can see an example of QGIS’s interface with a shapefile of the Continental Portugal map loaded.

Edit Shapefiles with QGIS Software

Fig.1: Interface of QGIS editing software

Case study

The idea to write this article came from the need to create a report with a map of Continental Portugal and islands at district level. The objective of this report was to click on each of the districts on the map and make a drilldown to the detail of municipalities.


Fig.2: On the left a map of the continent at district level. 
When we click, for example, on the Lisbon district, a drilldown 
would be made for all municipalities from this district as shown on the right

Having said this, the first step was downloading the files in shapefile format corresponding to the polygons of the administrative areas of the continent and islands, available at the Direção Geral do Território website. The size of the files can reach 50MB because it has too many details, meaning that creating a report using this file can result in a heavy report with long loading periods.

Since we do not need all the information provided in the file, we are going to edit it following the steps below (Note: it will only be shown how to edit the map of Portugal and Azores Islands (Central), whereby the logic of the edition of the remaining maps, Azores (East and West) and Madeira islands, is the same):

QGIS

1. After the extraction of the files, open the QGIS and open the file as follows:

a. Press Ctrl+Shift+V and it will open a window like below. Click “Pesquisar” and select the directory that contains the extracted files

Edit Shapefiles with QGIS Software

b. First, we are going to edit the Portugal map so you have to open the file with the .shp extension which is in the selected folder as below. After selecting the file, click “Open” and at last “Abrir”

Edit Shapefiles with QGIS Software

2. After loading the file, we can verify the big amount of visible polygons in the map because it is detailed at parishes level. The next step is to remove the information that we don’t need and for that we are going to change the original shapefile and aggregate it at district level as follows:

a. Click on menu “Vector” > “Ferramentas de geoprocessamento” > “Agregar” and it will pop up the next window:

Edit Shapefiles with QGIS Software

b. On the field “Camada vetorial de entrada” select the file that was loaded

c. On the option “Campo de agregação” change to “Distrito”. Before clicking “OK” it is necessary to define an output shapefile and for that click on the “Explorar” button and save the new file in a directory by choice

d. When the aggregation algorithm finishes, click on “Fechar” and the new map stays immediately visible

3. As it was told on the beginning of the document, there is a file with the .dbf extension that stores all the information of the attributes (district names, municipalities, parishes, etc). On this stage, it can be added attributes, edit the existing ones or remove them. In our case, we are going to edit the attributes on the new shapefile and remove the information about the parishes as follows:

a. On the layers panel, immediately at the left side of the map, click with the mouse right button on the new shapefile and click “Abrir Tabela de Atributos”

Edit Shapefiles with QGIS Software

b. The next window will open:

Edit Shapefiles with QGIS Software

c. Following, press Ctrl+E( ) to activate the edition mode and press Ctrl+L( ) to delete fields:

1. On the window that pops up, select all the fields except “Distrito”

d. Press Ctrl+S( ) to save the changes made and press again Ctrl+E( ) to deactivate the edition mode. Close the window.

4. Next, we are going to edit the Azores Island (Central), repeating all the steps above. Take special attention at step 2 because you have to select the field “Ilha” instead of the aggregation field “Distrito”. At step 3 do as follows:

a. Remove all the attributes except the field “Ilha”

b. Press Ctrl+W( ) to add a new field:

1. Nome: Distrito

2. Tipo: Texto

3. Comprimento: 30

c. Copy the values from the “Ilha” field to this new field

d. Remove “Ilha” field

5. After the changes made above, we are going to create a single shapefile composed by the two maps that were edited through the menu “Vector” > “Ferramentas de gestão de dados” > “Unir Ficheiros Shapefile numa só...”. It will open a window as below:

a. On the field “Diretoria de entrada” click on “Explorar” and choose the directory which contains the files edited before

b. On the field “Shapefile de saída” click on “Explorar” and give a name to the new file. On our example we gave the name “Distr_CAOP2016”

c. Click “OK”

d. At the end, in the layers panel, we have the files:

6. The next step is to edit the coordinates of the maps so they can be “together” (yes, we are going to put Azores next to Lisbon since this isn’t a map in real scale):

a. At layers’ panel select the file created on the last step and with the right mouse button click on “Aproximar à camada”

b. Select again the file and with the right mouse button click “Alternar Edição”

c. Then, click on the icon shaded in yellow (image below) and select the elements to move:

d. After selecting the elements, move them to the center by clicking on the icon shaded in yellow as in the image below:

1. Do the same for the map of Portugal

e. To finish, click on the yellow pencil icon (image above) and a window will appear showing a message to save the changes made. Click “Gravar” and the final result will be identical as the image below:

7. Finally, we will decrease the resolution of the map so that, in this way, we are able to reduce the number of the points/vertices, and the file size, ensuring a better performance in the execution of the final report. Click on menu “Vector” > “Ferramentas de Geometria” > “Simplificar Geometrias…”:

a. On the field “Tolerância de generalização” define the value “25,0001” and click “OK”

1. This value is defined by trial and error. Defining the value “25,0001” we are decreasing the file size from 7MB to 563KB. It is possible to reduce even more, but bigger the value, bigger the lost in definition of the map because this method converts polygons into lines

b. Save a new file to maintain the original

c. Click “OK”

d. The edition mode will be automatically activated so you must click on the yellow pencil icon and save the changes made

8. Execute, again, the steps above but this time to create the map for the municipalities

a. To the islands you have to select the field “Município”

b. On the simplification of geometries, the defined value was “100,0001”, decreasing the original file from 20MB to 660KB

c. The final result will be identical as the image below:

SSRS

After the edition of the shapefiles we are going to create the reports in SSRS:

1. Create a Report Server project

2. Create a report for the districts and open it

3. In Visual Studio’s toolbox, drag the “Map” item to the report zone

4. It will open a window like the image below. Choose the option “ESRI shapefile” and select the edited shapefile referent to the districts

Edit Shapefiles with QGIS Software

a. Click “Next” in the following two windows

b. In the window to choose the type of visualization of the map, there are three types presented: basic map, colour analytical map and bubble map. Select the second option, the colour analytical map and click “Next”

c. In the following window, choose the second option to add a dataset that contains the analytical data to relate with our map. Click “Next”

d. Create a data source connected to the SQL Server database that contains the table with the analytical data. Click “Next”

e. The next step is to build the query that returns the data for the analysis. For the district’s map, it is important to have the distinct information for each district. Click “Next”

f. At this step, it is necessary to define which attribute relates with the spatial data, to create the relation between the analytical data and the map. Choose the “Distrito” field and click “Next”

Edit Shapefiles with QGIS Software

g. In the last window, we can make simple changes such as changing the theme of the map, the colour scheme or if we pretend to show some kind of description on the map, considering the attributes defined in the query. For now, leave as follows:

 

5. After making some changes in the report’s layout, the final result is:

6. Create a report for the municipalities and open it

7. Drag the “Map” item in Visual Studio’s toolbox to the report zone

a. Choose the option “ESRI shapefile” and select the edited shapefile referent to the municipalities. Click “Next” in the following two windows

b. Select the colour analytical map type and click “Next”

c. In the following window, choose the second option to add a dataset that contains the analytical data to relate with our map. Click “Next”

d. Create a data source connected to the SQL Server database that contains the table with the analytical data. Click “Next”

e. The next step is to build the query that returns the data for the analysis. For the municipalities’ map, it is important to have the information for each municipality, to what district belongs (it will be necessary later) and the analytical values. After building the query click “Next”

f. Define which attribute relates with the spatial data, to create the relation between the analytical data and the map. Choose the “Concelho” field and click “Next”

g. At last, choose the field that we want to analyse. On the field “Field to visualize” select the column that contains the values for analysis. Click “Finish”

8. After making some changes in the report’s layout, the final result is:

9. Still in the municipalities’ report, create a parameter with the name “Distrito”, data type “Text” and visibility “Hidden”

10. In the created dataset for this report, it’s necessary to edit the query to add the following filter: where Distrito = @Distrito

11. Click with the right mouse button on the white space beside the map and choose the menu’s option “Viewport Properties...”

a. In “Center and Zoom” properties

1. Choose the option “Center map to show all data-bound map elements”

2. In the field “Zoom level (percent)” write the expression =”Auto” and click “OK”

12. The next step is to define an action in each polygon on the map of the districts in order to create the drilldown for the municipalities. For that, open again the map of districts and click on the report to show the menu visible below. Click above the layer with the right mouse button and click on “Polygon Properties...”

13. In “Action” property, define the values like the image below:

14. Click “OK” and execute the district’s report. At the end the result will be as follows:

João

João

João

João

     João Gaspar
       Consultant
Blog