I am working in a Postgresql database with PostGIS.

I need to automatically populate a PostGIS column with the intersection of another column's geometry and a landmass polygon. The current trigger function is not populating the column, returning NULL.

I expect: having the column "croppedtolandmass" filled with a single geometry of the intersection of the geometry column and my landmasses05deg grid.

I am getting: only NULLs in "croppedtolandmass".

No warning is raised.

All of the columns exists.

The table I want to update is defined like this:

CREATE TABLE IF NOT EXISTS public."MOBI_site"
(
    "verbatimSiteID" text COLLATE pg_catalog."default" NOT NULL,
    "datasetID" integer NOT NULL,
    "footprintSRS" text COLLATE pg_catalog."default" NOT NULL,
    "verbatimFootprintSRS" text COLLATE pg_catalog."default" NOT NULL,
    geometry geometry,
    croppedtolandmass geometry, --This is what I want to estimate on every update
    CONSTRAINT "MOBI_site_pkey" PRIMARY KEY ("verbatimSiteID", "datasetID"),
    CONSTRAINT "MOBI_site_MOBI_dataset_FK" FOREIGN KEY ("datasetID")
        REFERENCES public."MOBI_dataset" ("datasetID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

My function and trigger code the following:

CREATE OR REPLACE FUNCTION update_cropped_geometry()
RETURNS TRIGGER AS $$
BEGIN
    NEW.croppedtolandmass := ST_Intersection(
        NEW.geometry::geography,
        (SELECT ST_Union(geom) FROM wrld_grids.landmass05deg 
         WHERE ST_Intersects(ST_Envelope(NEW.geometry), ST_Envelope(geom))
         AND ST_Intersects(NEW.geometry, geom)
        )::geography
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_cropped_geometry
BEFORE UPDATE ON public."MOBI_site"
FOR EACH ROW
EXECUTE FUNCTION update_cropped_geometry();

I will appreciate any hint to discover what am I doing wrong.

I am working in a Postgresql database with PostGIS.

I need to automatically populate a PostGIS column with the intersection of another column's geometry and a landmass polygon. The current trigger function is not populating the column, returning NULL.

I expect: having the column "croppedtolandmass" filled with a single geometry of the intersection of the geometry column and my landmasses05deg grid.

I am getting: only NULLs in "croppedtolandmass".

No warning is raised.

All of the columns exists.

The table I want to update is defined like this:

CREATE TABLE IF NOT EXISTS public."MOBI_site"
(
    "verbatimSiteID" text COLLATE pg_catalog."default" NOT NULL,
    "datasetID" integer NOT NULL,
    "footprintSRS" text COLLATE pg_catalog."default" NOT NULL,
    "verbatimFootprintSRS" text COLLATE pg_catalog."default" NOT NULL,
    geometry geometry,
    croppedtolandmass geometry, --This is what I want to estimate on every update
    CONSTRAINT "MOBI_site_pkey" PRIMARY KEY ("verbatimSiteID", "datasetID"),
    CONSTRAINT "MOBI_site_MOBI_dataset_FK" FOREIGN KEY ("datasetID")
        REFERENCES public."MOBI_dataset" ("datasetID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

My function and trigger code the following:

CREATE OR REPLACE FUNCTION update_cropped_geometry()
RETURNS TRIGGER AS $$
BEGIN
    NEW.croppedtolandmass := ST_Intersection(
        NEW.geometry::geography,
        (SELECT ST_Union(geom) FROM wrld_grids.landmass05deg 
         WHERE ST_Intersects(ST_Envelope(NEW.geometry), ST_Envelope(geom))
         AND ST_Intersects(NEW.geometry, geom)
        )::geography
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_cropped_geometry
BEFORE UPDATE ON public."MOBI_site"
FOR EACH ROW
EXECUTE FUNCTION update_cropped_geometry();

I will appreciate any hint to discover what am I doing wrong.

• sql
• postgresql
• geospatial
• postgresql-triggers

• 1 Why the cast to geography? Maybe one of the geographies is invalid (it is possible that the geometry is ok but not the geography). Also beware of st_intersection using geographies, as they are first converted to the "best" possible projection, which can be surprisingly bad for large areas ("code") – JGH Commented 2 days ago
• Are all involved geometries in the same CRS 4326? (if so, it would be safer to add this information in the column definition, i.e geometry(geometry,4326)) – JGH Commented 2 days ago
• 1 You can try to run the query outside of the trigger, part by part, and see where it is not returning the expected result – JGH Commented 2 days ago
• Please add sample data. As a side-note: the envelope intersection is redundant. Intersection of base geometries directly implies intersection of their envelopes On top of that, the ST_Intersects() function is inlineable so you can run your query through explain verbose and see there's a && operation on bboxes in it already. Except a missing cast back to geometry, there's nothing inherently wrong about the code you showed so I'd guess it's something about your data. – Zegarek Commented 2 days ago
• If I make my landmasses small and scarce, most of my MOBI_site geometries can't find anything to intersect with, so the update will set croppedtolandmass to null. An intersection with nothing, is nothing: demo. – Zegarek Commented 2 days ago

1 Answer 1

Except a missing cast back to geometry, there's nothing inherently wrong about the code you showed so I'd guess it's something about your data.

If I make my landmass05deg small and scarce, most of my MOBI_site geometries can't find anything to intersect with, so the update will set croppedtolandmass to null. An intersection with nothing, is nothing.

To clarify: the trigger must be before update or insert in order to fire when you populate the table via insert, merge..then insert or copy.
A plain before update obviously only fires on update, merge..then update and insert..on conflict do update.

If the trigger was defined after you pulled in your data, you need to run an unqualified update to make it fire for all existing rows. It might be a good idea to compare counts before and after, to see if it really changed nothing, or just very little.
_{demo at db<>fiddle}

select count(*)
     , count(*)filter(where croppedtolandmass is null) as nulls
from public."MOBI_site";

explain analyze verbose update public."MOBI_site" set croppedtolandmass=null;

The trigger intercepts it and overwrites that value everywhere (as long as your landmasses actually intersect your geometries):

select count(*)
     , count(*)filter(where croppedtolandmass is null) as nulls
from public."MOBI_site";

Side-note: the envelope intersection is redundant. Intersection of base geometries directly implies intersection of their envelopes On top of that, the ST_Intersects() function is inlineable so you can run your query through EXPLAIN VERBOSE and see there's a && operation on bboxes in it already:

explain analyze verbose
update public."MOBI_site" as t
set croppedtolandmass=ST_Intersection(
        t.geometry::geography,
        (SELECT ST_Union(geom) FROM wrld_grids.landmass05deg 
         WHERE ST_Intersects(ST_Envelope(t.geometry), ST_Envelope(geom))
         AND ST_Intersects(t.geometry, geom)
        )::geography
    )::geometry;

explain analyze verbose
update public."MOBI_site" as t
set croppedtolandmass=ST_Intersection(
        t.geometry::geography,
        (SELECT ST_Union(geom) FROM wrld_grids.landmass05deg 
         WHERE ST_Intersects(t.geometry, geom)
        )::geography
    )::geometry;