[Qgis-user] qgis/postgis : flow map

[image]

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