pa_analysis_with_dl.sql

-- Before running, load ecoregions, ecosections, bec, nr regions
-- to the db with the same schema.table name as in bcgw
-- This assumes using ogr2ogr for the load, it creates the objectid columns

-- ----------------------------
-- first, overlay designatedlands with nr regions
-- ----------------------------
DROP TABLE IF EXISTS nr_sub;

CREATE TABLE nr_sub AS
SELECT
    objectid,
    ST_Subdivide (
        geom) AS geom
FROM
    whse_admin_boundaries.adm_nr_regions_sp;

CREATE INDEX ON nr_sub
USING GIST (geom);

DROP TABLE IF EXISTS dlpa_nr;

CREATE TABLE dlpa_nr (
    dlpa_nr_id serial PRIMARY KEY,
    designation text,
    category text,
    map_tile text,
    bc_boundary text,
    region_name text,
    geom geometry
);

INSERT INTO dlpa_nr (designation, category, map_tile, bc_boundary, region_name, geom)
SELECT
    a.designation,
    a.category,
    a.map_tile,
    a.bc_boundary,
    c.region_name,
    CASE WHEN ST_CoveredBy (a.geom,
            b.geom) THEN
        ST_Multi (a.geom)
    ELSE
        ST_Multi (ST_Safe_Intersection (a.geom,
                b.geom))
    END AS geom
FROM
    designatedlands a
    INNER JOIN nr_sub b ON st_intersects (a.geom,
        b.geom)
    INNER JOIN whse_admin_boundaries.adm_nr_regions_sp c ON b.objectid = c.objectid
WHERE
    a.category = '01_PPA'
    AND a.bc_boundary = 'a00_bc_boundary_land_tiled';

CREATE INDEX ON dlpa_nr
USING GIST (geom);


-- ----------------------------
-- now overlay the designatedlands / nr region combination with bec
-- ----------------------------
CREATE TABLE bec_sub AS
SELECT
    objectid,
    ST_Subdivide (
        geom) AS geom
FROM
    whse_forest_vegetation.bec_biogeoclimatic_poly;

CREATE INDEX ON bec_sub
USING GIST (geom);

DROP TABLE IF EXISTS dlpa_nr_bec;

CREATE TABLE dlpa_nr_bec (
    dlpa_nr_bec_id serial PRIMARY KEY,
    designation text,
    category text,
    map_tile text,
    bc_boundary text,
    region_name text,
    bgc_label text,
    ZONE text,
    subzone text,
    variant text,
    geom geometry
);

CREATE INDEX ON dlpa_nr_bec
USING GIST (geom);

INSERT INTO dlpa_nr_bec (designation, category, map_tile, bc_boundary, region_name, bgc_label, ZONE, subzone, variant, geom)
SELECT
    a.designation,
    a.category,
    a.map_tile,
    a.bc_boundary,
    a.region_name,
    c.bgc_label,
    c.zone,
    c.subzone,
    c.variant,
    CASE WHEN ST_CoveredBy (a.geom,
            b.geom) THEN
        ST_Multi (a.geom)
    ELSE
        ST_Multi (ST_Safe_Intersection (a.geom,
                b.geom))
    END AS geom
FROM
    dlpa_nr a
    INNER JOIN bec_sub b ON st_intersects (a.geom,
        b.geom)
    INNER JOIN whse_forest_vegetation.bec_biogeoclimatic_poly c ON b.objectid = c.objectid;

-- ----------------------------
-- overlay designatedlands / nr regions with ecosections
-- ----------------------------

DROP TABLE IF EXISTS eco_sub;

CREATE TABLE eco_sub AS
SELECT
    objectid,
    ST_Subdivide (
        geom) AS geom
FROM
    whse_terrestrial_ecology.erc_ecosections_sp;

CREATE INDEX ON eco_sub
USING GIST (geom);

DROP TABLE IF EXISTS dlpa_nr_eco;

CREATE TABLE dlpa_nr_eco (
    dlpa_nr_bec_id serial PRIMARY KEY,
    designation text,
    category text,
    map_tile text,
    bc_boundary text,
    region_name text,
    ecoprovince_code text,
    ecoregion_code text,
    ecosection_code text,
    geom geometry
);

CREATE INDEX ON dlpa_nr_eco
USING GIST (geom);

INSERT INTO dlpa_nr_eco (designation, category, map_tile, bc_boundary, region_name, ecoprovince_code, ecoregion_code, ecosection_code, geom)
SELECT
    a.designation,
    a.category,
    a.map_tile,
    a.bc_boundary,
    a.region_name,
    d.parent_ecoprovince_code AS ecoprovince_code,
    c.parent_ecoregion_code AS ecoregion_code,
    c.ecosection_code,
    CASE WHEN ST_CoveredBy (a.geom,
            b.geom) THEN
        ST_Multi (a.geom)
    ELSE
        ST_Multi (ST_Safe_Intersection (a.geom,
                b.geom))
    END AS geom
FROM
    dlpa_nr a
    INNER JOIN eco_sub b ON st_intersects (a.geom,
        b.geom)
    INNER JOIN whse_terrestrial_ecology.erc_ecosections_sp c ON b.objectid = c.objectid
    INNER JOIN whse_terrestrial_ecology.erc_ecoregions_sp d ON c.parent_ecoregion_code = d.ecoregion_code


-- ----------------------------
-- overlay designatedlands / nr regions with land use plans
-- ----------------------------

DROP TABLE IF EXISTS lup_sub;

CREATE TABLE lup_sub AS
SELECT
    objectid,
    ST_Subdivide (
        geom) AS geom
FROM
    whse_land_use_planning.rmp_strgc_land_rsrce_plan_svw;

CREATE INDEX ON lup_sub
USING GIST (geom);

DROP TABLE IF EXISTS dlpa_nr_lup;

CREATE TABLE dlpa_nr_lup (
    dlpa_nr_bec_id serial PRIMARY KEY,
    designation text,
    category text,
    map_tile text,
    bc_boundary text,
    region_name text,
    strgc_land_rsrce_plan_name text,
    plan_type text,
    plan_status text
);

CREATE INDEX ON dlpa_nr_lup
USING GIST (geom);

INSERT INTO dlpa_nr_eco (designation, category, map_tile, bc_boundary, region_name, strgc_land_rsrce_plan_name, plan_type, plan_status, geom)
SELECT
    a.designation,
    a.category,
    a.map_tile,
    a.bc_boundary,
    a.region_name,
    c.strgc_land_rsrce_plan_name,
    c.plan_type,
    c.plan_status
    CASE WHEN ST_CoveredBy (a.geom,
            b.geom) THEN
        ST_Multi (a.geom)
    ELSE
        ST_Multi (ST_Safe_Intersection (a.geom,
                b.geom))
    END AS geom
FROM
    dlpa_nr a
    INNER JOIN lup_sub b ON st_intersects (a.geom,
        b.geom)
    INNER JOIN whse_land_use_planning.rmp_strgc_land_rsrce_plan_svw c ON b.objectid = c.objectid;