QGIS and PostgreSQL/PostGIS as an enterprise solution: 5 features everyone should use

hello my name is cliff patterson welcome to my talk entitled qgis and postgres post gis as an enterprise solution five features everyone should use a little bit about me i've been a supporter of all things open source gis for a number of years i've been using qgis since about 2007. i'm currently a senior gis consultant at psd a canadian company that makes asset management software and budgeting software now within the company i'm the product lead for something called citywide enterprise gis which is a platform as a service uh designed for organizations that want i open to migrate to open source gis um so by day i migrate organizations to open source gis i train staff and i supply professional gis support the question i get asked a lot uh and the subject of this presentation is can open source gis be used as an enterprise solution well the answer is yes um with the the requirements in my opinion for an enterprise gis are centralized data uh the ability to manage users access control to promote data integrity uh you need to be able to automate tasks you mean you need to have default layer styles standard map templates you need map services for both internal and external users metadata metadata and also metadata very important within an enterprise environment likewise data licenses are incredibly important you need to decide how you want to share your data with the with the outside world um so over overarching everything is governance you want to have governance through policies or some sort of strategic plan within your organization so today i'm going to be using data from the city of windsor's open data portal i'll be using the electoral wards the parks the polygon layers the fire stations the hospitals and the police stations and the software that i'm going to be using for this demo will be postgres version 10.12 uh post gis version 2.4 qgis 3.10.4 the current ltr and windows 10 version 19.03 so let's dive in so the first feature i want to discuss is user management any any enterprise gis system requires some sort of user management you want to control how your users have access to this to data you want some users to be able to edit data you want other users to only be able to read data you want some users who you might want to grant them permission to edit just one specific layer and you might have an admin user who you want to give them permission to really edit anything to add data to any schema and to have full control over the database so fortunately we can do this through postgres and qgis will reflect whatever user management is in place so specifically if some if you grant a role the ability to edit that editing button will be available within qgis to that user when they load a layer so what we're going to do is set up the database schemas and upload data we're going to create roles and grant access to the database we're going to create users and grant membership to these specific roles we're going to grant access to each schema for each role we're going to grant read-only permission to view each table and we're going to grant editor permission to edit just the parks layer so let's dive into this i'm going to show you a bunch of the statements that you need to run in order to build this of course you can do it through through any program like pg admin or dbeaver or even run this through the database manager within qgis um so the first one we're going to create the schemas we're going to create a boundary schema map parks and rec and services after that we're going to create two roles we're going to create a read-only role and we're going to create an editor role these are no no login roles right because we're going to actually give users the ability to log into the database and they'll be members of this role third we're going to allow the role to connect to the database we want anybody who's a member of the read-only or editor roles to have the ability to connect to the database after this we're going to add our data we're going to add the electoral rewards to the boundaries we're going to add parks to parks and rec we're going to add fire stations hospitals and fire stations and police stations to the services schema switching over to qgis now we can see that we have our database connection set up here and i assume of course that you already know how to connect to a postgis database uh and you have somewhere where you can build this database and play along um so we have all the schema that we said we're gonna build create including the public which is default so within the boundary schema we have those municipal electoral ward boundaries within the parks and rec schema we have the parks and within services we have fire stations hospitals and police so next we're going to add users to our database and we're going to grant certain privileges to both the roles and to the users who are members of these roles we're going to create bob first bob is going to be a a member of the read only role and we're going to create sarah who's going to be a member of both read-only so she'll be able to see all the data within the database but she'll also have the editor role assigned her as well so she'll actually be able to edit certain tables next we want to grant permission so we want to grant access to each schema for the read-only role we want to grant select on the tables for both for all the tables that we've added to the database and we want to grant for the editor it's a little different we want to grant all on the sequence so that this will actually allow the user to edit the table and specifically since the id is going to be within a sequence you want to be able to add another id for new rows and you want to grant all on the table parts to editors so you want the editor to be able to pretty much do anything on that table so let's take a look in qgis how this works for each user at this so i'm in qgis now and i've set up the two users and i also have an admin user i'm currently logged in as the admin user so if i load any layer if i load the parts layer we can see that the editing button is available and i could edit this layer if i load any other layer i also still have the ability to edit this layer but this is what we what we don't want for all users we want some to have read-only access so what i've done is i've switched now to bob so now you're going to be able to see what the the access to the database looks like from bob's perspective so when we add the municipal boundaries layer to the map the editing button is not there the parks layer which should be editable to any editors again is not editable by uh bob so we've we've we've created now a read only role and anybody we add to them so if we add new users who should just be able to read the data make maps even run analysis within qgis but not save to the database or make any changes whatsoever to a layer we just have to add them to that role um the the read-only role and these are the capabilities that they will have but let's switch over to sarah and see what it looks like from her perspective okay so i have now switched over i've logged into sarah and i've added all the layers to the to the map we have the municipal elec election ward boundaries again those aren't editable fire stations are not editable neither are police stations nor hospitals but if we go into parks you guessed it we can now edit this layer and we can create a new polygon which will create a new row within the database we can save that turn off the editing session and we've created a new uh polygon and again because we've granted full access to this data set um sarah is also able to delete this polygon so it has a permit the user has the permission you can lock that down too you can give users what seems like full control they can edit they can modify they can update but prevent them from deleting a a row and that's a really powerful tool and that's pretty much it we've created two users we've created a read-only role we've created a role that can edit and we have an admin role that sort of has control over the entire database so the second feature i want to discuss are trigger functions trigger functions are powerful this gives you the ability to automatically populate certain fields within the database so what we're going to do is we're going to add a few new fields to the parks table and we're going to create a function that will measure the park area identify the ward in which the park is located determine the name of the closest police station hospital and fire station calculate the distance to the closest police station hospital and fire station populate and populate the metadata fields that we're going to create so first we're going to create a whole bunch of new new uh columns in the table we're gonna create one for like area square meters ward um all the nearest the nearest police station nearest uh the actual name of it and the nearest police station in distance uh and we're also going to create all the metadata fields so the date created notice we're gonna use a time stamp and we're going to set the default to now and we're going to have a date created and we're going to use the current user function here and then date modified modified by that will be populated by a trigger function uh and then we're going to alter the table we're going to add an additional column called status and we're just going to set the default one and one is basically going to mean actual park um so for the trigger functions there's two parts to them the first is the function itself the function basically just you can create a few different queries within within the function to populate specific fields so the new area square meters we're just using st area here we're casting it to numeric we're wrapping it around a round function uh setting it to two decimal places and this will calculate the area in square meters to two decimal places and likewise for ward we're using a simple st intersects on the centroid of the new geometry that we're producing um in the parks table and we're intersecting that with the uh ward boundaries but the second part of a trigger function is really the trigger itself so before insert or update on the parks table for each row we're going to execute a procedure and what procedure well we're going to execute this function now if we establish just this within the database this would trigger um both the this would would calculate just the area square meters the ward all those other uh columns would not be populated so we want to add those and for the nearest police station we're going to do a sort of nearest neighbor query here based on the distance in meters uh and we're going to figure out which station is located closest and we're also going to take that exact that distance uh so how close is that that station in meters uh to the new park that we've we've created uh and we're also going to modify we're going to um populate the date modified field with now which is the current time and we're going to populate the modified by field with the current user lastly if you want to update every single row within your database you just need to run a simple update and set something like the area square meters to zero and that will just automatically populate your entire table and then you will be ready to go so in pg admin four um i'm on the parks table right now you can see the all the new um the new columns that we created for the nearest police station nearest police station distance meters nearest hospital and so on as well as the sort of metadata fields that we attacked on at the end and we can also see that there is a trigger function so if we go to the um within the tree here we can find the parks trigger and this is exactly as as we showed it in the in the presentation uh but now we want to see this entire function so this entire function will be calculating the area the ward nearest police station nearest police station in meters and then we're doing the same for hospital hospital distance nearest fire station nearest fire station in meters and then at the bottom these metadata fields which will be populated so this will all uh this is all in the database so if you ever want to modify it one thing that's really nice is just open it up go into the properties go into definition and you can just modify the entire function here press save and the changes will take place so now let's take a look in qgis so back in qgis i'm logged in as sarah so i have that editing privilege and i'm going to click on the parks layer and i'm going to start an editing session i'm going to create a new polygon so i'm going to establish a new park right here and we see here that none of the fields are are populated the trigger function actually is triggered on save and when you commit those changes to the database so we're going to give the park a name and this is going to be of course cliff park and we're going to press ok i'm going to press save and i'm going to turn off this editing session right now and i'm going to identify this new park that i created so you see that cliff park now we know the area in square meters we know the ward in which it's located we know the nearest police station is a collision reporting center which is 1.5 about 1.5 kilometers away and same thing we know where the closest hospital uh closest fire hall and we also have this metadata these metadata fields calculated and we know that sarah created it at a certain time and we know that sarah modified it at a certain time which is the exact same time um so one of the things we do it is listed as status one but we know that's supposed to be uh established you know real parks you want to change this to a two because this is a proposed park let's press save and close this so now um we will notice that that when we click on when we identify any of the other parks we can see that they're all populated and that's because we ran that update function already which updates every single uh park within this table now default styling is the other feature that i want to discuss for any enterprise environment it's probably the most simple one to implement within qgis but it is very very important you can create default styles for layers by simply saving them to the database and what it does is it creates a new table within the public schema and that stores all the styles that you associate with specific layers but you can so you can save actually additional ones you can have a default style and you can have a few uh non-default styles so i'm going to go into qgis and show you how to do this exactly and we're going to work on the parks table and we're going to also work on the ward boundary table so back in qgis we want to create a default style for this parks layer um to do that you go into properties change the style in any way you want we're going to create we're going to classify categorize the layer based on that status field so we're going to take away this this last one and we're going to turn this into let's say a nice forest green i'm going to turn this just say a nice pink um and we're going to say we know that one are our actual um our actual parks and two will be our proposed parks so now that we have this style we want to save this directly into the database so to do that go back into the properties here where it says style save as default you have two choices if you save it in your local database that will just be visible to you when you're using your your qgis if you save it into the data source database that will save it directly into the database now if we remove this layer start a new project and add the layer to the data to the map you're going to see that by default that default style loads this is really a powerful method to use within qgis and within any enterprise environment okay i know i said that trigger functions were my favorite uh but my other favorite is form data input i think this is such a powerful feature within qgis if you haven't used it much i'll give you a little brief glimpse of what you can do with this but read the documentation look for some additional tutorials online it is such a powerful feature uh and it and it's great within an enterprise environment because it helps to maintain the integrity of your data you can make sure that any user within your organization is entering data in the exact same way so you can create forms to facilitate data input that contain things like text inputs for single or multiple lines numeric inputs check boxes drop down selections date pickers you can create a tab interface you can hide certain fields and you can also prevent editing of certain fields via the form so let's take a look in qgis and i'm going to show you how we're going to do this for the parks table okay so back in qgis we want to create a form for the parks layer so to do that you go into properties and you're going to notice that there's a tab here called attribute form by default it auto-generates every field is visible we want to change that to drag and drop designer what you can do is you can sort of remove certain fields that you don't want to appear on the form and you can create different categories the first category will just be park info park info and then we're also going to add nearest and we're also going to add metadata so we have these three categories so for park info we're going to grab the first three fields for metadata we're going to grab these metadata fields let's also put status within the park info and all these nearest fields we're going to move them to the nearest so now we have these three categories of data um but one of the other things we want to do is the status we don't want to just just show up as one or two there are these widgets and you can actually do them for any field and i'll show you quickly how this works we're going to create a value map so we know that one is actually a an actual park and two is a proposed park right so if we press apply and take a look at this let's see what this looks like so if we click on cliff park and opening an edit session now we can see proposed or actual now if we change this this actually changes the integer value within that field from either a one or a two but the areas here the ward and the nearest we have all the nearest fields and the metadata fields it really is a powerful feature um this allows you to maintain data integrity within your organization you can have a series of drop down menus everyone will be using the exact same layer the next important part is to go in and actually sorry open up the wrong one to go into the parks and to save this as the default style so you want to over overwrite the existing default style and this will say are you sure you want to change it yes i do press ok now if anybody loads this layer within your organization they will see that form when they either identify or edit this layer really powerful feature within an enterprise environment so the last feature i'd like to discuss is the ability to save qgis projects to the database um you're able to save projects to a specific schema in the database but there's some tricky parts to this is that the first is you have to grant permission to the read-only users to view the project especially if you've created this as an admin the only users who will be able to view that table or the table that contains all the maps are the ones that have permission to to view it of course now you also might want to grant permission for editors to be able to edit that project so i'm going to switch over to qgis and show you a little bit of how this works i've created a very simple map it has all the layers it has the municipal electoral ward boundaries the fire stations the hospitals the police stations and of course the park boundaries um and we have all the same styles that we've created so what we want to do is we want to save this as a project within the database and we want to save it here within the maps schema so that anybody within our organization will be able to load this project so if we go to file save to postgres we have our database here we tell it that we want to save in the maps and we want to call this our park project and press save so now if we refresh the map schema we can see that park project is available within this database so we can add it now i'm just dragging dropping it into into the map and it's going to load all the different layers and it's going to load the map as it was now this is really neat because other users can actually view the same map and they will not have the same capability so one of the things that's neat is that if another user loads it let's say i have editing privileges but bob doesn't bob will not be able to edit layers because it loads it using their credentials right so whatever restrictions are in place for that user um in terms of access to data those will be enforced even as they load this project so this is a really powerful feature but i'm going to show you a little bit in in pg admin just where this table actually exists because one of the features that doesn't exist is you can't delete one you can open the project you can extract some symbols you can see the file properties but you can't actually delete a map after you've created or a project after you've created it so i'm going to go to pg admin i'm just going to show you where this lives so in pg admin you can see that there is a maps schema of course and there is one table i'm just going to refresh there's nothing else here so the schema is actually called qgis underscore projects now one of the things here is you can see that the owner is postgres which is the sort of admin user within within postgres but we haven't granted access so you'd actually have to add grant access to this table for other users uh within your organization but for now i just want to show you what the table looks like and if we open this and view the entire table we can see that the name is park project which is the name i established there's some metadata associated with it and then there's the content which is actually just a binary binary data now if you do want to delete a a project that you've saved you have to do it here through pg admin so i would actually just delete this yes i want to delete it it's gone so now if i switch back over into qgis and i'll press new just to make sure this is closed um if i press refresh now there is no uh map or no project within the maps schema so again a really powerful feature something that you can use throughout your organization what i often do is i'll i'll use it for creating a base map that everybody within the organization can use and then i might create something like an asset management map which maybe the only the asset management team might be using um and and of course we have those different capabilities right we can give the editor the ability to actually edit the map and we can give specific users within the organization the ability to both create and update maps so we've come to the end um i'll take a few questions after this but i just want to give my info um if you want to get in touch you can reach me on twitter at cliffpat uh you can email me at cpatterson.psdrcs.com and i will not read all of this but um all the the sequel that i used to create this database including a few other things like granting privileges to the map uh the map schema and the uh the map file that's or the project file that's within that all that is going to be on uh github uh you can go now and and grab that if you'd like to take a closer look at some of the statements that we use to create this so thank you for your time i really do appreciate it this has been a wonderful conference to be part of and to be a co-organizer on it has been a lot of fun um and thanks for all who attended and who've asked questions and participated it's really been a fun experience thank you all right it looks like we're actually pretty tight on time here so i would say if you've got one top question you want to answer from the chat cliff because i know you've been looking at it yeah it happened i'll sorry for some reason my camera's not turning on oh there we go um yeah the last question actually just popped up there about value relations i actually was in the same situation i loaded a whole bunch of lookup tables associated with one layer and then i realized that whenever i load that actual layer it's not loading the the related fields um so there's two solutions to that one you can make a materialized view for your table and then just just do a join with all those other tables but then of course you can't edit it um the other option is to just move over and start using a value map and then those values will actually save to the form itself so that when anybody loads it so just like i did in the tutorial anybody who loads it they'll see the same drop downs in that list um i i heard i think i remember i posted something on on the qgis user group and i think somebody said that that's a feature that's coming i don't quote me on this but i think eventually maybe one day you're going to be able to drag a layer and then associated layers will actually pop in or i'm completely dreamy well thanks for the info um we're gonna have to cut this off so we can get to the next presentation but i'm sure cliff will be happy to answer more questions in the chat

Channel: QGIS North America

Views: 3,749

Keywords:

Id: TEL1FIp-PSE

Length: 24min 15sec (1455 seconds)

Published: Sat Aug 01 2020