Re: [Qgis-user] Filter in field group sections of report
Hi Jésahel, Thanks for your reply. Your proposed solution would be fine if it was just me. What I forgot to say is the purpose: I want to share the QGIS project with other users who have limited QGIS skills. A user story would be: the user opens the QGIS project, selects the polygon layer A, selects the Generate Report action from the action menu, clicks on the desired polygon, and the PDF report for the clicked polygon gets generated. Or something similar with a processing algorithm. I think I found a workaround by using an atlas instead of a report. An atlas is OK for this particular case: main polygon layer A related with a non-geometric layer B whose data can be displayed within an Attribute Table item. Contrary to a report, an atlas output can be filtered with the `is_selected()` expression. I will continue to explore this path and let everyone know the found solution in the forum thread when I'm done. But I still believe that, for more complex use cases, namely maps in sections and related maps in subsections, which require a report, a filter option would be extremely useful. Regards. Yann Le 10/10/2021 à 23:20, Jésahel Benoist a écrit : Hi Yann, As I'm often using reports based on filtered datas, and as I see that you know how to filter, I don't really understand what is the problem. You can just apply a filter in the main ui on the a or b layer, or make virtual layers with only the needed datas. Then use the report (close when a change is made in the main ui). Regards, Jésahel Le dim. 10 oct. 2021 à 18:53, Yann Voté <mailto:ygver...@lilo.org>> a écrit : Hi everyone, Let's imagine we have a polygon layer named Layer A with tons of features (tens of thousands), and an attribute layer named Layer B with even more features. Each polygon in Layer A is associated with one or more rows in Layer B. Now suppose we have created a report with a top section for Layer A containing a map centered at each polygon, and a subsection for Layer B showing attributes for each related B row. Is there a way to output the report for exactly one polygon in layer A, or for only some polygons (for example selected polygons) ? And is there a way in the subsection to output only some of the related rows based on some filter expression ? I am aware of this feature request https://github.com/qgis/QGIS/issues/44821 <https://github.com/qgis/QGIS/issues/44821> but is there a way to achieve this with more steps in the UI or with pyQGIS ? Thanks a lot ! Regards. -- Yann Voté - ygver...@lilo.org <mailto:ygver...@lilo.org> ___ Qgis-user mailing list Qgis-user@lists.osgeo.org <mailto:Qgis-user@lists.osgeo.org> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user <https://lists.osgeo.org/mailman/listinfo/qgis-user> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user <https://lists.osgeo.org/mailman/listinfo/qgis-user> ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Filter in field group sections of report
Hi everyone, Let's imagine we have a polygon layer named Layer A with tons of features (tens of thousands), and an attribute layer named Layer B with even more features. Each polygon in Layer A is associated with one or more rows in Layer B. Now suppose we have created a report with a top section for Layer A containing a map centered at each polygon, and a subsection for Layer B showing attributes for each related B row. Is there a way to output the report for exactly one polygon in layer A, or for only some polygons (for example selected polygons) ? And is there a way in the subsection to output only some of the related rows based on some filter expression ? I am aware of this feature request https://github.com/qgis/QGIS/issues/44821 but is there a way to achieve this with more steps in the UI or with pyQGIS ? Thanks a lot ! Regards. -- Yann Voté - ygver...@lilo.org ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] How to publish layer metadata with QGIS Server ?
Hi all, I can't manage to see layer metadata when I go to Explorer panel -> WFS -> -> right click on a layer -> Layer property. In the Layer Property window, Identification section, Extent section and Access section are all empty. The server is QGIS Server 3.18. I've used QGIS Desktop 3.18 to prepare the project, in which I've filled some information in the Metadata tab and in the QGIS Server tab for each layer. In the project properties, I've check the layer in WFS section. And loading the layer from WFS works fine, that's not the problem. But how to populate the Layer Property window with correct information from WFS ? Thanks a lot! P.S. For info, everything is stored in PostgreSQL, both project and layers. -- Yann Voté - ygver...@lilo.org ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] How to set field alias expression in PyQGIS ?
Hi list, I'm stuck at configuring field alias expressions programmatically, didn't found helplful documentation, so any help would be appreciated. What I want is the field label in form to looks like "Code (last used: XXX)" where XXX is, well, the last used code. And this is easily done in GUI, in the layer properties, form tab (feature introduced in 3.14). But I want to do it programmatically in PyQGIS. What I tried so far is the following two lines >>> prop = QgsProperty.fromExpression("format('Code (last used: %1)', maximum(code))", isActive=True) >>> layer.editFormConfig().dataDefinedFieldProperties('code').setProperty(QgsEditFormConfig.Alias, prop) But then the following command >>> layer.editFormConfig().dataDefinedFieldProperties('wetland_code').hasProperty(QgsEditFormConfig.Alias) still returns False. What am I missing ? Thx a lot! -- Yann Voté - ygver...@lilo.org ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] How to use Package layers algorithm in a model when layers to package have same name ?
Hi list, I'm trying to design a model whose last step is to package 3 layers into one GeoPackage using the "Package layers" algorithm. The 3 layers are outputs from three distinct "Refactor fields" algorithms respectively. When I run the model, I get only one layer into the GeoPackage named "Refactored". I believe this is because all three "Refactor fields" algorithms each outputs a layer named "Refactored". Thus the "Package layers" algorithm writes a layer named "Refactored", the overwrites it with the second layer, and overwrites the second one with the third layer. At the end only the last layer written is seen in the GeoPackage. Is there a way to work around this ? I've tried to change output name of the "Refactor fields" algorithm, but: * it does not work if output type is "Model output" (layer name in Geopackage still is "Refactored"), * it does work if output type is "Value", but then it creates intermediate GeoPackages that must be deleted manually. Thanks. -- Yann Voté - ygver...@lilo.org ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] What is a correct input or ouput for "Order by expression" algorithm ?
Hi all, I'm trying to use the "Order by expression" algorithm in a processing model. Before this algorithm I manipulate input layers (remove and refactor columns). After it, I export ordered layers to CSV as a final result (then I want to diff CSV files, that's why I need ordered output). But it seems to not work. Layers are not ordered at all. The help message says: "Be careful, it might not work as expected with some providers, the order might not be kept every time." But which providers are working ? There is also this Github issue: https://github.com/qgis/QGIS/issues/29794 which does not provide helpful feedback. Does anybody know what is the correct way to use the "Order by expression" algorithm ? Or any workaround to acheive the same effect ? Thanks. -- Yann Voté - ygver...@lilo.org ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Plugin development: how to handle API break
Hi list, I wrote the experimental CSV Tools plugin which depends on the processing.tools.postgis Python module. Now with QGIS 3.14, this module does not exists anymore since classes `QgsProcessingParameterDatabaseSchema` and `QgsProcessingParameterDatabaseTable` have been introduced (but maybe it should have been kept for backward compatibility ?). How do I properly manage this situation ? I want both QGIS 3.12 and older and QGIS 3.14+ to be able to install the plugin ? Do I create a new separate plugin for QGIS 3.14+ and maintain both plugins until the next LTR ? For now I have set QgisMaximumVersion to 3.12 in metadata. Thanks. -- Yann Voté - ygver...@lilo.org ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Plugin development: how to handle API break
My bad, didn't see before that it is possible to check QGIS version in PyQGIS. That's the way to go... Le 10/07/2020 à 14:11, Yann Voté a écrit : Hi list, I wrote the experimental CSV Tools plugin which depends on the processing.tools.postgis Python module. Now with QGIS 3.14, this module does not exists anymore since classes `QgsProcessingParameterDatabaseSchema` and `QgsProcessingParameterDatabaseTable` have been introduced (but maybe it should have been kept for backward compatibility ?). How do I properly manage this situation ? I want both QGIS 3.12 and older and QGIS 3.14+ to be able to install the plugin ? Do I create a new separate plugin for QGIS 3.14+ and maintain both plugins until the next LTR ? For now I have set QgisMaximumVersion to 3.12 in metadata. Thanks. -- Yann Voté - ygver...@lilo.org ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] query of a layer from another layer in select by expression
First, I think you can replace layer ids with layer names from your QGIS project legend : 'BATIMENT_TERRAIN_ROLE_MARS_2020_46364fcb_8628_404d_9ca6_0ab713170ceb' -> 'BATIMENT TERRAIL ROLE MARS 2020' (if that is the name of the layer in your QGIS project legend) However, this is risky, because the expression will fail in case you change the layer name in the legend. Second, since you want to compare multiple fields, you can compute the related feature (with get_feature) only once and then store it into a variable 'feat', thanks to the function with_variable(). You can then reference this variable with @feat. You can also use array comparison instead of a long string of AND. Something like this : with_variable( 'feat', get_feature('BATIMENT_TERRAIN_ROLE_MARS_2020_46364fcb_8628_404d_9ca6_0ab713170ceb', 'Matricule', "Matricule"), array(attribute(@feat, 'Aqueduc'), attribute(@feat, 'Sanitaire'), attribute(@feat, 'Batiment')) = array('O', 'O', 'O') ) Le 29/05/2020 à 15:02, Patrick St-Hilaire a écrit : Hi Yann Thank you very much, everything works fine, but is it possible to simplify the query when we want to involve several fields? Here is an example that works well where I replicate the request to achieve my ends, but I can't simplify it. Perhaps it is not possible, but if so it would be good. Thank you attribute (get_feature('BATIMENT_TERRAIN_ROLE_MARS_2020_46364fcb_8628_404d_9ca6_0ab713170ceb', 'Matricule', "Matricule"), 'Aqueduc')= 'O' AND attribute (get_feature('BATIMENT_TERRAIN_ROLE_MARS_2020_46364fcb_8628_404d_9ca6_0ab713170ceb', 'Matricule', "Matricule"), 'Sanitaire')= 'O' AND attribute (get_feature('BATIMENT_TERRAIN_ROLE_MARS_2020_46364fcb_8628_404d_9ca6_0ab713170ceb', 'Matricule', "Matricule"), 'Batiment')= 'O' Le jeu. 28 mai 2020 à 16:25, Yann Voté <mailto:ygver...@lilo.org>> a écrit : Hi, You can try the direct but implicit represent_value() function, if the filtering is by the referenced column, like this: represent_value("referencing_field") = 'filter_value_on_referenced_layer' Or you can try the more verbose and more explicit attribute()/get_feature() functions, if you want to filter by another column: attribute(get_feature('referenced_layer_name', 'referenced_field', "referencing_field"), 'filter_field') = 'filter_value' And be careful about quotes: 'foo' (single quotes) is a string (but may designates a field in other layer in some functiones), while "foo" (double quotes) designates a field in the active layer. Hope this helps! Yann Le 28/05/2020 à 19:11, Patrick St-Hilaire a écrit : > Hi ! > > Could someone tell me how to query a layer from fields of another layer > using select by expression? I have already created a relationship > between my two layers in projects. > > Thank you > > Patrick > > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org <mailto:Qgis-user@lists.osgeo.org> > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > ___ Qgis-user mailing list Qgis-user@lists.osgeo.org <mailto:Qgis-user@lists.osgeo.org> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] query of a layer from another layer in select by expression
Hi, You can try the direct but implicit represent_value() function, if the filtering is by the referenced column, like this: represent_value("referencing_field") = 'filter_value_on_referenced_layer' Or you can try the more verbose and more explicit attribute()/get_feature() functions, if you want to filter by another column: attribute(get_feature('referenced_layer_name', 'referenced_field', "referencing_field"), 'filter_field') = 'filter_value' And be careful about quotes: 'foo' (single quotes) is a string (but may designates a field in other layer in some functiones), while "foo" (double quotes) designates a field in the active layer. Hope this helps! Yann Le 28/05/2020 à 19:11, Patrick St-Hilaire a écrit : Hi ! Could someone tell me how to query a layer from fields of another layer using select by expression? I have already created a relationship between my two layers in projects. Thank you Patrick ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Relations in .qlr not restored ?
Hi all, I've recently discovered a somewhat unadvertised new feature in QGIS 3.12: relations can now be exported as styles! Consequently, when exporting a bunch of layers as a QGIS Layer Definition file (.qlr), relations for each layer are now saved under the tags `` and ``. But when loading back the .qlr file, relations are not restored: project relations are empty. Am I missing something ? Is this by design ? A feature request ? Anyway, thanks to all developers and team for the excellent work ! Regards. Yann ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] PyQGIS to add PostGIS raster layer?
As Alessandro pointed out, with QGS 3.12, you can use 'postgresraster' instead of 'postgres' as the providerType argument. That is: iface.addRasterLayer(uri.uri(), 'my layer label', 'postgresraster') You can find out these parameters (URI and provider type) with further introspection: when you have loaded the layer manually, select it, open a Python console, and you can get the layer Python object with: >>> db_layer = iface.activeLayer() Then you can get the source URI with: >>> db_layer.source(): and the provider type with: >>> db_layer.providerType() With these parameters you can feed iface.addRasterLayer() method or QgsRasterLayer() init method. With QGIS 3.10 you can introspect this way too to find out the URI while 'gdal' is the provider. Regards. Yann Le 30/04/2020 à 22:41, Thayer Young a écrit : Thank you for your response Yann, I tried your idea in the QGIS Python Console in both 3.10.5 and 3.12.2. I was able to create a one part connection string URI by copying the details from a manually added raster. Note that the two step layer addition procedure causes QGIS to crash. The one step iface method does not work either but at least it does not crash QGIS: uri = QgsDataSourceUri("PG: dbname='my_database' host=my:host:address port=5432 sslmode=verify-full mode=2 schema='public' column='rast' table='my_raster_table'") uri.setConnection(' my:host:address', '5432', 'my_database', 'my_user_name', None, QgsDataSourceUri.SslVerifyFull, 'my_qgis_authentication_id') rlayer = iface.addRasterLayer(uri.uri(False), "my layer label", "postgres") # one step add That resulted in the following: Messages: 2020-04-30T16:02:01 CRITICAL Invalid Layer : Raster layer Cannot instantiate the 'postgres' data provider PostGIS Messages: 2020-04-30T16:02:01 WARNING Field rast ignored, because of unsupported type b 2020-04-30T16:02:01 WARNING NOTICE: row number 0 is out of range 0..-1 -Thayer Message: 5 Date: Thu, 30 Apr 2020 09:59:04 +0200 From: Yann Voté mailto:ygver...@lilo.org>> To: qgis-user@lists.osgeo.org <mailto:qgis-user@lists.osgeo.org> Subject: Re: [Qgis-user] PyQGIS to add PostGIS raster layer? Message-ID: <mailto:cda24fb7-1832-b50f-649b-8f615ea50...@lilo.org>> Content-Type: text/plain; charset=utf-8; format=flowed Sorry, I read too fast. Didn't see it was about raster layers. But here is a clue : load the raster layer manually, then look at the tooltip appearing when you hover the cursor on the layer. It will contain the connection string. Le 30/04/2020 à 09:45, Yann Voté a écrit : > Hi, > > Something like this should do. > > pg_layer = QgsVectorLayer( > "host=db.priv.example.org port=5432 authcfg=expauth srid=4326 " > "dbname=exampledb table='schema'.'table_name' (geometry) " > "type=MULTIPOLYGON sql=", > baseName='table_name', > providerLib='postgres', > ) > QgsProject.instance().addMapLayer(pg_layer) > > Of course, adapt with your values. In particular, authcfg is the 7-chars > identifier of the auth configuration that contains your login and > password (or cert) to authenticate to PostgreSQL. > > See https://qgis.org/pyqgis/master/core/QgsVectorLayer.html > > See also > https://qgis.org/pyqgis/master/core/QgsDataSourceUri.html#qgis.core.QgsDataSourceUri > for more options to build database connection strings. > > Regards. > > Yann > > > Le 30/04/2020 à 00:24, thayeray a écrit : >> Is it possible to add a PostGIS raster layer to a project using PyQGIS? >> The PyQGIS Developer Cookbook mentions adding rasters from files, >> geopackages and WCS services. >> If I try using a QgsDataSourceUri by feeding it a raster column >> instead of a >> geometry column, the PostGIS error that is generated shows that is not >> going >> to work because raster is not a geometry: >> WARNING Erroneous query: SELECT 0, array_agg(DISTINCT >> st_srid("rast")::text || ':' || UPPER(geometrytype("rast"))) FROM >> "public"."indicators__g_economic_rebal_r_62ft" returned 7 [ERROR: >> function >> geometrytype(raster) does not exist >> >> Any ideas of what I can do? Do I just have to give up on PostGIS >> rasters and >> just turn it into WCS on the server? ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] PyQGIS to add PostGIS raster layer?
Sorry, I read too fast. Didn't see it was about raster layers. But here is a clue : load the raster layer manually, then look at the tooltip appearing when you hover the cursor on the layer. It will contain the connection string. Le 30/04/2020 à 09:45, Yann Voté a écrit : Hi, Something like this should do. pg_layer = QgsVectorLayer( "host=db.priv.example.org port=5432 authcfg=expauth srid=4326 " "dbname=exampledb table='schema'.'table_name' (geometry) " "type=MULTIPOLYGON sql=", baseName='table_name', providerLib='postgres', ) QgsProject.instance().addMapLayer(pg_layer) Of course, adapt with your values. In particular, authcfg is the 7-chars identifier of the auth configuration that contains your login and password (or cert) to authenticate to PostgreSQL. See https://qgis.org/pyqgis/master/core/QgsVectorLayer.html See also https://qgis.org/pyqgis/master/core/QgsDataSourceUri.html#qgis.core.QgsDataSourceUri for more options to build database connection strings. Regards. Yann Le 30/04/2020 à 00:24, thayeray a écrit : Is it possible to add a PostGIS raster layer to a project using PyQGIS? The PyQGIS Developer Cookbook mentions adding rasters from files, geopackages and WCS services. If I try using a QgsDataSourceUri by feeding it a raster column instead of a geometry column, the PostGIS error that is generated shows that is not going to work because raster is not a geometry: WARNING Erroneous query: SELECT 0, array_agg(DISTINCT st_srid("rast")::text || ':' || UPPER(geometrytype("rast"))) FROM "public"."indicators__g_economic_rebal_r_62ft" returned 7 [ERROR: function geometrytype(raster) does not exist Any ideas of what I can do? Do I just have to give up on PostGIS rasters and just turn it into WCS on the server? -- Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-User-f4125267.html ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] PyQGIS to add PostGIS raster layer?
Hi, Something like this should do. pg_layer = QgsVectorLayer( "host=db.priv.example.org port=5432 authcfg=expauth srid=4326 " "dbname=exampledb table='schema'.'table_name' (geometry) " "type=MULTIPOLYGON sql=", baseName='table_name', providerLib='postgres', ) QgsProject.instance().addMapLayer(pg_layer) Of course, adapt with your values. In particular, authcfg is the 7-chars identifier of the auth configuration that contains your login and password (or cert) to authenticate to PostgreSQL. See https://qgis.org/pyqgis/master/core/QgsVectorLayer.html See also https://qgis.org/pyqgis/master/core/QgsDataSourceUri.html#qgis.core.QgsDataSourceUri for more options to build database connection strings. Regards. Yann Le 30/04/2020 à 00:24, thayeray a écrit : Is it possible to add a PostGIS raster layer to a project using PyQGIS? The PyQGIS Developer Cookbook mentions adding rasters from files, geopackages and WCS services. If I try using a QgsDataSourceUri by feeding it a raster column instead of a geometry column, the PostGIS error that is generated shows that is not going to work because raster is not a geometry: WARNINGErroneous query: SELECT 0, array_agg(DISTINCT st_srid("rast")::text || ':' || UPPER(geometrytype("rast"))) FROM "public"."indicators__g_economic_rebal_r_62ft" returned 7 [ERROR: function geometrytype(raster) does not exist Any ideas of what I can do? Do I just have to give up on PostGIS rasters and just turn it into WCS on the server? -- Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-User-f4125267.html ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Distincguis NULL and empty strings when reading CSV files
Brent : thanks for the detailed explanation. As for your suggestion, I'm trying to go the other way around: Postgis --> CSV --> QGIS. The goal is to quickly extract data from a very large table in Postgis using an extent drawn by the user on the canvas. So I do a `COPY (SELECT ... FROM ... WHERE ST_Intersects(geometry, extent_drawn_on_canvas)) TO ...` to get a CSV file, and then I load the CSV file into QGIS, expecting to get the exact same values than in Postgis (NULL when NULL in Postgis, empty string when empty string in Postgis). Richard: nice workaround, and it almost works! With a space in double quotes (" "), I get in my QGIS 3.12 a single space string, not an empty string, even when Trim fields in checked. But with a space and no quotes, it works! That is just like the following. col1,col2,col3,col4 1,, ,"foo" col3 get an empty string (if Trim fields is checked). Hope this is a stable behavior. Thanks again! Regards. Yann Le 15/04/2020 à 12:35, brent.w...@niwa.co.nz a écrit : Not that I'm aware of - Text files do not have an implicit way of representing null values, except for an empty string. Databases do this better, with an isnull() function which is different to an ='' (2x single quotes) expression - the empty string. Some software packages use a locally specified string (eg: NA) to represent nulls, so you can use an !='NA' expression to exclude nulls. The ^A (control-A) ANSI character also represents a null, but good luck getting a useful text file containing control codes. Generally non-trivial. Note that an "=null" expression is semantically void, null means unknown, so two nulls do not equal each other, we don't know what they equal by definition. Which is why SQL has the is null/isnull() approach, it is not a logical "=" operator. I suggest you either load your text file into a database table (spatialite or postgis?), define the nulls as db null values (which are NOT the same as empty strings) & QGIS can use the db to do the null operations, or alternatively you could define a string (not an empty string) that is used to represent nulls in your file, and use an "=" operator. For numerical values you can use a value which does not occur in the dataset to represent a null . Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: Qgis-user on behalf of Yann Voté Sent: Wednesday, April 15, 2020 21:57 To: qgis-user@lists.osgeo.org Subject: [Qgis-user] Distincguis NULL and empty strings when reading CSV files Hi all, Just wondering if there is a way to distinguish null values and empty strings when loading CSV files in QGIS. What I tried so far is quoting empty strings like in the following example. col1,col2,col3 1,,"" I was expecting col2 to be NULL and col3 to be an empty string. But actual result is that both col2 and col3 are NULL. Is there another way ? Am I missing something ? Thanks in advance. Yann ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://aus01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fqgis-user&data=02%7C01%7Cbrent.wood%40niwa.co.nz%7Cd4ca1af8180e4d000d9e08d7e1237944%7C41caed736a0c468aba499ff6aafd1c77%7C0%7C0%7C637225414814189476&sdata=Kn4huzdkXPJpZK86xwhz9o17TlIhyzB6Ph72Br37sF4%3D&reserved=0 Unsubscribe: https://aus01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fqgis-user&data=02%7C01%7Cbrent.wood%40niwa.co.nz%7Cd4ca1af8180e4d000d9e08d7e1237944%7C41caed736a0c468aba499ff6aafd1c77%7C0%7C0%7C637225414814189476&sdata=Kn4huzdkXPJpZK86xwhz9o17TlIhyzB6Ph72Br37sF4%3D&reserved=0 [https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png]<https://www.niwa.co.nz> Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | National Institute of Water & Atmospheric Research Ltd (NIWA) 301 Evans Bay Parade Hataitai Wellington New Zealand Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz> Facebook<https://www.facebook.com/nzniwa> LinkedIn<https://www.linkedin.com/company/niwa> Twitter<https://twitter.com/niwa_nz> Instagram<https://www.instagram.com/niwa_science> To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Distincguis NULL and empty strings when reading CSV files
Hi all, Just wondering if there is a way to distinguish null values and empty strings when loading CSV files in QGIS. What I tried so far is quoting empty strings like in the following example. col1,col2,col3 1,,"" I was expecting col2 to be NULL and col3 to be an empty string. But actual result is that both col2 and col3 are NULL. Is there another way ? Am I missing something ? Thanks in advance. Yann ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Should be easy? Open csv in Graphical Modeller
Hi, Not sure if this will help you, but I recently wrote a processing plugin to deal with CSV files. The plugin is named CSV Tools. It aims to be a little more generic than the "create points layer from table" since it can handle WKT geometries and hence can load lines and polygons. The plugin is *highly* experimental, and thus is available in QGIS only when experimental plugins are allowed. It works for me at the moment, I use it in models. But I've plans to make it more usable so that it can be useful to more people. I'd love to have feedback : if it works or not and would gladly fix bugs. Regards. Yann Le 31/03/2020 à 12:43, magerlin a écrit : Thanks for your comment. But I am trying to setup a model for reading GTFS data (General Transit Feed Specification) which are sets of like 10 csv files and I am right now working with 3 sets so like 30 csv files in total. So the aim was to automate tedious work and not have to remember every time how these 10 csv files relate to each other. I am aware of two plugins reading GTFS data but they both break down with python errors ... - Regards Morten Currently using Qgis 3.10.0 Windows 10, 64bit -- Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-User-f4125267.html ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Handle local layers in resources folder when project saved in PostgreSQL
Thank you all for your answers. Alessandro: the resources folder contains only read-only data (reference rasters or vectors from French national geographic institute, not to be modified). I deploy these layers on every workstation. Is it still a bad practice for read-only reference data to use this folder ? Raymond: indeed I'm currently working on a plugin, and I've seen QgsPathResolver. But documentation for this class is quite terse. but I'll continue to investigate and experiment. Jorge: interesting setup. Does the offline editing plugin work when everything is in database ? In other words, are raster layers converted offline ? Cheers. Yann Le 05/12/2019 à 13:16, Jorge Gustavo Rocha a écrit : Hi, In my setup - using PGSERVICES - all vector, raster, styles and projects are stored on database. This is my preferred way to organize the data: on a database. QGIS Server also takes advantage of this setup and publish the projects directly from the database, using the rasters (in db or out of db rasters). The few things I'm not sharing on the database are SVG, layout images and fonts. There are problems with Postgresql/GDAL access to Postgresql, but if using PGSERVICES it works. Best regards, Jorge On 05/12/19 11:18, Alessandro Pasotti wrote: On Thu, Dec 5, 2019 at 11:58 AM Yann Voté <mailto:ygver...@lilo.org>> wrote: Hi all, I can't find a solution to the following issue, or a way to work around it: I have created a project with some PostgreSQL layers to edit and some raster layers on local filesystem. I work on a Linux computer and have put the local layers into the pkg data path resources folder (/usr/share/qgis/resources/local/raster.gpkg). Sorry but this is not a good idea, that path is usually not user-writeable and should not be used to store any user related information. But if I understand correctly what you were trying to do I'm afraid there is not a solution: the concept of "relative path" means relative to the project's storage path but that path does not make any sense in case of a POSTGRES storage (because POSTGRES is not a local filesystem-based DB in the same sense as GPKG or SPATIALITE are), so your file-system based asset paths are not (and cannot be) converted to relative paths because there is no project path we can relate to. This makes portability an issue if the project has filesystem-based layers (rasters in your case). There is no solution I'm aware of, storing rasters into PG is also not a viable option at the moment because support is currently buggy (see: https://github.com/qgis/QGIS/issues/30392). Cheers I have also saved the project into PostgreSQL so that it can be shared easily. Other users work on Windows computers and have the same rasters under pkg data path resources folder (C:/PROGRAM~1/QGIS3~1.8/apps/qgis/resources/local/raster.gpkg). But when they open the project, Postgis layers are loaded fine, but QGIS says that raster.gpkg cannot be found under the /usr/share/... path. I would expect that layers in the pkg data path resources folder are saved with the inbuilt: prefix, but in fact they are not. Moreover, it seems that one cannot select "Relative" for saving layer paths, in project properties, when project is saved to PostgreSQL. Looks sensible (what is the relative path to a PostgreSQL table ?), but then what can I do to share a PostgreSQL project with local layers ? We all use QGIS 3.8.3. Thank you for any advice ! Cheers Yann ___ Qgis-user mailing list Qgis-user@lists.osgeo.org <mailto:Qgis-user@lists.osgeo.org> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user -- Alessandro Pasotti w3: www.itopen.it <http://www.itopen.it> ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info:https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe:https://lists.osgeo.org/mailman/listinfo/qgis-user -- Email Signature Logo <https://www.geomaster.pt> *Geomaster* *Jorge Gustavo Rocha* | Software Engineer *e:*j...@geomaster.pt | *m:*+351 910 333 888 *g:*41.54094,-8.40490 | *v: *510 906 109 *a: * Rua António Cândido Pinto, 67, 4715-400 Braga ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Handle local layers in resources folder when project saved in PostgreSQL
Hi all, I can't find a solution to the following issue, or a way to work around it: I have created a project with some PostgreSQL layers to edit and some raster layers on local filesystem. I work on a Linux computer and have put the local layers into the pkg data path resources folder (/usr/share/qgis/resources/local/raster.gpkg). I have also saved the project into PostgreSQL so that it can be shared easily. Other users work on Windows computers and have the same rasters under pkg data path resources folder (C:/PROGRAM~1/QGIS3~1.8/apps/qgis/resources/local/raster.gpkg). But when they open the project, Postgis layers are loaded fine, but QGIS says that raster.gpkg cannot be found under the /usr/share/... path. I would expect that layers in the pkg data path resources folder are saved with the inbuilt: prefix, but in fact they are not. Moreover, it seems that one cannot select "Relative" for saving layer paths, in project properties, when project is saved to PostgreSQL. Looks sensible (what is the relative path to a PostgreSQL table ?), but then what can I do to share a PostgreSQL project with local layers ? We all use QGIS 3.8.3. Thank you for any advice ! Cheers Yann ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user