4  Sampling frames

We will have different sampling frames, also called the universe, for the different ANO-modules.

4.1 Setup schema

schema_ur <- "CREATE SCHEMA sampling_frames"
dbSendQuery(con, schema_ur) 

Write queries to grant read only access to all.

priv <- "ALTER DEFAULT PRIVILEGES IN SCHEMA sampling_frames GRANT SELECT ON TABLES TO ag_pgsql_ano_moduler_ro"
priv2 <- "ALTER DEFAULT PRIVILEGES IN SCHEMA sampling_frames GRANT SELECT ON TABLES TO ag_pgsql_ano_moduler_rw"
priv3 <- "ALTER DEFAULT PRIVILEGES IN SCHEMA sampling_frames GRANT SELECT ON TABLES TO ag_pgsql_ano_moduler_admin"
priv4 <- "GRANT USAGE ON SCHEMA sampling_frames  TO ag_pgsql_ano_moduler_admin"
priv5 <- "GRANT USAGE ON SCHEMA sampling_frames  TO ag_pgsql_ano_moduler_rw"
priv6 <- "GRANT USAGE ON SCHEMA sampling_frames  TO ag_pgsql_ano_moduler_ro"

dbSendStatement(con, priv)
dbSendStatement(con, priv2)
dbSendStatement(con, priv3)
dbSendStatement(con, priv4)
dbSendStatement(con, priv5)
dbSendStatement(con, priv6)

4.2 ANO Havstrand

For ANO Havstrand (ANO Coast), SSB500 has been masked to only include grid cells that overlap with the Norwegian coastline. The script for doing that is written in python ad can be found here: /data/P-Prosjekter2/412421_okologisk_tilstand_2024/Jan/

Read data into R:

coast_path <- "/data/P-Prosjekter2/412421_okologisk_tilstand_2024/Jan/CoastalSampling3.gdb"

sf::st_layers(coast_path)

Driver: OpenFileGDB 
Available layers:
                layer_name     geometry_type features fields
1            fcNorgeKyst_l Multi Line String   101331      2
2 fcKystline_500mSegmenter Multi Line String   240861      1
3              Samples62_5       Multi Point   769342      2
4              Samples83_3       Multi Point   555958      2
5               Samples125       Multi Point   350676      2
6            SSB500_Coast4     Multi Polygon   121920      6
7        Hexagon62_5_Coast     Multi Polygon  2729978      3
8        Hexagon83_3_Coast     Multi Polygon  1622139      3
9         Hexagon125_Coast     Multi Polygon   796190      3
               crs_name
1 ETRS89 / UTM zone 33N
2 ETRS89 / UTM zone 33N
3 ETRS89 / UTM zone 33N
4 ETRS89 / UTM zone 33N
5 ETRS89 / UTM zone 33N
6 ETRS89 / UTM zone 33N
7 WGS 84 / UTM zone 33N
8 WGS 84 / UTM zone 33N
9 WGS 84 / UTM zone 33N

SSB500_Coast4 contains the SSB500 grid cells that overlap with the coastline.

coastSSB <- read_sf(coast_path, layer = "SSB500_Coast4")

The SSBid will be the foregn key, linking to ssb_500. We also need a primary key, and therefore I add a unique identifier.

ids <- UUIDgenerate(n = nrow(coastSSB))
anyDuplicated(ids)
coastSSB <- coastSSB |>
  select(ssbid = SSBid)|>
  mutate(havstrand_id = ids)

We don’t need the geometry

coastSSB <- as_tibble(coastSSB) |>
  select(ssbid, havstrand_id)

4.2.1 Define table properties

We will name the table samplingframe_havstrand_2024.

q1 <- "create table sampling_frames.samplingframe_havstrand_2024 (
havstrand_id character varying(50) primary key,
ssbid character varying(50),
CONSTRAINT fk_ssb_500
      FOREIGN KEY (ssbid)
        REFERENCES ssb_grids.ssb_500 (ssbid)
);"

# indices makes the database work faster. It should be added to all tables that are looked up frequently
q2 <- "create index on sampling_frames.samplingframe_havstrand_2024 using btree(ssbid);"
q3 <- "create index on sampling_frames.samplingframe_havstrand_2024 using btree(havstrand_id);"

# sending the queries:
dbSendStatement(con, q1)
dbSendStatement(con, q2)
dbSendStatement(con, q3)

4.2.2 Write to db

Then we write data to the samplingframe_havstrand_2024 table.

write_sf(coastSSB, dsn = con,
         layer = Id(schema = "sampling_frames", table = "samplingframe_havstrand_2024"), 
         append = T)