I'm trying to find a data storage method that can handle up-datable calculated fields (such as population density=population/area). I want to be able to alter the population as the data changes (like next year's survey) and have the density update on my database too. Qgis virtual fields are only stored with the project, not available to any other software and Sqlite and Postgre database systems do not store calculated fields (see https://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql , and https://stackoverflow.com/questions/1124695/can-i-create-computed-columns-in-sqlite ).

Keeping track of something like population density as it changes must be such a basic task of any GIS database that I can't believe I'm finding it so hard to do, what is it I'm missing?

  • wouldn't it be better to use triggers in the database to just recalculate the density and such any time an update was made to one of the tables or views? – HDunn May 7 '16 at 7:44
  • That sounds like it would do the job, but I can't seem to find a way of getting a geographic database to hold the value '="population"/"area"' in the first place let alone trigger it to update that calculation. I'm not very experienced in using such databases outside of Qgis, if there's a good bit of software whose GUI can do all the necessary code writing for triggers etc I'd appreciate a link to it. – user39790 May 7 '16 at 8:00

Using views (as suggested in link) is pretty easy, for example.

-- population table (the table you edit when changes...)
CREATE TABLE pop_table (
gid integer primary key,
population integer,
geom geometry);

-- Density view (you can open it like a table in QGIS)
CREATE VIEW density_table AS 
SELECT gid,population,(population/ST_Area(geom))::float AS density, geom
FROM pop_table;

-- insert example
INSERT INTO pop_table VALUES (1,10000,ST_GeomFromText ('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
INSERT INTO pop_table VALUES (2,20000,ST_GeomFromText ('POLYGON((10 10,10 15,15 15,15 10,10 10))'));

-- Checking results
SELECT * FROM density_table

enter image description here

  • Thank you for you response. I don't quite understand it as I'm not familiar with database syntax, but that's for another day. What I was really wondering is why, given this is such an obvious GIS requirement, I'm still having to write ten lines of computer code just to get it done (which I presume I'd have to re-do each time I wanted an update?). What I thought I might be missing is a simple way of adding the field 'population/area' and have the software store the result. I'm baffled as to why this isn't possible, but thanks anyway I will try to learn the coding required. – user39790 May 8 '16 at 11:18
  • Views are "always updated" so there is no need to re-do something. There is only 3 lines to create the View... SQL is probably the better way to create "good spatial database architecture" and put the logic to it... Learn SQL and you never back to spend time in "buttons"... ;) – WKT May 8 '16 at 11:26
  • SQL it is then; can all this be done within Qgis, or is there better software to manage SQL databases and queries? – user39790 May 9 '16 at 6:07
  • You can use o DB Manager in QGIS. But pgAdmin client is better to manage your postgres database. Give a try ! – WKT May 9 '16 at 11:08
  • What software have you used in your example? When I Copy and Paste your code into DB Manager's SQL tab I just get errors; unrecognised token':'; and syntax error near 'CREATE'. Do you know of a good web source for help using SQL with DB Manager? – user39790 May 16 '16 at 7:37

Your Answer

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