Performing spatial queries on postgis database using the sql menu I

This post will demonstrate carrying out spatial queries on data stored in a PostGIS database using the SQL menu. SQL functions we will be demonstrating are

ST_Buffer, ST_MakePoint and ST_Intersects.

We start with ST_Buffer. This function creates a buffer around the shapefile defined in the SQl statement.

1. Launch QGIS and open the DB Manager

1

2.We select a shapefile- Ikeja_district- stored in our postgis database.highlighted in blue on the screenshot below.

2

3. Click on the SQL icon (red boxed on the screenshot below) to open the window.

3

4. A. Define the SQl statement using the ST_Buffer function, the geometry column and the buffer distance. Click Execute tab.B. If SQL statement is valid, a table for the result is generated in the lower pane of the SQL window.C.Check the box if you desire to create a layer from the result. D. Fill the form below making sure you define an ID field and the geom field. Click Load Now to export the result to the map canvas.

4

5. On the screenshot below, the buffer created from the SQL statement is now seen around the polygon-Ikeja_district.

7

 

ST_MakePoint is useful to determine the closest spatial objects to a defined point on the map. It is a function used for doing analysis on point data. A similar function is ST_ClosestPoint.

6. Back to our DB Manager, we select a set of point data-school- representing schools of various types within a geographic extent. We intend to query to know the ten closest schools to a geographic location with coordinates- lon-3.867, lat-7,598.

8

7. The SQL statement in the window performs the query using LIMIT 10 to instruct that only the closest ten points be generated in the result. As in the previous query, we create a layer from the result and load it onto the map canvas.

10

8. The result of the query is seen as the red points on the map canvas on the screenshot below.

12

ST_Intersect defines spatial objects that have a form of spatial relationship within their geometries. Intersecting geometries should have an overlap of some sort,

9. This time we want to query which schools are intersecting with a polygon feature- Ido. Both data are stored within the PostGIS database.

On the screenshot of the SQL window, the query selects columns from the school layer and uses ST_Intersect function to query for those schools that share geometry with the polygon-Ido. We load the result as a layer on the map canvas.

20

10. The screenshot below illustrates the result of the query on the map canvas. Points that share geometry with Ido are displayed.

21

You can download sample data for practice by clicking the links below:

https://www.dropbox.com/s/mhcieow4zr1gb85/ido.zip?dl=0

https://www.dropbox.com/s/4kx8ppd4z80mlgs/ikeja_district.zip?dl=0

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

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