March 22, 2022 By David Crowther
Question:
How can I open data from my PostGIS database?
Answer:
Why not use the PostgreSQL Execute and Load SQL tool available in the Processing Toolbox!
From the Processing menu choose > Toolbox > open the Database section and choose the PostgreSQL Execute and Load SQL menu option.
The PostgreSQL Execute and Load SQL Tool will open.
Before we run the SQL query, let’s ensure that our SQL query runs ok within PGAdmin.
In this example we are going to load features from a census boundary layer where the Index of Deprivation Rank is greater than 30,000.
If the query runs successfully in PG Admin, you can then copy the syntax to use it within the QGIS tool.
If you now Run the PostgreSQL Execute and Load SQL Tool, a new layer called SQL Layer will be loaded into the map canvas.
If we check the Attribute Table, we can see the query has worked because the IMD_RANK values are all greater than 30,000…
… and the record count matches the same as the results in PGAdmin.
So, we can now utilise the power of PostGIS and SQL to load features from our datasets that meet specific criteria!
In the next example we will use ‘Spatial’ SQL queries to load data from PostGIS.
Try this one – where we will select only the Hospital features that are within 10km of a coordinate in the centre of London.
Once tested in PGAdmin, you can then copy and paste the spatial query into the QGIS tool….
.. and once its ran, the new SQL Layer will only load the Hospitals within 10km of London.
Or maybe we could load all the features from one PostGIS table that intersect another PostGIS table. e.g. load the schools that fall within the London Borough of Havering.
Finally, we can save these PostGIS query layers to open at another time.
Simply > right click on the SQL Layer > choose Export > Save as Layer Definition File > and give the layer a name e.g. Kent_Schools.
Now your users can simply load data by choosing Layer > Add Layer from a Definition file > and choose the .LYR file that you saved.
Opening the Layer Definition file will re-run the query of your PostGIS data, to add the schools into the map that fall within the County of Kent.
The PostgreSQL Execute and Load SQL tool is a great way to open (and query) data from your PostGIS database, running queries on the fly so you don’t need to create multiple versions of those datasets as flat GIS files!
