By David Crowther
Question:
How can I automatically update the Area of my PostGIS datasets?
Answer:
One way to automatically update new or edited features in your PostGIS database is to utilise Widgets in QGIS. Widgets allow you to automate unique IDs, provide list box and date picker choices for users and also perform updates on geometric properties such as calculating areas. Here is a ‘How To’ tip on using Widgets in QGIS:
There may however be occasions that your PostGIS data is being updated by other applications, such as a webGIS, and therefore the Widgets in QGIS cant help automate these updates. So, when working with data in a database it is always a good idea to use database triggers to help you manage the integrity of your information.
In this FAQ we will outline how to implement a database trigger for a Planning Applications layer which contains polygon features. Once a new feature is added, or an existing feature is reshaped, the database trigger will automatically calculate the geometry of the edited feature and update an AREA column in the PostGIS database.
Firstly, ensure that you have an Area field in your PostGIS database table:
Next you will need to create a database level trigger. Use the script below, changing the value of area to use the field name for the column in your PostGIS table that contains your area values.
In your database, refresh the Trigger Functions and the new database trigger has been added – here it is called calc_area.
Finally, use the script below to create a Table level trigger that will run the calc_area update trigger whenever a NEW feature is added or an EXISTING feature is updated.
In your database, refresh the Planning Applications table and the new database trigger has been added – called area_calculate.
To test the trigger you can now insert a new record or reshape an existing feature and the database will handle the update of the Area field automatically. In this example we will use the Cadline webGIS – MapThat - to perform the insert using the OSMM Capture tool.
In the above step we didn’t need to reveal the AREA field to the user. However the PostGIS database trigger has added this value once the new record was inserted into the Planning Applications table and MapThat is revealing this new value in the Information bubble for the layer.
And the PostGIS database table also shows the Area value updated.
Why not use PostGIS database triggers to ensure that you can retain the integrity of your database tables, without having to impact the work that your Users need to do.