issue-65

[leebuhrmann]

The 'ugc_boundary' table now automatically maintains a record of all 'boundary' and 'ugc_zone' geometries that intersect. This was done by creating Functions and Triggers in a copied database named 'snow_65'. It also required minor alterations to the 'update' function in the BoundarySerive.java.

Functions and Triggers Implementation in 'snow_65'

I created a Function in the 'snow_65' database that detects when a 'boundary' or 'ugc_zone' entry has been created or updated. When the function detects this change it reevaluates the 'ugc_boundary' table using the 'ST_Intersects()' PostGIS function. In addition, I altered all the many-to-many table foreign keys so that they CASCADE DELETE when one of the referenced entries is deleted. I posted the source code at the bottom of this PR.

BoundaryService.java and EntityManager

After making the database alterations I found that using the PUT API endpoint for boundaries did not result in accurate changes to the 'ugc_boundary' table. To fix this I had to use the Entity Manager directly. To be honest, I don't fully understand what it does even after researching it.

Testing

I did many different things to ensure this works. First I ran all the database insertions via terminal just to make ensure it worked in 'snow_65' before trying it with Spring Boot. You can also do that by running the below code snippet:

REMEMBER TO BE IN 'snow_65' not 'snow'

-- Clear all tables
TRUNCATE TABLE ugc_zone, alert, boundary, snow_user, ugc_alert, ugc_boundary, user_boundary;

-- Reset the sequence for the `boundary` table
ALTER SEQUENCE boundary_boundary_id_seq RESTART WITH 1;

-- Reset the sequence for the `snow_user` table
ALTER SEQUENCE snow_user_user_id_seq RESTART WITH 1;

-- Reset the sequence for the `alert` table
ALTER SEQUENCE alert_alert_id_seq RESTART WITH 1;

-- ugc_zone inserts

INSERT INTO ugc_zone (ugc_code, the_geom) VALUES 
('NEZ001', ST_GeomFromText('MULTIPOLYGON(((3 3, 7 3, 7 7, 3 7, 3 3)))', 3857)),
('NEZ002', ST_GeomFromText('MULTIPOLYGON(((6 6, 10 6, 10 10, 6 10, 6 6)))', 3857)),
('NEZ003', ST_GeomFromText('MULTIPOLYGON(((10 10, 17 10, 17 17, 10 17, 10 10)))', 3857)),
('NEZ004', ST_GeomFromText('MULTIPOLYGON(((100 100, 150 100, 150 150, 100 150, 100 100)))', 3857));

-- boundary inserts

INSERT INTO boundary (boundary_id, the_geom) VALUES 
-- intersects with ugc_zone NEZ001
(1, ST_GeomFromText('MULTIPOLYGON(((0 0, 5 0, 5 5, 0 5, 0 0)))', 3857)),
-- intersects with ugc_zone NEZ001 & NEZ002
(2, ST_GeomFromText('MULTIPOLYGON(((4 4, 9 4, 9 9, 4 9, 4 4)))', 3857)),
-- inersects with no ugc_zones
(4, ST_GeomFromText('MULTIPOLYGON(((25 25, 30 25, 30 30, 25 30, 25 25)))', 3857));

-- check ugc_boundary
SELECT * FROM ugc_boundary;

To test Spring Boot you will need to first insert some 'ugc_zone' entries via terminal into the database.

INSERT INTO ugc_zone (ugc_code, the_geom) VALUES 
('NEZ001', ST_GeomFromText('MULTIPOLYGON(((3 3, 7 3, 7 7, 3 7, 3 3)))', 3857)),
('NEZ002', ST_GeomFromText('MULTIPOLYGON(((6 6, 10 6, 10 10, 6 10, 6 6)))', 3857)),
('NEZ003', ST_GeomFromText('MULTIPOLYGON(((10 10, 17 10, 17 17, 10 17, 10 10)))', 3857)),
('NEZ004', ST_GeomFromText('MULTIPOLYGON(((100 100, 150 100, 150 150, 100 150, 100 100)))', 3857));

Then make a POST API call creating a new boundary entry. Below is an example body.

