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.