Dynamic GIS Layers with QGIS and Spatial Databases

okay hi there here's carsten uh talking to you from seattle and i'm happy to be here and i will talk about dynamic gis layers with qgis and spatial databases so maybe let's talk a little bit about myself and how i came about to talk about this topic so myself i'm a geographer by background and i have been working in many different locations i'm originally from germany i've been here in seattle and had my own small consulting business for the last 13 years and i've seen many things from environmental you know companies non-profits government and also in universities so i've been working with open source for quite a long time you know i have been working with map server open layers and post gis since 2008 and then i have experience with the sv stack and came to use much more qgis and so lately i'm almost exclusively using that so how did we get here so how did i decide you know talk about that it wasn't a project for a client or something but as a single consultant you always need to kind of um advance yourself learn a little bit and so i was inspired by what you see inspiration for the talk there was a blog post and a video by tim sutton one of the main programmers or in qgis and there were talk he was in that he was demonstrating how to do dynamic queries in qgis using a database view based on another layer or actually a query layer that you can do with the db manager and then i found you know reading a little bit lately about hostilis in a little bit older book by paulo corti whose yes cookbook i found some python code how they were using python from within qgis to access actually weather data from the um open weather map and these two kind of led me to investigate a little bit more and i came up with this and i think i have something pretty cool to present and you know the topic is you know spatial database and dynamic layers but when i was investigating that i also looked at more simple things you know i came across a geometry generator and you know adding data such as remote apl calls or geojson or such so because of the shortness of talk i will not talk about it but i will jump between my slides and the live presentation and if you go to my github account which we will post into that link in the youtube later you will find you know these project files data and in sql and functions that i produced for that you can replicate this you know it could be a template for other calls so basically we will look at db manager connections and database views and then we will also look at database views in a different way there is some specific keyword it's called lateral so there can be a little join in pusher since version like 9.3 that's available and you can also use python within the database it's called pl python we'll take a look at that and how i created a view that will actually join an existing data set in a postgas table with a sub query which is you know calling a function and that function actually calls remote data and we'll have an example about weather data and temperature and then earthquakes so let's go and i will skip these first seven slides and you have that you know in if you download it from the github and i already you know just to recall one more time so we will do dynamic queries with db manager look at post jazz and specialized database views and then the last thing will be that we will be looking at combining postgis with pl python functions on an external api request if you look at db manager which i will show you know mostly you know in the qgis you can connect to either you know relational databases there's a built in connected to oracle spatial here you can see if you open this under db manager you will see you can connect to geo packages you can connect to spatialite files which is like a special version of the sqlite um database and you can connect to postgis just one for one node for people who haven't used it you can make con connections to gu packages or special files right from the db manager however this postgis you still need to do that in the qgis browser so let's um take a look uh if you work with spatialite i also wanted to note there is an external application that has been along around for a long time is called spatialite gui you know that is there's a link down here if you download at least with powerpoint the link is active i think in the pdf it doesn't work so but all the links are also on my github page and let me just um show you uh this is the github page when i go to my dynamic layers with qgis to find all the information here and you will also find these links here and you will find the code for post gis functions right here just want to show that okay let's go to qgis so how do you access the db manager basically you go here oh sorry i switched my user interface to german but i think you will get what i'm talking about so basically you can see connected databases to your package postgis i connect it to a remote database which we will be using and you can see the spatialite or actually that's non-connected but that's fine so you can create actually a query here on this data if you select um let me just connect to this tracks and let me just check where i got that so that should have been there i connect with my db manager and say add connection and then basically you navigate to where that file is and uh so we have to navigate here again because it doesn't let me [Music] so i go to my projects and just classes sorry that takes a little bit i had to switch my laptop because it didn't work and that's the reason for this trouble so i can connect one here so just want to have an example that that can be connected so if you look and here you can see the table you can click on that and actually make a query so you could say select star from and then put in point flooding if i do that then i get these data and you could also have that i'm not sure if people are aware of this you can load that as a query layer if you say that there's two things that you need you need a unique id and to lead a geometry column so i could say i want that query one and then say load so that should give us a query layer um sorry i think that has the wrong projection and that actually should show up i don't want to mess with it with yet now too much but that's how you can learn i mean that's how you can create a query layer so i just did that for the seattle track so if i have here a sqlite file connected i can basically run a query and i can just use this one instead since the other one was messing me up in db manager i go to my um spatialite file and i need to connect to the tracks and then let me just do that if i run that query that i was about to show which is this one select star from tracks where population in 2010 is bigger than 7700 it would select just that one tracked i just did it that way and then you have you know an option to use any of the functions that the spatial database has and if you have postgis you can wrap around you know all these functions you have for example if you wrap around the geometry function st buffer and then use 900 meters or 900 feet in this case i do minus which is an inside buffer it gives you basically this that you have this inside buffer so i just wanted to show this simple example before we go to something more complicated so let's maybe go back to um my slides for a second so i don't want to talk much about post gis but basically you can store spatial objects in here and you have a whole lot of spatial operations you can use via functions then i showed kind of what wasn't really working here is in the slides when you have connected the set of tracks the query that i could buffer inside 900 feet the geometry and then select just one of the tracks was which i did by population from census 2010 bigger than 2 um 7700 so that was an example i wanted to show so if you were um to add something like a database view a view is like a temporary you know glance at the data it's not a temp it's a not a permanent table but it's an sql query that can be stored and you can retrieve data from one or more tables so if you want to use any of those in you know qgis you should add a unique id and you can do that you know generate one if you don't have one by that sql construct row number in brackets sgid so that is something to keep in mind that otherwise it will not correctly work often so i wanted to show you now one of the three examples we have one is you know using a spatialite file and i was creating two query layers so what we have here let me just show it basically what it does we have like one location file in points and then we have two views one is using you know the geometry of the point and buffering that by sd underscore buffer around it you can use similar functions in spatial adjust like in post gis and then we have one where i query you know those you know circles that were created in a sec in the first view by intersecting them with the seattle road network so there's two data sets one's the point and one is the road data network so let's take a look at that and make that run so i have this example here when i go here and switch off the open street map i could go right here and let us at these points for now i will first delete those points so that we start with zero save it and then refresh so what i have here is the road network so i will digitize one point and it takes as a parameter the radius i want to run that buffer around so i'd say 200 then i make that's i save that and once the day this is updated it will create that buffer layer which is this one so basically take a short look at this what it is maybe you cannot see that so quickly but it says select point and then an sd buffer around the geometry and as a radius sticks this field radius that i entered so we can have a variable radius and then that second thing here is a selection it's a view that says select from the points and the roads where it does intersect these circles and if i update that map those roads you know i can take off the point i can take out the roads and you know this is the buffer sorry and this is the intersected road so basically from that point you could dynamically create or we actually dynamically created this just to uh you can do multiple ones but in terms of time i need to go to the next one so you you have that all in a github account and can download that example later to try it out for yourself so now we are connecting to a post gis database and let me just go to what's happening here will go so let's explain that from the beginning we will use a custom peel python function and an external api request and there are certain prerequisites that you can run that you need a lateral join in the view so that's available from postgas 9.3 on posterior s extension installed python 2.7 or higher or you could also use python 3. my example is python 2.7 and you need to have the peel python language installed so so what will happen in this we have actually an open weather api where you can get weather data from an external source and i will wrap that into a function in postgres when you know that is python that will actually run a query based on the point i added in a location layer and then i have a um i have a view that then will display i mean that will the view is basically a query on the point you know running you know an api call to open weather map getting the closest weather data and displaying that from the view as i added a view as well into posterior into qgis with the actual current temperature in degrees celsius and then also calculated the distance from there so let's let's just let that show you can go put back to the slides to see like more explanations how that work but i think it's better to see what it actually does so if i go to um this is a remote server so hopefully it won't be too slow i will click on my okay let's go to this point here so let's just go somewhere this api is available for usa only so if i go this is my post.js point layer i will click on editing one adding one here in arlington so now the remote server i have to give a unique id and put a name so i say okay so this is already showing up with the id so what i did you need to do is now save that so i have two views here and that's i need to zoom in more to make this a little bit more visible so first of all the first view is what i created with spl python functions i will show that in a second and basically i can show that in a database at create you know it you know did that call found this information and added it to the view and then also used uh st distance functions to calculate the distance you know from that point and then i have a second view which takes these two points and generates a line that shows here up in red we only have about two minutes left i just want to let you know that okay so basically i would say since we're running over time you can go to the github page and check out these examples the second one is about yours earthquakes let me just show what i did here for um that's a little bit slow because of the remote server let me just go back to the slides and basically i wanted to show you this is the weather api where you can get an http request and get jason back this is the request you can do you can do that you know from all the examples i have on github basically this is how the pl python function works so basically the first and last line is a wrapper inside is python and you show you see where it creates a call for that api and then basically what you see is you know first example of meta map gives you that json back and if i create that function that you've seen in black before i can run for example select get weather all as my function and get back that result down here and then if i wrap that in a view which i added to qgis then it is what you have seen before so basically the view looks like this everything in red is what we have from these points the orange is you know the digitized chorus is in orange the red is you know just from postgres the geometries i generated and the green is what you get from the api call so for now since i ran out of time i will i want to leave time for questions and really you know this can be much better understandable if you go to the github and try it for yourself and get in contact with you if any of these examples don't work thanks a lot for your attention and i'm happy to answer any questions carson that was great um it's making me want to step up my python game a little um so there was one question is there a spatial light gui for mac i don't know if you're able to answer that question so i'm not aware of since i'm not a mac user myself unfortunately i don't know you can do most things now in the db manager you know previously it used to be that you couldn't create views and such you can do that but especially i go as far as i know is only for linux and there's a source code if you get it to build on your mac you can do it i don't know if there's pre-packaged ones last question just just really quick um what about the speed to load views database views typically if it's a very large table things start to get a little bit slower have you have you experimented at all in trying to speed that up well the the database view also uses you know the the unique key or the indexes in your original table so if you set you know your indexes in those it should be fine you know if you have this on a remote server and we do api calls you know api calls only happens if you digitize a new point and then it creates it after that yeah it still does that api call because actually what you will see in here is always the current temperature uh at that location so yes it can be a little slower especially in this case well thank you very much that was really interesting i'm sure there's gonna be a lot of people flocking to your your github repo to take a closer look at this so thanks for participating in the conference okay thanks a lot

Channel: QGIS North America

Views: 1,936

Keywords:

Id: 92EC-cZWEUc

Length: 23min 0sec (1380 seconds)

Published: Sat Aug 01 2020