• Blog posts

    Bio

    By David Crowther

    QGIS___Open_and_Query_PostGIS_Data_-_1.JPG

    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.

    QGIS___Open_and_Query_PostGIS_Data_-_2.JPG

    The PostgreSQL Execute and Load SQL Tool will open.

    QGIS___Open_and_Query_PostGIS_Data_-_3.JPG

    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.

    QGIS___Open_and_Query_PostGIS_Data_-_4.JPG

    If the query runs successfully in PG Admin, you can then copy the syntax to use it within the QGIS tool.

    QGIS___Open_and_Query_PostGIS_Data_-_5.JPG

    If you now Run the PostgreSQL Execute and Load SQL Tool, a new layer called SQL Layer will be loaded into the map canvas.

    QGIS___Open_and_Query_PostGIS_Data_-_6.JPG

    If we check the Attribute Table, we can see the query has worked because the IMD_RANK values are all greater than 30,000

    QGIS___Open_and_Query_PostGIS_Data_-_7.JPG

    … and the record count matches the same as the results in PGAdmin.

    QGIS___Open_and_Query_PostGIS_Data_-_8.JPG

    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.

    QGIS___Open_and_Query_PostGIS_Data_-_9.JPG

    Once tested in PGAdmin, you can then copy and paste the spatial query into the QGIS tool….

    QGIS___Open_and_Query_PostGIS_Data_-_10.JPG

    .. and once its ran, the new SQL Layer will only load the Hospitals within 10km of London.

    QGIS___Open_and_Query_PostGIS_Data_-_11.JPG

    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.

    QGIS___Open_and_Query_PostGIS_Data_-_12.JPG

    QGIS___Open_and_Query_PostGIS_Data_-_13.JPG

    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.

    QGIS___Open_and_Query_PostGIS_Data_-_14.JPG

    Now your users can simply load data by choosing Layer > Add Layer from a Definition file > and choose the .LYR file that you saved.

    QGIS___Open_and_Query_PostGIS_Data_-_15.JPG

    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.

    QGIS___Open_and_Query_PostGIS_Data_-_16.JPG

    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!