{
    "the_geom": {
        "type": "MultiPolygon",
        "coordinates": [
            [
                [
                    [0,0],[5,0],[5,5],[0,5],[0,0]
                ]
            ]
        ]
    },
    "description": "test description",
    "name": "testname"
}

Check the 'ugc_boundary" table with 'SELECT * FROM boundary;'

Also you can check update with a PUT API call. Below is an example body.

{
    "the_geom": {
        "type": "MultiPolygon",
        "coordinates": [
            [
                [
                    [8,8],[15, 8],[15,15],[8,15],[8,8]
                ]
            ]
        ]
    },
    "description": "updateddescription",
    "name": "updatename"
}

Finally I tested the UgcZoneService 'create' function. This was done by inserting boundaries into the database that had geometries that intersect with real world UGC zones scraped by the UgcZoneScraper. Below is some example insertions for testing. These may need to be altered depending on the current real life alerts in Texas.

-- insert boundary the interacts with TXC001 (real ugc zone)

INSERT INTO boundary (boundary_id, the_geom) VALUES 
-- intersects with ugc_zone TXC001
(1, ST_GeomFromText('MULTIPOLYGON(((-96 31, -94 31, -94 33, -96 33, -96 31)))', 3857)),
-- doesn't intersect with ugc_zone TXC001
(2, ST_GeomFromText('MULTIPOLYGON(((4 4, 9 4, 9 9, 4 9, 4 4)))', 3857));

Database Source Code

I couldn't think of a good way of documenting the changes to the database, so I pasted them below.

performed on all many-to-many tables

ALTER TABLE user_boundary DROP CONSTRAINT IF EXISTS user_boundary_user_id_fkey;
ALTER TABLE user_boundary DROP CONSTRAINT IF EXISTS user_boundary_boundary_id_fkey;

ALTER TABLE user_boundary
ADD CONSTRAINT user_boundary_user_id_fkey
FOREIGN KEY (user_id) REFERENCES snow_user(user_id) ON DELETE CASCADE;

ALTER TABLE user_boundary
ADD CONSTRAINT user_boundary_boundary_id_fkey
FOREIGN KEY (boundary_id) REFERENCES boundary(boundary_id) ON DELETE CASCADE;

ALTER TABLE ugc_alert DROP CONSTRAINT IF EXISTS ugc_alert_ugc_code_fkey;
ALTER TABLE ugc_alert DROP CONSTRAINT IF EXISTS ugc_alert_alert_id_fkey;

ALTER TABLE ugc_alert
ADD CONSTRAINT ugc_alert_alert_id_fkey
FOREIGN KEY (alert_id) REFERENCES alert(alert_id) ON DELETE CASCADE;

ALTER TABLE ugc_alert
ADD CONSTRAINT ugc_alert_ugc_code_fkey
FOREIGN KEY (ugc_code) REFERENCES ugc_zone(ugc_code) ON DELETE CASCADE;

ALTER TABLE ugc_boundary
ADD CONSTRAINT ugc_boundary_boundary_id_fkey
FOREIGN KEY (boundary_id) REFERENCES boundary(boundary_id) ON DELETE CASCADE;

ALTER TABLE ugc_boundary
ADD CONSTRAINT ugc_boundary_ugc_code_fkey
FOREIGN KEY (ugc_code) REFERENCES ugc_zone(ugc_code) ON DELETE CASCADE;

Function

