3  Read data from the database

Now that we have added data to the database, we can also read it back.

coast_sample <- dplyr::tbl(con, dbplyr::in_schema("sampling_frames", "samplingframe_havstrand_2024"))
coast_sample

# Source:   SQL [?? x 2]
# Database: postgres  [anders.kolstad@t2lippgsql03:5432/ano_moduler]
   havstrand_id                         ssbid         
   <chr>                                <chr>         
 1 4de8478a-d2c8-4730-83dd-12ac25d4fa07 22495006594500
 2 bc9a80d7-c434-47e9-a89a-7f7a3f59f625 22495006595000
 3 ce7166b6-cb49-499a-a99f-9da0e274abd8 22495006595500
 4 e56036c5-7fe4-461a-a022-44367119acac 22495006596000
 5 1f592dac-2fb7-49e5-9aa9-fc0cf9e9184f 22490006596500
 6 20394fe8-305c-4c39-add9-f013295ca932 22495006596500
 7 3d3ffe94-0400-4c1c-9a0f-62f4d6c29c4d 22490006597000
 8 4d199401-dd20-48ef-b6c6-8de00b9eb9a4 22495006597000
 9 a34df6f4-8f92-4976-b7ae-94b176fa73b1 22625006542000
10 89e8296e-e076-4519-b916-60c5ef922292 22625006542500
# ℹ more rows

This data only exists remotely still. We need to use collect to bring it down to our local machine. At the same time we can use dplyr pipelines to filter the data.

coast_sample |>
  dplyr::mutate(id = row_number()) |>
  dplyr::slice_min(n = 8, order_by = id) |>
  dplyr::collect()

# A tibble: 8 × 3
  havstrand_id                         ssbid               id
  <chr>                                <chr>          <int64>
1 4de8478a-d2c8-4730-83dd-12ac25d4fa07 22495006594500       1
2 bc9a80d7-c434-47e9-a89a-7f7a3f59f625 22495006595000       2
3 ce7166b6-cb49-499a-a99f-9da0e274abd8 22495006595500       3
4 e56036c5-7fe4-461a-a022-44367119acac 22495006596000       4
5 1f592dac-2fb7-49e5-9aa9-fc0cf9e9184f 22490006596500       5
6 20394fe8-305c-4c39-add9-f013295ca932 22495006596500       6
7 3d3ffe94-0400-4c1c-9a0f-62f4d6c29c4d 22490006597000       7
8 4d199401-dd20-48ef-b6c6-8de00b9eb9a4 22495006597000       8

We can also get the geometries, which are stored in the foreign table. To do this we can use the dm package.

We first create a dm object from the connection object. Then we can view data easily.

dm <- dm::dm_from_con(con,
  table_names = c("ssb_500",
                  "samplingframe_havstrand_2024"))

Keys queried successfully.
ℹ Use `learn_keys = TRUE` to mute this message.

dm$samplingframe_havstrand_2024

# Source:   table<"ano_moduler"."sampling_frames"."samplingframe_havstrand_2024"> [?? x 2]
# Database: postgres  [anders.kolstad@t2lippgsql03:5432/ano_moduler]
   havstrand_id                         ssbid         
   <chr>                                <chr>         
 1 4de8478a-d2c8-4730-83dd-12ac25d4fa07 22495006594500
 2 bc9a80d7-c434-47e9-a89a-7f7a3f59f625 22495006595000
 3 ce7166b6-cb49-499a-a99f-9da0e274abd8 22495006595500
 4 e56036c5-7fe4-461a-a022-44367119acac 22495006596000
 5 1f592dac-2fb7-49e5-9aa9-fc0cf9e9184f 22490006596500
 6 20394fe8-305c-4c39-add9-f013295ca932 22495006596500
 7 3d3ffe94-0400-4c1c-9a0f-62f4d6c29c4d 22490006597000
 8 4d199401-dd20-48ef-b6c6-8de00b9eb9a4 22495006597000
 9 a34df6f4-8f92-4976-b7ae-94b176fa73b1 22625006542000
10 89e8296e-e076-4519-b916-60c5ef922292 22625006542500
# ℹ more rows

DM learns the relationshios between tables by reading the constraints:

dm |>
  dm::dm_set_colors(
    darkgreen = samplingframe_havstrand_2024, 
    darkblue = ssb_500) |>
  dm::dm_draw() 

Then we can read the data back, including the geometries from the parent table.

dm |>
  dm::dm_flatten_to_tbl(samplingframe_havstrand_2024,
                    .recursive = TRUE)

# Source:   SQL [?? x 3]
# Database: postgres  [anders.kolstad@t2lippgsql03:5432/ano_moduler]
   havstrand_id                         ssbid          geom                     
   <chr>                                <chr>          <pq_gmtry>               
 1 4de8478a-d2c8-4730-83dd-12ac25d4fa07 22495006594500 0103000020E9640000010000…
 2 bc9a80d7-c434-47e9-a89a-7f7a3f59f625 22495006595000 0103000020E9640000010000…
 3 ce7166b6-cb49-499a-a99f-9da0e274abd8 22495006595500 0103000020E9640000010000…
 4 e56036c5-7fe4-461a-a022-44367119acac 22495006596000 0103000020E9640000010000…
 5 1f592dac-2fb7-49e5-9aa9-fc0cf9e9184f 22490006596500 0103000020E9640000010000…
 6 20394fe8-305c-4c39-add9-f013295ca932 22495006596500 0103000020E9640000010000…
 7 3d3ffe94-0400-4c1c-9a0f-62f4d6c29c4d 22490006597000 0103000020E9640000010000…
 8 4d199401-dd20-48ef-b6c6-8de00b9eb9a4 22495006597000 0103000020E9640000010000…
 9 a34df6f4-8f92-4976-b7ae-94b176fa73b1 22625006542000 0103000020E9640000010000…
10 89e8296e-e076-4519-b916-60c5ef922292 22625006542500 0103000020E9640000010000…
# ℹ more rows

The geometry column is a bit weird though. Not sure what is happening there, but see this.