Spatial Database: Connect to PostgreSQL/PostGIS Through QGIS

To visualize datasets in PostgreSQl/PostGIS, you can connect the database through a GIS software, such as QGIS or ArcGIS. Here shows general steps to access and visualize spatial layers (tables), as well as query results, which is an effective way to examine your datasets, with QGIS.

1. Open QGIS, select Layer -> Add layer -> Add PostGIS layers, it should open the interface to connect a PostGIS table.

2. In the dialogue, click “New”, it should open the dialogue to configure the PostGIS server, and database (nyc1). Enter relevant information in Step 3, click Ok.

3. Back to the dialogue, click “Connect”, you shall be able to see all tables in the database (e.g., nyc1). You may select any table, click “Add”, then you can load it into the QGIS.

4. If you want to manage PostGIS database similar like pgAdmin, and visualize SQL results (e.g., buffer, intersection results) in the QGIS, click ‘Database’ drop-down menu, and open “DB Manager”, which should have similar interface and function like PgAdmin.

5. Once open “DB manager” interface, you shall be able to see several spatial database systems in the left panel. Click “PostGIS”, you should be able to see all connected PostGIS tables displayed (Step 2).

6. Select “SQL window” (Step 3), you should open the interface to enable to enter you SQL statements. In this example, you are randomly select 10 streets records “select name, geom from nyc_streets limited 10”.

7. Click “Load as new layer” (Step 5), select the “geom” attribute to visualize, click “Load now”, you should be able to see these selected streets are visualized in the QGIS map area.

8. You can also load and visualize more complex SQl queries. For example, you can generate 500 meter buffer for these streets, by entering “select name, st_buffer(geom, 500) as geom_buffer from nyc_streets limit 10” , and then load them as the map layer (Note: in the geometry column, you need to use the new buffer geometry “geom_buffer” as visualization column)


Please check this link for a tutorial of connecting to PostgreSQL/PostGIS from ArcGIS.