Exploring PostgreSQL data with QGIS

From Open source mapmaking technologies
Jump to: navigation, search

QGIS reads and writes data from PostGIS. In fact, this was one of the major motivations behind the initial QGIS development: to create a good visualization tool for PostGIS.

Try to add a PostGIS layer to QGIS, by selecting Layer → Add PostGIS Layers...

Add PostGIS table without connections.png

A dialog opens for you, to choose a database to connect to. We haven't done any connection yet to our new database beijing. We need to creating a new database connection.

PgAdmin imported tables.png

Afterwards, we just need to select this new connection to add any table from this database.

QGIS connecting to beijing database.png

Adding all features

After connecting to a database, you see all tables. You can add all features from a table or just part of them.

Let's add the entire chn_adm3 table to QGIS.

Select which features to add

For large datasets, we want to keep the QGIS project light as possible, loading only the features we want to manipulate.

Let's add only the restaurants from the layer planet_osm_point.

QGIS adding only restaurants.png

Exercise

Remove the chn_adm3. Add it again, but filtering only the subdivisions within the Beijing municipality (the condition is "name_1" = 'Beijing').

QGIS add filtered chn adm3.png

DB Manager plugin

Let explore the nice DB Manager plugin. Open it by going to the Database menu.

QGIS DB Manager.png

For each table, you can see its metadata, table view and map preview.

On the Table menu, there are some handy operations that we will explore later.

For now, let's see some advanced feature of this plugin.

Performing queries

You can write queries using the DB Manager. The query editor supports table and field name completion, among other things.

Let's write our first query.

select * from planet_osm_polygon where building = 'yes'

Around 25k polygons are returned by the query.

Let's do a more sophisticated query.

select * 
from planet_osm_polygon, chn_adm3
where building = 'yes'
and st_contains(chn_adm3.wkb_geometry, planet_osm_polygon.way)
and name_2 = 'Beijing'

Run the query and check that you can get a result set.

Buildings query.png

Save the query. Assign it a name, like 'wuhan buildings'. The query string will be saved with the project.

Check Load as new layer.

Column with unique integer values: none

Geometry columnm: way

QGIS DB Manager load as new layer.png

Showing (another) query

Restaurantes in 2km radio.png