Performing attribute queries ON postgis database Using sql menu

This post will demonstrate how to carry out attribute queries on data stored in a POSTGIS database using the SQL menu in the database.

For our PostGIS database, we installed PostGreSQl 9.5 and created a PostGIS Connection to our QGIS Desktop application,

Download sample date by clicking the links below:

https://www.dropbox.com/s/j6jcyf9qbdaihwf/oyo.zip?dl=0

https://www.dropbox.com/s/kqrestdbxlhqjnx/school.zip?dl=0

1. Launch QGIS,go to the main menu and click Database- DB Manager – DB Manager

s1

2. In the dialog window, all PostGIs connections will appear. We click on our localhost connection named postgis and open up the catalog tree.

s2

3. We select the data – northcentral- and click to see the table view.

s3

 

4. We also click on preview to see the geography as seen on the screenshot. northcentral data is a multipolygon(a polygon broken into several smaller polygons) representing a zone made up of several districts.

s4

5.We intend to use the SQL menu to perform queries on the attributes of the data. Click on the SQL window icon (highlighted on the screenshot below)

s5

6. We query using the name of one of the districts.

1- select from the table where the name column is equal to Demsa. 2. click on execure 3.if the query is valid, the result is created as a table.4. to add the result as a layer on the map canvas, check the option and use the dropdown button to choose the appropriate columns.5. Give the layer a name and click on ‘Load Now’ to export it to the map canvas.

s7

7.The geography of the selected district is displayed on the map canvas as seen on the screenshot below.

s8

8. That was a very simple query. Let us go to another that is a bit more advanced, This time we use the table-school– point data that represent schools within a geographic extent. Here, we write sql statement that queries which schools are primary schools and are city owned. The result is shown on the result pane of the SQL menu window. Just as earlier, we load the result onto the map canvas.

s11

9. The points that have the declared attributes in the SQL statement are shown on the map canvas in the screenshot below.

s12

10. So far we have been querying qualitative attributes, this time we query quantitative attributes declaring terms like greater than in our SQL statements.

We carry out the queries on the table-oyo- a multipolygon representing administrative districts.

s14

11. We write our sql statement querying which districts have males greater than 85000 and females greater than 75000. We execute and the result is on the result pane. We load the result as a layer on the map canvas.

s15

12.The districts are displayed as polygons on the map canvas.

s16

13. Next we write a sql statement that embodies both qualitative and quantitative. We query for districts within the multipolygon –oyo, where there are more males than females.

s18

14. See result on the map canvas. Note the darker hue of the query result illustrates that almost all the districts have more males than females. We overlaid the query result over the map of the entire state as shown on the screenshot below.

s19

 

Thank you for following.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s