Re: [Qgis-user] qgis/postgis : flow map
To understand you correct: In your raw-data table entrants you have one row for each person and in your query you want one flow for each person, ie. the same number of rows in your output? In the regards to the group by, you want the sum(ipondi) on each flow where they're grouped by a specific field? Is the above understood correctly? (The way aggregates work is that you need to group by all non-aggregrate, so it will find all unique combinations of the other fields in the query and sum based on those.) Mvh. / Best regards Casper Bertelsen Mail: casperb...@gmail.com On 03-02-2018 21:07, image wrote: So, i succeed in executing the query without error message. The query generates a temp postgis layer linear. I attach the SS below showing the qgis canvas <http://osgeo-org.1560.x6.nabble.com/file/t365144/6.jpg> But i have always a problem regarding the GROUP BY...i would like to do a GROUP BY one field only...but i can't generate the query without put all fields present in SELECT into GROUP BY ! Please read my currently query : create table temp as select st_makeline(st_makepoint(entrants_v2.x_resi,entrants_v2.y_resi),st_makepoint(entrants_v2.x_tr,entrants_v2.y_tr))as geometry,sum(entrants_v2.ipondi) as sumipondi, entrants_v2.codgeo_res as codgeo_res, entrants_v2.codgeo_tr as codgeo_tr from entrants_v2 group by entrants_v2.codgeo_res,entrants_v2.x_resi,entrants_v2.y_resi,entrants_v2.x_tr,entrants_v2.y_tr,entrants_v2.ipondi,entrants_v2.codgeo_tr Is it a way to assign one field into group by with postgis? Because my result is false. In advance, thank you very much. -- 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] qgis/postgis : flow map
Try use DB Manager (QGIS Plugin - Comes with standard installation of QGIS), here you can connect to the database and run queries and afterwards import the queries into QGIS. When you've create a link to your database in "Add PostGIS-layers" (Ctrl + Shift + D) it should show up in DB manager :) Here it'll be able to read the postgis-functions in the database. Hope it helps. Mvh. / Best regards Casper Bertelsen Mail: casperb...@gmail.com On 03-02-2018 14:06, image wrote: Good morning, I have to do flow map (home->working place) for my area of interest (28 french cities). I get a database .dbf created by a french institute. I imported it into postgres/postgis. One row = 1 people. My table is composed by several fields : id (primary key) codgeo_res: residence (home) city 's id x_resi: residence (home) city 's longitude(centroid) y_resi : residence (home) city 's latitude(centroid) codgeo_tr : work place city 's id x_tr: work city 's longitude(centroid) y_tr :work city 's latitude(centroid) ipondi : poids de l'individu (weighting value assigned to each individual du to the sampling) geom : residence (home) city 's ponctual geometry (centroid) So my goal is to create a new table in order 1 row = 1 flow (city A toward city B) I have to generate useful geometries (points + lines arrow). Moreover, have to apply a SUM on the ipondi field and a GROUP BY on "codgeo_res" field. Could you throw light for me? Could you help me in order to translate this objective to functional sql queries? Up to now, i tried to run this query throw qgis interface called "virtual layer creation" : -- Code: select st_makeline(st_makepoint(entrants_only.x_resi,entrants_only.y_resi),st_makepoint(entrants_only.x_tr,entrants_only.y_tr))as geometry,sum(entrants_only.ipondi) as sumipondi, entrants_only.codgeo_res as codgeo_res, entrants.codgeo_tr as codgeo_tr from entrants_only group by entrants_only.codgeo_res But i get this error message : - query preparation error on PRAGMA table_info(_tview): no such column entrants_only.geom - Moreover, i send you by attachment the Screen shot representing the error message. <http://osgeo-org.1560.x6.nabble.com/file/t365144/ss_error_message_qgis2.jpg> In advance, thank you for your help. -- 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] Clip raster with vectorlayer in Postgis
Hi Lene If you go into Advanced parameters in the GDAL-module and you write -cl schema.table in Additional creation parameters [optional] it might work. It did for me :) Eks: -cl greg.v_greg_flader Mvh. / Best regards Casper Bertelsen Mail: casperb...@gmail.com On 10-01-2018 11:06, Lene Fischer wrote: Hi Casper, I need to be able to set the No data value – and in SAGA this option is not available. It works in 2.99 – but my students still use 2.18 – (As you know ;-)) *Lene Fischer* Associate Professor *University of Copenhagen* Department of Geoscience and Natural Resource Management Forest and Landscape College Nødebovej 77a 3480 Fredensborg Denmark MOB +45 40115084 _...@ign.ku.dk_ <mailto:l...@ign.ku.dk> <@Lene_Fischer> *From:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *On Behalf Of *Casper Bertelsen *Sent:* 10. januar 2018 09:52 *To:* qgis-user@lists.osgeo.org *Subject:* Re: [Qgis-user] Clip raster with vectorlayer in Postgis Hi Lene, Using the Toolbox I get the option to use my vectorlayer from PostGIS. However I did not have any luck getting it to clip with the GDAL-clipper (Trying with a shape file there was no problem), but using the SAGA Clip raster with polygon works fine for me :) Mvh. / Best regards Casper Bertelsen Mail: casperb...@gmail.com <mailto:casperb...@gmail.com> On 10-01-2018 08:51, Lene Fischer wrote: Hi Just started to use PostGIS in QGIS. I only have vector data in postGIS When I try to clip a raster layer with a vector mask (PostGIS) the layer is not shown in the drop down meny. So I had to ‘Save As’ shape before clipping. Is this correct ? Regards *Lene Fischer* Associate Professor *University of Copenhagen* Department of Geoscience and Natural Resource Management Forest and Landscape College Nødebovej 77a 3480 Fredensborg Denmark MOB +45 40115084 l...@ign.ku.dk <mailto:l...@ign.ku.dk> ___ 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] Clip raster with vectorlayer in Postgis
Hi Lene, Using the Toolbox I get the option to use my vectorlayer from PostGIS. However I did not have any luck getting it to clip with the GDAL-clipper (Trying with a shape file there was no problem), but using the SAGA Clip raster with polygon works fine for me :) Mvh. / Best regards Casper Bertelsen Mail: casperb...@gmail.com On 10-01-2018 08:51, Lene Fischer wrote: Hi Just started to use PostGIS in QGIS. I only have vector data in postGIS When I try to clip a raster layer with a vector mask (PostGIS) the layer is not shown in the drop down meny. So I had to ‘Save As’ shape before clipping. Is this correct ? Regards *Lene Fischer* Associate Professor *University of Copenhagen* Department of Geoscience and Natural Resource Management Forest and Landscape College Nødebovej 77a 3480 Fredensborg Denmark MOB +45 40115084 _...@ign.ku.dk_ <mailto:l...@ign.ku.dk> ___ 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] QGIS Save style
Actually wrapping the style-part with <\qgis> and removing the rest is enough to QGIS to registrer it as a style file Mvh. / Best regards Casper Bertelsen Mail: casperb...@gmail.com Tlf.: 28 56 06 41 On 19-11-2017 20:44, Lene Fischer wrote: Thanks for your quick reply... This gives me a lot of work :-( -Original Message- From: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] On Behalf Of Matthias Kuhn Sent: 19. november 2017 20:39 To: qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] QGIS Save style Hi Lene, that's the way it is. It's been on my wishlist to have more control over what's saved for a while, but didn't happen so far. You might try to manually edit the qml file and remove some definitions, but not sure it works either. Regards Matthias On 11/19/2017 08:36 PM, Lene Fischer wrote: Hi Using QGIS 2.18.14 on Windows I´m trying to save a categorized style (only style not labels ect) If I save as default or QML file - the labels ect are saved too. Any ideas ? Regards Lene Fischer ___ 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 ___ 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] QGIS Save style
Hi, I guess for the style to work afterwards, it would be easiest to copy the style part from original QML-file into a newly and not editet QML-file. Mvh. / Best regards Casper Bertelsen Mail: casperb...@gmail.com Tlf.: 28 56 06 41 On 19-11-2017 20:44, Lene Fischer wrote: Thanks for your quick reply... This gives me a lot of work :-( -Original Message- From: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] On Behalf Of Matthias Kuhn Sent: 19. november 2017 20:39 To: qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] QGIS Save style Hi Lene, that's the way it is. It's been on my wishlist to have more control over what's saved for a while, but didn't happen so far. You might try to manually edit the qml file and remove some definitions, but not sure it works either. Regards Matthias On 11/19/2017 08:36 PM, Lene Fischer wrote: Hi Using QGIS 2.18.14 on Windows I´m trying to save a categorized style (only style not labels ect) If I save as default or QML file - the labels ect are saved too. Any ideas ? Regards Lene Fischer ___ 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 ___ 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] Updating a field set with default global variable on edit
Hi Ross, If you make your project without saving username and password, the project will prompt for a user and password when opened. Then each member can login using their assigned login and thus making it possible to use current_user in postgres. To remove saved username and password from your current project file (make a copy, beforehand), you can open it in a text editor and seach for untill you find dbname='xx' host=yy port=5432 four lines above and remove the username and password-part only . Mvh. / Best regards Casper Bertelsen On 22/08/2017 15:36, McDonaldR wrote: Thanks Karl-Magnus We use triggers in the database already and they work perfectly but if we want to record the specific user creating and editing features then we’d have to create separate projects for each user. At the moment the team of about 10 users all use a set of centrally stored project files – one for each different task. I found this post on Stackexchange (https://gis.stackexchange.com/questions/217011/how-to-automatically-update-wfs-edits-with-a-user-id) where global variables are used to populate fields when features are added. However, as Matthias notes in the comments this doesn’t work on feature “update”. If this were possible then it would meet all our needs. Maybe something that could be funded? Ross *From:*Karl Magnus Jönsson [mailto:karl-magnus.jons...@kristianstad.se] *Sent:* 22 August 2017 12:34 *To:* McDonaldR; qgis-user@lists.osgeo.org *Subject:* SV: [Qgis-user] Updating a field set with default global variable on edit Hi! I’ve made the same things with triggers in the database. To avoid getting same username I’ve chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. *Karl-Magnus * *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För *McDonaldR *Skickat:* den 22 augusti 2017 13:12 *Till:* qgis-user@lists.osgeo.org <mailto:qgis-user@lists.osgeo.org> *Ämne:* [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "*created_by*" and "*updated_by*" fields. These are updated by a trigger in the database with the "*current_user*" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - *user_account_name* and *user_full_name* that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "*updated_by*" field to be updated with the current "*user_account_name*". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross *Ross McDonald | *GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT*| t: 01307 476419* This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons.Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment.
Re: [Qgis-user] Updating a field set with default global variable on edit
Hi too, Well logging into the QGIS-project should really be a big deal right ? - You only have to log in once no matter how many layers are loaded from the database. I have a table inside my QGIS project where I can create new users inside the database and administer reading/writing/admin-privileges. Mvh. / Best regards Casper Bertelsen On 22/08/2017 13:34, Karl Magnus Jönsson wrote: Hi! I’ve made the same things with triggers in the database. To avoid getting same username I’ve chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. *Karl-Magnus * *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För *McDonaldR *Skickat:* den 22 augusti 2017 13:12 *Till:* qgis-user@lists.osgeo.org *Ämne:* [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "*created_by*" and "*updated_by*" fields. These are updated by a trigger in the database with the "*current_user*" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - *user_account_name* and *user_full_name* that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "*updated_by*" field to be updated with the current "*user_account_name*". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross *Ross McDonald | *GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT*| t: 01307 476419* This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. ___ 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
