qgis/postgis : flow map

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

qgis/postgis : flow map

image
This post was updated on .
Good morning,

I have to do a 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.


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
Reply | Threaded
Open this post in threaded view
|

Re: qgis/postgis : flow map

Casper Bertelsen
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: [hidden email]


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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user


_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: qgis/postgis : flow map

image
Thanks...so...i did a cast from STRING to BIGINT for my X/Y (x/yhome and
x/ywork).
And i executed this query :

create table test as 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_only.codgeo_tr as codgeo_tr

from entrants_only
group by entrants_only.codgeo_res, entrants_only.x_resi,
entrants_only.y_resi,entrants_only.x_tr,entrants_only.y_tr,
entrants_only.codgeo_tr

For the first time, the query working without error message...but is working
provided that before I put all my fields present in SELECT part into the
GROUP BY...? Unfortunatly, this condition alters fullymy result, no? because
I want to group only by the field codegeo_res, no by all fields...

So, i load the temp postgis table via qgis...the line layer appears...it
seems ok for the location of my home points...but points work location seems
to be false (south hemisphere!)...
i paste below a SS showing the query and result

:

<http://osgeo-org.1560.x6.nabble.com/file/t365144/5.jpg>
In advance, thank you for your usefull help...









--
Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-User-f4125267.html
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: qgis/postgis : flow map

image
This post was updated on .
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 there 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
Reply | Threaded
Open this post in threaded view
|

Re: qgis/postgis : flow map

Casper Bertelsen
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: [hidden email]


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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user


_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user