When we select a geographic area in the map we created in the previous chapter, it gave us some information about that particular county like population, latitude, longitude e.t.c.
This information is stored in an attribute table. For the shapefile we used, physically, the information is stored in dBase, a type of database. So how do we view this
information and how do we modify it if we need to? We can use the QGIS software to do this.
Start your QGIS and open the TM_WORLD_BORDERS-0.3.shp shapefile as we had done previously and open the attribute table.
Let us look at the information for Brazil from the attribute table highlighted below in red.
5
4
27 Compare this information to what we were getting in
SpagoBI.
You can see that the data is similar which means that both the data in QGIS and the one in SpagoBI came from the attribute
For support and projects [email protected] WEB: www.learn-spagobi.com
28
table. From SpagoBI, check the population of Kenya. We want to see how to modify this. So check the value before modification.
We can see that Kenya’s population is 35 million. Let us change it to 42 million using the editing feature.
Then enter the value 42,000,000 in the POP2005 field.
29 Save your attribute table. Now reload your SpagoBI
document and check if the population information have changed for Kenya. You will find that it is still reading the old values. This is because SpagoBI is not reading the information directly from the shapefile but form the Geoserver. So if you want the changes to be visible in SpagoBI, you will need to reload the shapefile in the geoserver. Follow these steps.
1. Navigate to http://localhost:8090/geoserver and login with username admin and password geoserver.
2. Navigate to stores and select the WORLDMAP store we created previously.
3. Then under Directory of shapefiles *, select the shapefile you just modified.
4. Reload SpagoBI (Note that you may need to restart Tomcat) and check if population information for Kenya is updated.
For support and projects [email protected] WEB: www.learn-spagobi.com
30
You can see that the population information is now updated to 42,000,000.
What if you wanted to modify the column information in the attribute table? You might even want to delete the column entirely. All these can be done using QGIS. Let us say we want to delete the SUBREGION and modify POP2005 to be POP2015.
31 To make the changes above, proceed as follows.
1. Start the QGIS desktop.
2. We will need to download the Table manager plugin to help us modify columns. Click on Plugins then select Manage and Install Plugins…
3. Search for Table manager then install it.
For support and projects [email protected] WEB: www.learn-spagobi.com
32
4. Now, on the left bottom of QGIS, click on the double arrows and click on Table Manager icon.
5. Select POP2005 and select Rename.
33 6. Change it to POP2015.
7. Select subregion and then select delete.
For support and projects [email protected] WEB: www.learn-spagobi.com
34
8. Confirm deletion.
9. Save your new shapefile as World2.
35 10. We will need to create a new store and layer in
Geoserver with the new modified shapefile. Add a new shapefile store to the learnspagobi workspace as we had done previously.
11. For the shapefile, select the new one we just modified i.e World2.
12. Then Save.
For support and projects [email protected] WEB: www.learn-spagobi.com
36
13. Next, click on Publish.
14. Under Declared SRS, enter EPSG:4326 and then select Reproject native to declared under SRS
handling. Next click on Compute from native bounds.
37 15. Click on Save. Click on Layer Preview then search for
the layer World2. To preview your layer, click on OpenLayers.
16. Click on any area in the map. From the Geoserver preview, confirm that the attribute column POP2005 was changed to POP2015. Also confirm that the
For support and projects [email protected] WEB: www.learn-spagobi.com
38
SUBREGION column was deleted.
17. Now we need to modify the entry for layer name in our json file that is used by SpagoBI as the template.
In the files that came with this book, under the templates folder, open the file
Learn-SpagobiNoNavigationWorld2.json in a text editor.
18. You will note that the file references the layer World2.
Load this new json file as your new template in SpagoBI.
39 19. Save and execute the document. You will notice that
SUBREGION field have been deleted and POP2005 is now changed to POP2015.
For support and projects [email protected] WEB: www.learn-spagobi.com
40
41
Template
We now have some understanding of how to use SpagoBI with GIS but we have not yet discussed the template we used for this exercise. We will do so in this chapter. Here is the template we used in the previous chapters discussed line by line. You can get the full file in the folder template in the CD that came with this book. The file is called
Learn-SpagobiNoNavigationWorld2.json.
The first line is just an opening bracket for the json file. The second line mapName: "Students", is the name of the map panel. You can give any string here. We have called ours Students. Remember this is not a map that exists in SpagoBI, it is just a name of a panel.
Line 3, analysisType: "choropleth", means the thematistion method used. It can be:
1. Proportional-Symbols shows point geometry. Here is an example where Proportional-Symbols is used i.e analysisType: "proportionalSymbols".
6
For support and projects [email protected] WEB: www.learn-spagobi.com
42
2. Choropleth shows polygonal geometry. Here is the same map with Choropleth i.e analysisType:
"choropleth".
Line 7 or indicators: [["No of Students", "STUDENTS"]], is a numeric value that we can analyze. These can be several e.g
43 if our map was showing analysis of disease outbreak, then if we clicked on Malaria for example, it would only show
malaria on our map and if we clicked on Ebola, it would show Ebola. In our case, we only have one indicator, i.e the number of students registered at www.learn-spagobi.com
But what if we wanted to display in addition to the number of students registered for a course, the number of courses
registered for? Then we would need to modify the dataset to add a column for the number of registered courses.
Fortunately, we have already done this so modify your dataset to use the file Learn-spagobi.com Users2.csv under the
Dataset folder that came with this book. Your dataset should now look like this.
For support and projects [email protected] WEB: www.learn-spagobi.com
44
Then you would need to modify your template to include the new field as an indicator.
indicators: [["No of Students", "STUDENTS"] ,["Courses Registered", "REGISTERED"]],
45 You can then select which indicator to display.
The next lines 8 and 9 shows how you link the data in your dataset and the data in the attribute table. In your dataset, you have a column called FIPS and this is what line 8 shows under businessId. We need to link this to another column in the attribute table called FIPS and this is what line 9 shows under geoId. Remember the field names need not be the same.
You can see below how we have linked the FIPS field in the attribute table with the FIPS field in the dataset. You can see that they have similar data that is 2 character country code.
For support and projects [email protected] WEB: www.learn-spagobi.com
46
Next we need to tell SpagoBI what is the source of the map so we do so in line 15 by giving it the URL of the Geoserver.
Line 14 gives the name of the layer and line 13 is just a description. You can put anything here.
47