8 Exercise 6: Advanced Topics
PostgreSQL/PostGIS database environment gives you some powerful capabilities that you should get familiarized step by step.For example triggers can be a valuable asset in enhancing your enterprise GIS environment.
8.1 Creating Triggers for Data Entry Automation
Try to create a Trigger in the database for making the data entries a more seamless process and exploring a bit the possibilities you have.
To create a trigger that calculates the area of a polygon every time it is created, you can follow these steps:
Alter the table “dwellings” to add the “area” column:
ALTER TABLE dwellings
ADD COLUMN area DOUBLE PRECISION;
Create the trigger function that calculates the area:
CREATE OR REPLACE FUNCTION calculate_area() RETURNS TRIGGER AS $$
BEGIN
NEW.area := ST_Area(NEW.geom);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Create the trigger on the “dwellings” table that calls the trigger function:
CREATE TRIGGER calculate_area_trigger BEFORE INSERT OR UPDATE ON dwellings
FOR EACH ROW EXECUTE FUNCTION calculate_area();
Now, try to create some new features for the dwellings-table in QGIS. First you need to bring the table again to QGIS, so just erase the Dwellings-table you have in QGIS and load (with e.g. a double click) the table again.
Now that you have set up the trigger, every time a new polygon is inserted or updated in the “dwellings” table (ie. saved in QGIS), the trigger function “calculate_area” will be executed, and the area of the polygon will be automatically calculated and stored in the “area” column of the table.
8.2 Creating some sample data
We can also create some sample data for you to imagine how your database-powered data management process could go for the long-term:
INSERT INTO water_distribution_points (capacity, operational_status_id, geom)
SELECT
floor(random() * 1000) AS capacity,
floor(random() * 2 + 1) AS operational_status_id,
ST_SetSRID(ST_MakePoint(random() * (412810.70 - 411685.52) + 411685.52)::numeric(10, 2),
(random() * (2344064.41 - 2342832.60) + 2342832.60)::numeric(10, 2)
(32646) AS geom
), FROM generate_series(1, 1000);
As a result, you should many points in the map: