1

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.

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

  • 1
    Try geometry instead of geom – underdark Feb 3 '18 at 17:02
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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