Exploring PostgreSQL data with QGIS
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...
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.
Afterwards, we just need to select this new connection to add any table from this database.
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.
Remove the chn_adm3. Add it again, but filtering only the subdivisions within the Beijing municipality (the condition is "name_1" = 'Beijing').
DB Manager plugin
Let explore the nice DB Manager plugin. Open it by going to the Database menu.
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.
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.
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