[postgis-users] qgis/postgis : flow map

[Image]

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 
<http://postgis.17.x6.nabble.com/file/t377520/ss_error_message_qgis4.jpg>
the Screen shot representing the error
message.


Thank you for your help.



--
Sent from: http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html