QGIS & PostGIS : Tips & Tricks
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 351 | |
| Author | ||
| License | CC Attribution 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/69033 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language | ||
| Production Year | 2022 |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
| |
| Keywords |
00:00
Finitary relationField (computer science)Structural loadError messageDefault (computer science)Internet service providerSource codeAsynchronous Transfer ModeData bufferLocal ringIdentifiabilityKey (cryptography)Database transactionGroup actionDatabaseBlock (periodic table)Reading (process)Form (programming)Electronic visual displayGUI widgetComputer fontForceText editorData typeConfiguration spaceAttribute grammarZoom lensDrop (liquid)Customer relationship managementWeb browserEvent horizonRule of inferenceArtistic renderingMaxima and minimaExclusive orMessage passingRaster graphicsVolumenvisualisierungGeometryMaß <Mathematik>Scale (map)MetreInstance (computer science)Line (geometry)Thresholding (image processing)PixelAlgorithmWeb 2.0TelecommunicationMacro (computer science)Plug-in (computing)Scripting languageVideo game consoleReliefVertex (graph theory)Process (computing)Function (mathematics)Vector spaceBuffer solutionKeilförmige AnordnungLogical constantMultiplicationRectangleVariable (mathematics)Vector graphicsTable (information)Operations researchOracleBookmark (World Wide Web)Server (computing)Computer fileRotationSymbol tableSingle-precision floating-point formatEmbedded systemClique-widthTexture mappingAerodynamicsParameter (computer programming)Cursor (computers)Binary fileDigital filterQuery languageSet (mathematics)Total S.A.View (database)Relational databasePresentation of a groupCASE <Informatik>Decision tree learningTable (information)Key (cryptography)Shape (magazine)Local ringCustomer relationship managementProjective planeDatabase transactionBuffer solutionGeometryDatabaseDemo (music)Asynchronous Transfer ModeMultiplication signCartesian coordinate systemSource codeFunction (mathematics)Internet service providerStructural loadField (computer science)Associative propertyDistanceUniqueness quantificationSoftware developerLoginSoftwareDifferent (Kate Ryan album)Configuration spaceMessage passingCode refactoringLine (geometry)Point (geometry)Proper mapDefault (computer science)Computer configurationEvent horizonBitProcess (computing)Operator (mathematics)Client (computing)Front and back endsInformationWeb browserMultilaterationView (database)Row (database)Error messageType theoryGoodness of fitContent (media)Computer fileArithmetic meanOrder (biology)State of matterLink (knot theory)FreewareForm (programming)Right anglePerformance appraisalVideo game consoleElectronic mailing listSystem administratorDependent and independent variablesInsertion lossSubject indexingSoftware testingKavitationsblaseConsistencyFlow separationVirtual machineInflection pointComputer reservations systemReal numberAdditionResultantDebuggerSequenceElectronic visual displayCategory of beingServer (computing)Macro (computer science)Plug-in (computing)WordCase moddingGroup actionMoment (mathematics)PlastikkarteAreaOnline helpXMLComputer animationJSONSource codeProgram flowchart
Transcript: English(auto-generated)
00:00
Thank you. Yes, like you said, I am a QGSCORE developer and I work at Auslandia. And yes, today I wanted to talk to you about two software very much like Poggis and QGIS and how to configure each other to work perfectly.
00:21
So I'm not going to talk about all the features related to QGIS and Poggis because there are so many. So I'm going to focus on some main features and maybe some unknown features for some of you. So I'll talk about relations, PostgreSQL notification,
00:43
or to output your processing to database, or to manage your database from the browser, or to save the project in database. And then I will, if we add time, also I will try to speak about the new thing, SQL Logging.
01:01
So first of all, relations. So I'm going to take a use case to explain clearly how you configure your relation. When you design your tables, quite every time you are creating table and you are creating association between this table with primary key and foreign key.
01:21
So let's say we have airports and airlines and we want to have some association between airports and airlines, meaning that we have an enemy relations. That means that airport have airlines and airlines are installed in airports. And so we have two table and ports and airlines
01:41
and we have a third table, which is making the association between the two table. If we want to see the SQL beyond this use case, or if you want to have more information about everything I'm going to describe, everything is in the QGIS documentation, you have a link in my presentation.
02:04
So first of all, the easy way is, once you have defined every primary key, foreign key in your database, is to go to your QGIS to load all the layers. So the free one, airport, airlines, and airport and lines. And then you go to project properties, relations,
02:23
you click on the button discover relation and QGIS will automatically discover all the relations. So you see here that we have a relation, airport is referring, yes, airport airline is referencing airport and airport airlines
02:42
is also referencing airlines. Once you agree with what QGIS has discovered, you select all, you click the okay button, and now you're good, QGIS is aware about the relations. But there is still some configuration to do,
03:00
to have a really good user experience with relation in QGIS. First one is to enable and check the option, evaluate default values on provider side, because imagine that with your primary key you have a sequence, which happen quite often. When you create an item in QGIS,
03:20
maybe you've already seen this value, next, val, my sequence, and this value is evaluated when you commit on the QGIS database. But it's not possible to keep these values when you have a relation. Because when you have relation, QGIS has to have the real primary key, has to have a real number, one, two, three, four. So you can say the airport one
03:41
is connected to the airline four, et cetera, et cetera. So check this option. But it's still not enough according to me, because in the default transaction mode, the option that we see above, local edit buffer, you will have to edit all the relation content layer.
04:01
You will have to save it in the appropriate order, meaning that you will have to save the airport airlines before airport airlines. And so what happen if there is an error in the safe process? Well, you could find yourself in an inconsistency state.
04:24
So that's not completely really a good user experience that we have here. So there is another option, transaction mode, which is automatic transaction groups. And well, this one works differently.
04:40
It open a database transaction. As soon as that you click on the edit button, and everything that you are going to do are going to be done on the database directly in live. So the good things is that all layers from the database are edited at once. Cool things is that it evaluate triggers,
05:04
meaning that if you have some intelligent triggers that update the database, then you will see the modification appears directly into your QGs. So the cool things, if you have really smart triggers, the bad things about this mode is the long-living transaction.
05:22
So there is another QGs developer, which has list the different caveats between behind this option. But to sum up, the more important caveat, the more important drawback of these things that you're gonna lock your modified row, meaning that if you are modifying an airport,
05:42
well, if there is another user who want to modify those, it will be blocked. It increases, it will hang. So there is a timeout, but still, it's not really perfect, it's not really cool things. So we have a new transaction mode, which is called buffered transaction group.
06:01
It's new in 3.26, and it's better of the two words, meaning that all the modification are locally, they are buffered, and they are replayed only one on saved in the correct order regarding the relation and in one transaction. So it solve all the issues.
06:21
The only thing is that you don't have the trigger evaluation live. The triggers will be evaluated, but it will be when you commit only one. So now we have everything configured. We can go through form configuration. I'm gonna pass quickly on this.
06:40
Be careful if you have an relation, you have to switch the cardinality, because by default, it's one-to-one, so you have to switch with end-to-end. It's called airlines ID. I'm not sure about the label, what the meaning of this, but switch, and it will work.
07:06
And that's it. Yep. It's just a little demo of how it works. So we have the three layers. When I click on the edit button, everything is edited at once. And so if I open the forms,
07:21
you see the airports and the airlines. And so here, I just click it on a button where I can create an airlines associated to the current, to the selected airport. So I'm going to add an airlines. And so you see, it has been associated. So I have added Air France, I'm gonna add another.
07:42
So you can see the ID is directly evaluated. There is 15, so it has already been evaluated from the provider, from the Postgres provider. So now I am over with the relation.
08:00
I'm gonna talk about PostgresQL Notify. So the Notify is a keyword in PostgresQL that helps to trigger an event directly to the Postgres backend client. And so in QGIS, you can trigger a layer refresh on a PostgresQL event, meaning insertion, update,
08:23
deletion, truncate table, this kind of thing. How you do that? So you have a little piece of SQL. So you have to create a function. And in the function, you have to call the Notify keyword with the QGIS label, and you have a message.
08:41
I'll talk about this later. Here is written points updated. And so then you create a trigger, and you set, you call your function directly when there is an insertion, update, delete, truncate, whatever you want. Once you have done that in your Postgres database,
09:01
you go into QGIS, and you click on the rendering tabs on your layers. So here is my points layers rendering tab. And I'm gonna check the option refresh layer on notification. And then you have another option
09:21
when you could filter on the message. So here I just set points updated, which was the message that I write in my SQL configuration. So you can have different type of notification and refresh layer accounting
09:41
of different type of notification. And so, yeah, it works like this, just a little demo. So here you see that I have points. I'm gonna insert directly in the database the points, and you could see that in the middle of QGIS,
10:02
there is points appearing without any user operation. QGIS is refreshing itself automatically. What if you want to do not only refresh? Yes, it's possible.
10:21
And always a little bit of Piton, so sorry for the people who don't know Piton much well. Where you can put your Piton source code in a plugin, either in a starter strip, in some macros, or directly in the Piton console if you want to just test.
10:41
And you can do whatever is available from the QGIS API. So it's very cool. Here is a small example. I define a Piton function, and I call and I push a message into the message bar of QGIS. And just to do that, then you have to connect to the notify signal
11:03
and to associate to you on notify method. And then it does this. So when I insert a point, you will see that the layer is also refreshed, but you have, in addition,
11:21
you have a message which is displayed here, notify points updated, and that's pretty it. I'm gonna set another point, that's it. So you can do whatever you want. Could you use it to refresh other layer?
11:40
Yes, but don't do it, because there is everything already plugged into QGIS. It's called data dependencies. So the use case behind this is, some of you maybe have already encountered this issue, is you have a database where you have nodes and lines, and your nodes are on the intersecting lines,
12:01
and you want to refresh your lines when you move the node, and you want to refresh the nodes when you move your lines. So when to do this, the better way to do this is to use data dependencies, where you're gonna say, well, on my nodes, when there is a modification on nodes, I want you to refresh the layer lines, and you can do vice versa.
12:25
Just a quick demo about how to put processing to database. So you go, so, yeah. So here, so just here I'm creating a buffer.
12:42
I'm launching a buffer treatment on a geo package or shape file, I don't know, layer. I'm just changing the distance to have a meaningful distance. And so I'm gonna change the output here, just to save to database,
13:00
and then I can select my database, the schema, and a unique table name. The table name needs to, doesn't have to exist on the database. Once you do that, you have a proper QGIS data source, and you run the treatment, and everything is done locally, then it's pushed to the database.
13:23
Then I'm just gonna remove the layer and check directly from the DB manager if it exists. Normally it should exist. And that's it. So really cool thing, if you want to just load a shape file, load your package, put it in your database, refactor some field
13:42
before this kind of thing. Very useful. Quite recent feature is you can manage your database directly from the browser, and you can do many things.
14:00
You can create table, you can choose the table name, the different field, the type of field, the geometry type, the name of the geometry, colon, et cetera, et cetera, et cetera. Nice. You can also create, rename, and delete schema. Create, rename, and delete and truncate table.
14:21
You can add and delete field. You can export to a file directly from the database browser. You can also execute SQL, see the result, load as a new layer directly in QGIS, choose the unique colon, the geometry colon, and maybe some of you are wondering,
14:42
but what's the point? Those features are already existing in the pay manager, and does it replace database manager? Well, yes, but no, not at the time. There is a discussion, if you want to see the different discussion messages between QGIS developer
15:03
and user too, because a QGIS developer wanted to remove completely the database manager, and there is still some feature missing, some key features like creating, managing constraint, create a view from query, modify your colon. Our table is torization.
15:22
I'm not sure everyone is using this at the moment, and maybe if you are using this, maybe you should not and use something else. So it's a great presentation about CART yesterday. Maybe you should use CART instead of this. But yes, so for now, we cannot remove the database manager because there is
15:42
still some feature missing, but in the end, it will be the case. So someday the database manager will leave us, and why do we want to replace the database manager? It's mainly because it's kind of a separate application.
16:04
It's written in Python while the entire rest of the QGIS application is written in C++. It doesn't use the QGIS provider API. It's not that well tested, and it may be not well tested at all. It's kind of fragile. There is a lot of issue on the QGIS tracker.
16:23
So prefer the browser feature every time is possible, and that's it. Another feature is saving project in database. To enable it, you have to check the option although saving loading this project in the database.
16:42
Once you do that, you can go to your project save tool and choose to pose SQL, and you can choose your database, your schema, the name of your project, and you have a button where you can remove the project that you don't want anymore.
17:05
Just a small hint, if you have external files, you like maybe SVG file in your symbology, please be aware that if you have this SVG file in your PC in local, and you want to share your project with other colleagues, well maybe they won't have
17:24
the same file in their machine, maybe not at the same place. So it's better to embed directly the file into the project before saving it in the PostgreSQL database.
17:47
Last but not least, a very cool feature which appears in 3.26 is SQL logging. It allows you to see all the requests sent by QGIS to the PostgreSQL database,
18:03
and you can see the time the request spent, how much row has been returned by the request, the kind of things you can see also, the request which has been sent to PostgreSQL. It's very, very cool to debug it to understand
18:22
what happens between your project and PostgreSQL database. We have, it's very useful to me because we have a lot of clients who ask us, well my project is too slow, it spent too much time, why is it slow? And to be honest, in 99% of the time, it's because of badly written requests,
18:43
badly written view, and missing indexes, missing materialized view. So it's very useful to debug this because before we have to say, well could you enable the log on your PostgreSQL server, and maybe the guy doesn't have the administration rights to do so. Also, it's very, very useful.
19:02
You can go in a view panel's development, debugging tools, and it logs all the requests, the select, the insert, the update, everything related to style, everything. And it's not the purpose of the presentation, but it logs also HTTP requests. So if you are opening a WMS layer,
19:20
you will see all the requests, and it's also very useful. And that's it for me. If you have any questions, I will be glad to answer it. Thank you.