sag_sql

INSERT INTO alerts_has_geo_municipios (id_alert, id_geo, area, geom)
(
WITH intersected AS (
    SELECT
        a.id AS alert_id,
        b.id AS geo_mun_id,
        ST_Intersection(a.geom, b.geom) AS intersection_geom
    FROM
        alerts a
        INNER JOIN geo_municipios b ON ST_Intersects(a.geom, b.geom)
)
SELECT
    alert_id,
    geo_mun_id,
    CASE
        WHEN ST_Area(intersected.intersection_geom::geography) > 0
        THEN ST_Area(intersected.intersection_geom::geography) / 100
    ELSE 0
    END AS area,
    intersection_geom
FROM
    intersected
)