Function Name: update_ugc_boundary Purpose: This function was designed to handle automatic updates and deletions in the ugc_boundary table when boundary or ugc_zone records are inserted, updated, or deleted. It checks for intersections between geometries for inserts and updates, and it handles clean-up for deletions.

 BEGIN                                                                               +
     -- Handle deletions from 'boundary'                                             +
     IF TG_TABLE_NAME = 'boundary' AND TG_OP = 'DELETE' THEN                         +
         DELETE FROM ugc_boundary WHERE boundary_id = OLD.boundary_id;               +
                                                                                     +
     -- Handle deletions from 'ugc_zone'                                             +
     ELSIF TG_TABLE_NAME = 'ugc_zone' AND TG_OP = 'DELETE' THEN                      +
         DELETE FROM ugc_boundary WHERE ugc_code = OLD.ugc_code;                     +
                                                                                     +
     -- Handle insertions or updates for 'boundary'                                  +
     ELSIF TG_TABLE_NAME = 'boundary' AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN+
         -- Remove existing entries                                                  +
         DELETE FROM ugc_boundary WHERE boundary_id = NEW.boundary_id;               +
         -- Insert new intersections                                                 +
         INSERT INTO ugc_boundary(ugc_code, boundary_id)                             +
         SELECT u.ugc_code, NEW.boundary_id                                          +
         FROM ugc_zone u                                                             +
         WHERE ST_Intersects(u.the_geom, NEW.the_geom);                              +
                                                                                     +
     -- Handle insertions or updates for 'ugc_zone'                                  +
     ELSIF TG_TABLE_NAME = 'ugc_zone' AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN+
         -- Remove existing entries                                                  +
         DELETE FROM ugc_boundary WHERE ugc_code = NEW.ugc_code;                     +
         -- Insert new intersections                                                 +
         INSERT INTO ugc_boundary(ugc_code, boundary_id)                             +
         SELECT NEW.ugc_code, b.boundary_id                                          +
         FROM boundary b                                                             +
         WHERE ST_Intersects(b.the_geom, NEW.the_geom);                              +
     END IF;                                                                         +
     RETURN NEW; 

This command will give you the source code in the PostgreSQL database.

SELECT prosrc AS source_code
FROM pg_proc
JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE proname = 'update_ugc_boundary' AND nspname = 'public';  -- Adjust 'public' if your function is in another schema

Triggers Using This Function

Trigger Names: tr_boundary_insert - Invoked after an insert operation on the boundary table. tr_boundary_update - Invoked after an update operation on the boundary table, specifically when there is a change in the geometry. tr_boundary_delete - Invoked after a delete operation on the boundary table. tr_ugc_zone_insert - Invoked after an insert operation on the ugc_zone table. tr_ugc_zone_update - Invoked after an update operation on the ugc_zone table, specifically when there is a change in the geometry. tr_ugc_zone_delete - Invoked after a delete operation on the ugc_zone table. These triggers are all linked to the update_ugc_boundary function and are set to respond to after insert, update, and delete events, executing the function logic that handles the appropriate modifications in the ugc_boundary table based on the operation type.

The command below gives the table below.

SELECT tgname AS trigger_name,
       pg_get_triggerdef(pg_trigger.oid) AS definition
FROM pg_trigger
JOIN pg_proc ON pg_proc.oid = pg_trigger.tgfoid
JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE tgname IN ('tr_boundary_insert', 'tr_boundary_update', 'tr_boundary_delete', 
                 'tr_ugc_zone_insert', 'tr_ugc_zone_update', 'tr_ugc_zone_delete')
      AND nspname = 'public';  -- Adjust 'public' if your triggers are in another schema

    trigger_name    |                                                                                definition                                                                                 
--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 tr_boundary_insert | CREATE TRIGGER tr_boundary_insert AFTER INSERT ON public.boundary FOR EACH ROW EXECUTE FUNCTION update_ugc_boundary()
 tr_boundary_update | CREATE TRIGGER tr_boundary_update AFTER UPDATE ON public.boundary FOR EACH ROW WHEN ((old.the_geom IS DISTINCT FROM new.the_geom)) EXECUTE FUNCTION update_ugc_boundary()
 tr_boundary_delete | CREATE TRIGGER tr_boundary_delete AFTER DELETE ON public.boundary FOR EACH ROW EXECUTE FUNCTION update_ugc_boundary()
 tr_ugc_zone_insert | CREATE TRIGGER tr_ugc_zone_insert AFTER INSERT ON public.ugc_zone FOR EACH ROW EXECUTE FUNCTION update_ugc_boundary()
 tr_ugc_zone_update | CREATE TRIGGER tr_ugc_zone_update AFTER UPDATE ON public.ugc_zone FOR EACH ROW WHEN ((old.the_geom IS DISTINCT FROM new.the_geom)) EXECUTE FUNCTION update_ugc_boundary()
 tr_ugc_zone_delete | CREATE TRIGGER tr_ugc_zone_delete AFTER DELETE ON public.ugc_zone FOR EACH ROW EXECUTE FUNCTION update_ugc_boundary()