Wrong results joining places to create flow map

Question

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 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
---------------------------------------------------------

Here's a screenshot representing the error message.

---

The problem was due to the datatype of my XY fields (string). So I did a cast STRING to BIGINT (those fields are storing integer EPSG 2154 Lambert93). So now, the query works. I attach a screenshot.

Indeed, the postgis temp linear layer is created. But I have always a problem regarding the GROUP BY. I want to group by on one field only (codgeo_res), but I can't generate the query without put all fields present in SELECT into GROUP BY!

This is my current 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?

My result is false.

Answer 1

Try with subquery:

CREATE TABLE _temp AS 
SELECT ST_MakeLine(
        ST_MakePoint(x_resi,y_resi)
        , ST_MakePoint(x_tr,y_tr)
    ) AS geometry
    , t.sumipondi
    , codgeo_res
    , codgeo_tr
FROM entrants_v2
    JOIN (SELECT codgeo_res,sum(ipondi) AS sumipondi FROM entrants_v2 GROUP BY codgeo_res) t USING(codgeo_res)