PostGIS - Update Area Trigger

Marketing
Marketing
  • Updated

By David Crowther

PostGIS_-_Update_Area_Trigger_-_1.PNG

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:

https://www.cadlinecommunity.co.uk/hc/en-us/articles/360000225998-Working-with-Widgets-in-QGIS-Part-1

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:

PostGIS_-_Update_Area_Trigger_-_2.PNG

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.

PostGIS_-_Update_Area_Trigger_-_3.PNG

In your database, refresh the Trigger Functions and the new database trigger has been added – here it is called calc_area.

PostGIS_-_Update_Area_Trigger_-_4.PNG

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.

PostGIS_-_Update_Area_Trigger_-_5.PNG

In your database, refresh the Planning Applications table and the new database trigger has been added – called area_calculate.

PostGIS_-_Update_Area_Trigger_-_6.PNG

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.

PostGIS_-_Update_Area_Trigger_-_7.PNG

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.

PostGIS_-_Update_Area_Trigger_-_8.PNG

And the PostGIS database table also shows the Area value updated.

PostGIS_-_Update_Area_Trigger_-_9.PNG

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.

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.