{"id":1726,"date":"2015-07-29T12:20:30","date_gmt":"2015-07-29T10:20:30","guid":{"rendered":"https:\/\/www.opengis.ch\/?p=1726"},"modified":"2020-04-29T16:05:41","modified_gmt":"2020-04-29T14:05:41","slug":"postgres-expression-compiler","status":"publish","type":"post","link":"https:\/\/www.opengis.ch\/it\/2015\/07\/29\/postgres-expression-compiler\/","title":{"rendered":"Postgres Expression Compiler for QGIS"},"content":{"rendered":"

Performance<\/h1>\n

This project is all about performance of QGIS with a postgres\/postgis database.
\nA lot of people have QGIS connected to postgres\/postgis (if you don’t: it’s a great combination in the open source geo stack). Databases are really optimized for querying. They keep indexes of geometries to be able to find them faster, they keep indexes of attributes to filter faster – and finally they often run on powerful servers.
\nQGIS tries to be smart enough to make use of this additional information. So whenever a map canvas is being rendered it asks the database only for features in this area. But if it’s about other attributes, QGIS filters by them locally. This means if you have a list of all the cities in the world and only want the capitals, QGIS will get the list with all the cities from the database, check for every city if it’s a capital. If it’s not, it will discard it, if it is it will use\u00a0it. That’s terribly slow and postgres itself could do it much faster.
\nThat’s where the postgres expression compiler comes into play. It is able to compile QgsExpressions (an SQL dialect) to postgres filter queries (another SQL dialect) and sends the converted SQL to the server if it is able to create matching SQL. If not it will gracefully fallback to the (slow) local evaluation.<\/p>\n

Where it can be used<\/h1>\n

When building enterprise scaled applications dealing with large amounts of data and only requiring a subset of it is a really common task.
\nLet’s start with some cartography. Think of a small country-side village with some beautiful old houses and a lovely small river making its way through the nearby forest. This village is in a bigger district which has some other cities (some of them more, some of them less worth a visit) and this district is just one out of several others which together form a whole kingdom. One day the king asks a map for him to be drawn of his kingdom. And the man who has been tasked the job settles out and finds our nice small village. It’s a hard job for him to decide how to visualize it. Should he draw all this beautiful old houses? And the river? And the forest? He tried hard to do it but he couldn’t make the lines thin enough to fit everything on that piece of paper on which he had to paint the other 258 villages. After hours of trying he decided to put a small dot for the village there. He could not even label it with the name.
\nIn cartography the task of going to a smaller map scale is called generalization. There are various possibilities to help with this task, an important one is: leave out things. While QGIS cannot replace the whole generalization task which the king’s cartographer had done, it can still help you as you zoom out and in on the map canvas and show certain features only at certain scales. To configure this, you can use the rule based renderer<\/em>. This let’s you define combinations of filters, scales and symbology. At small scales we will usually have only a subset of features being rendered. And this is where the expression compiler comes into play and helps you to not even let the server send them through the network but already filter all the small cities out before sending them to QGIS.
\nOn an example real world application (the QGEP project) the rendering speed on small scales has been increased by a factor of 3. This number is highly specific to this particular environment. The database server runs on the same machine (network latency, network throughput, CPU and memory shared between QGIS and postgres…) and can be better or worse, depending on the environment and the number of features which can be filtered on the server.<\/p>\n

The current status<\/h1>\n

It is currently in experimental state so it’s opt-in. Look for it in Options => Data Sources => Data Source Handling.<\/p>\n

For developers<\/h1>\n

If you are a developer and want to make use of it, it’s really simple. All you need to do is to use QgsFeatureRequest::setFilterExpression and make sure the setting is turned on.<\/p>\n

layer.getFeatures( QgsFeatureRequest().setFilterExpression( '\"size\" = \\'capital\\'' ) )<\/pre>\n
Thanks<\/h1>\n
I would like to thank the Swiss QGIS usergroup<\/a> who made this project possible. It is amazing how this group helps to push QGIS forward.<\/p>\n","protected":false},"excerpt":{"rendered":"
Performance This project is all about performance of QGIS with a postgres\/postgis database. A lot of people have QGIS connected to postgres\/postgis (if you don’t: it’s a great combination in the open source geo stack). Databases are really optimized for querying. They keep indexes of geometries to be able to […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_themeisle_gutenberg_block_has_review":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[161],"tags":[125],"class_list":["post-1726","post","type-post","status-publish","format-standard","hentry","category-uncategorised","tag-qgis-org"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1657,"url":"https:\/\/www.opengis.ch\/it\/2015\/06\/01\/qgis-quality-and-testing\/","url_meta":{"origin":1726,"position":0},"title":"QGIS Quality and Testing","author":"Matthias Kuhn","date":"1 Giugno 2015","format":false,"excerpt":"I promised that I will write a bit about what I've been up to at the last QGIS developer meeting - apart from the social part we also got some work done there. So let me start with something that really matters to me and I think can make a\u2026","rel":"","context":"In "Uncategorised"","block_context":{"text":"Uncategorised","link":"https:\/\/www.opengis.ch\/it\/category\/uncategorised\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":15450,"url":"https:\/\/www.opengis.ch\/it\/2023\/02\/14\/postgis-with-qgis\/","url_meta":{"origin":1726,"position":1},"title":"PostGIS with QGIS (on request)","author":"Marco Bernasocchi","date":"14 Febbraio 2023","format":false,"excerpt":"The course is aimed at PostgreSQL users who want to expand their knowledge. Various approaches will be explained to optimize the use of their databases and practiced through different examples.","rel":"","context":"In "Courses"","block_context":{"text":"Courses","link":"https:\/\/www.opengis.ch\/it\/category\/courses\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2024\/08\/DALL%C2%B7E-2024-08-12-15.09.25-A-professional-and-educational-themed-image-for-a-PostgreSQL-administration-course.-The-image-should-feature-a-laptop-displaying-a-PostgreSQL-interfac.webp?fit=1024%2C1024&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2024\/08\/DALL%C2%B7E-2024-08-12-15.09.25-A-professional-and-educational-themed-image-for-a-PostgreSQL-administration-course.-The-image-should-feature-a-laptop-displaying-a-PostgreSQL-interfac.webp?fit=1024%2C1024&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2024\/08\/DALL%C2%B7E-2024-08-12-15.09.25-A-professional-and-educational-themed-image-for-a-PostgreSQL-administration-course.-The-image-should-feature-a-laptop-displaying-a-PostgreSQL-interfac.webp?fit=1024%2C1024&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2024\/08\/DALL%C2%B7E-2024-08-12-15.09.25-A-professional-and-educational-themed-image-for-a-PostgreSQL-administration-course.-The-image-should-feature-a-laptop-displaying-a-PostgreSQL-interfac.webp?fit=1024%2C1024&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":11942,"url":"https:\/\/www.opengis.ch\/it\/2021\/02\/04\/corso-qgis-avanzato-online-10-17-05-2021\/","url_meta":{"origin":1726,"position":2},"title":"Corso QGIS avanzato, Online 10\/17.05.2021","author":"Marco Bernasocchi","date":"4 Febbraio 2021","format":false,"excerpt":"Iscrizione CORSO ANNULLATO \u2013 Prossimo corso in Settembre 2021 Il corso dura 2 giorni (9:00 - 17:00) tramite Google Meet (non \u00e8 richiesto un account Google) e costa 850 CHF a persona.\u00a0Un insegnante per un massimo di 6 partecipanti e 2 insegnanti da 7 a 12 partecipanti. Descrizione Alla fine\u2026","rel":"","context":"In "Non categorizzato"","block_context":{"text":"Non categorizzato","link":"https:\/\/www.opengis.ch\/it\/category\/non-categorizzato\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":12303,"url":"https:\/\/www.opengis.ch\/it\/2021\/09\/30\/corso-qgis-avanzato-30-11-07-12-2021\/","url_meta":{"origin":1726,"position":3},"title":"Corso QGIS avanzato, 30.11\/07.12.2021","author":"Marco Bernasocchi","date":"30 Settembre 2021","format":false,"excerpt":"Iscrizione Il corso dura 2 giorni (9:00 - 17:00) a Bellinzona o tramite Google Il corso della durata di 2 giorni 9:00 - 17:00, si terr\u00e0 a Bellinzona ad un costo di 990 CHF a persona. Questo prezzo include la registrazione, il materiale di formazione, la certificazione ufficiale e due\u2026","rel":"","context":"In "Non categorizzato"","block_context":{"text":"Non categorizzato","link":"https:\/\/www.opengis.ch\/it\/category\/non-categorizzato\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/04\/AdobeStock_305245557-scaled.jpeg?fit=1200%2C400&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":11521,"url":"https:\/\/www.opengis.ch\/it\/2020\/06\/24\/generate-dxf-on-qgis-server\/","url_meta":{"origin":1726,"position":4},"title":"Generate DXF on QGIS server","author":"Matthias Kuhn","date":"24 Giugno 2020","format":false,"excerpt":"Everyone knows QGIS is on the desktop and mobile devices. Many know QGIS is on the web with QGIS server through OGC services. Some know QGIS server has its own extension to generate PDFs. But did you know that QGIS server can also produce DXF files? DXF DXF files are\u2026","rel":"","context":"In "Uncategorised"","block_context":{"text":"Uncategorised","link":"https:\/\/www.opengis.ch\/it\/category\/uncategorised\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/06\/image.png?fit=1200%2C673&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/06\/image.png?fit=1200%2C673&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/06\/image.png?fit=1200%2C673&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/06\/image.png?fit=1200%2C673&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2020\/06\/image.png?fit=1200%2C673&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":14202,"url":"https:\/\/www.opengis.ch\/it\/2023\/03\/06\/qgis-advanced\/","url_meta":{"origin":1726,"position":5},"title":"QGIS Avanzato","author":"Marco Bernasocchi","date":"6 Marzo 2023","format":false,"excerpt":"Dopo aver seguito questa formazione, i partecipanti saranno in grado di realizzare progetti complessi con QGIS. Conosceranno le caratteristiche di diversi formati di dati come PostGIS e GeoPackage, saranno capaci di configurare flussi di lavoro per l\u2019elaborazione dei geodati con strumenti di processing, creare stampe tramite layout, importare\/esportare dati Interlis\u2026","rel":"","context":"In "Corsi QGIS"","block_context":{"text":"Corsi QGIS","link":"https:\/\/www.opengis.ch\/it\/category\/corsi\/corsi-qgis\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2021\/11\/modelbaker_baked_project_with_form_wide2.png?fit=1200%2C677&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2021\/11\/modelbaker_baked_project_with_form_wide2.png?fit=1200%2C677&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2021\/11\/modelbaker_baked_project_with_form_wide2.png?fit=1200%2C677&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2021\/11\/modelbaker_baked_project_with_form_wide2.png?fit=1200%2C677&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.opengis.ch\/wp-content\/uploads\/2021\/11\/modelbaker_baked_project_with_form_wide2.png?fit=1200%2C677&ssl=1&resize=1050%2C600 3x"},"classes":[]}],"jetpack_shortlink":"https:\/\/wp.me\/pbdBtI-rQ","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/posts\/1726","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/comments?post=1726"}],"version-history":[{"count":1,"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/posts\/1726\/revisions"}],"predecessor-version":[{"id":11154,"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/posts\/1726\/revisions\/11154"}],"wp:attachment":[{"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/media?parent=1726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/categories?post=1726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.opengis.ch\/it\/wp-json\/wp\/v2\/tags?post=1726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}