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


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

Here's a screenshot representing the error message.

enter image description here

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.

enter image description here

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

Is there a way to assign one field into group by with PostGIS?

My result is false.

  • 1
    Try geometry instead of geom
    – underdark
    Feb 3, 2018 at 17:02

1 Answer 1


Try with subquery:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.