8 Extras

8.1 Municipality population

Show the code

municipality_pop_geom <- left_join(municipalities_ghs,  population_2010, by = "row") |>
  select(c(shapename,"Population_2010.y", geom)) |>
  rename("population" = "Population_2010.y") |>
  mutate(n_od_sampling = ceiling(municipality_pop_geom$population/sum(municipality_pop_geom$population) *n_samples_ghs))

municipalities_ghs_leaflet <-  sf::st_read(connection,
                                  Id(schema="agile_gis_2025_rs",
                                     table="municipalities_ghs_leaflet"))

tabela1378_bairros <- read_csv("/home/ricardors/heigit_data/tabela1378_bairros.csv")
library(stringr)

filtered_bairros <- data.frame()

for (x in 1:length(municipalities_ghs$shapename)) {
  # Select places that are in the municipality of the AoI
  a= tabela1378_bairros[str_which(tabela1378_bairros$place, municipalities_ghs$shapename[x]),]
  # Filter places that have code of 7 characters
  a_filtered <- a[nchar(a$code) == 7, ] |>
  # Remove space and the (RS) that indicates Rio Grande do Sul
      mutate(place=str_remove(place, " \\(RS\\)"))
  # Create dataset
  filtered_bairros <- bind_rows(filtered_bairros, a_filtered)
}

# Add the population to municipalities_ghs
municipalities_ghs$Population_2010 <- filtered_bairros$Population_2010

# 
municipalities_ghs <- municipalities_ghs |> 
                            mutate(n_od_sampling = ceiling(
                              municipalities_ghs$Population_2010/sum(municipalities_ghs$Population_2010) *n_samples))

8.2 Figures

8.2.1 Using overture building data for the weighted OD figure.

Buildings from overture

Show the code

## Import data
library(overturemapsr)
core_metropolitan_area <- st_read(connection, Id(schema="agile_gis_2025_rs",
                              table="core_metropolitan_area"))
overture_buildings <- record_batch_reader(schema_type = 'building',
                                          bbox = sf::st_bbox(core_metropolitan_area))
## Subset data
overture_roads_subset <- overture_roads |> dplyr::select(c(id, geometry,class,update_time, subtype))
## Export data
### Local file
library(arrow)
library(sfarrow)
sfarrow::st_write_parquet(overture_roads, "overture_roads.parquet")
### Database
library(DBI)
library(nanoarrow)
DBI::dbWriteTable(eisenberg_connection, "overture_roads_subset", overture_roads_subset)

8.2.2 Redundancy base

Show the code

library(units)

## options(scipen=999)

plot_resilience_v2 <-  resilience_visintini_table |>
  mutate(rank = rank(resilience_visintini_table$lack_pre),
         municipality = resilience_visintini_table$shapename,
         letter = LETTERS[1:length(resilience_visintini_table$ds_cnes)],
         area_hm_2 = round(st_area(isochrone_ghs_aoi)),
          lack_norm = (lack_pre - min(lack_pre)) / (max(lack_pre) - min(lack_pre)),
         ds_cnes=stringr::str_to_title(ds_cnes)) |> 
  sf::st_drop_geometry() |> 
  select(c(rank,letter,ds_cnes, municipality, lack_pre,beds, lack_post,lack_norm, beds, code, area_hm_2)) |>
  arrange(lack_pre)

plot_resilience_v2$area_hm_2 <-  units::set_units(plot_resilience_v2$area_hm_2, "hm^2") 
x_mid <- mean(c(max(plot_resilience_v2$lack_pre, na.rm = TRUE), min(plot_resilience_v2$lack_pre, na.rm = TRUE)))
y_mid <- mean(c(max(plot_resilience_v2$area_hm_2, na.rm = TRUE), min(plot_resilience_v2$area_hm_2, na.rm = TRUE)))


ggplot(plot_resilience_v2, aes(x = lack_pre, y = area_hm_2, shape=code, colour=municipality)) +
    geom_vline(xintercept = x_mid, linetype="dashed", linewidth=0.5, colour="darkgray") +
    geom_hline(yintercept = y_mid, linetype="dashed", linewidth=0.5, colour="darkgray") +
    ggplot2::geom_text(aes(label=letter), vjust=-1.5, size=5, show.legend = FALSE) +
    geom_point(aes(shape = code, colour = municipality, fill=municipality, size = beds)) +
   scale_size(range = c(3.5,10)) +
  scale_shape_manual(values = c(23, 24))  +
  expand_limits(y= units::set_units(c(2800, 9500),"hm^2"), x=c(150,850)) +
  scale_x_reverse() +
  annotate("text",
           x = filter(plot_resilience_v2, lack_post ==0)$lack_pre[1],
           y = filter(plot_resilience_v2, lack_post ==0)$area_hm_2[1],
           label=filter(plot_resilience_v2, lack_post ==0)$ds_cnes[1],
           colour="red") +
  annotate("text",
           x = filter(plot_resilience_v2, lack_post ==0)$lack_pre[2],
           y = filter(plot_resilience_v2, lack_post ==0)$area_hm_2[2],
           label=filter(plot_resilience_v2, lack_post ==0)$ds_cnes[2],
           colour="red") +
    annotate("text",
           x = filter(plot_resilience_v2, lack_post ==0)$lack_pre[3],
           y = filter(plot_resilience_v2, lack_post ==0)$area_hm_2[3],
           label=filter(plot_resilience_v2, lack_post ==0)$ds_cnes[3],
           colour="red") +
  xlab("Lack of redundancy") +
  ylab("10-minutes isochrone coverage") +
  theme_minimal() +
  theme(legend.position = "bottom")

8.3 Snapping healthcare facilities

Show the code

CREATE TABLE municipalities_ghs_hospitals AS
WITH municipalities_ghs_united AS(
    SELECT 
    st_union(geom) AS geom_unite 
    FROM 
        municipalities_ghs),
---Use the bounding box to select hospitals
hospital_rs_porto AS (
SELECT 
    h.*
FROM 
    hospitals_bed_rs AS h,
    municipalities_ghs_united geom_ghs_aoi
WHERE st_intersects(h.geom, geom_ghs_aoi.geom_unite))
SELECT DISTINCT ON (h.cd_cnes)
    cd_cnes,
    ds_cnes,
    f.id,
    f.the_geom <-> h.geom AS distance,
    h.geom AS geom_hospital,
    f.the_geom AS geom_node
FROM hospital_rs_porto h
---- Snapping the hospitals to the closest vertices in the network
LEFT JOIN LATERAL
(SELECT 
    id, 
    the_geom
FROM ghs_aoi_net_largest_vertices_pgr AS net
ORDER BY
    net.the_geom <-> h.geom
LIMIT 1) AS f ON true

8.4 Leaflet input:

8.4.1 Hospitals with bed capacity

Show the code

---- Hospitals with ICU and their bed capacity -> ***Input for leaflet map****
EXPLAIN ANALYZE
CREATE TABLE hospital_bed_aux_leaflet AS
SELECT 
    h.fid,
    initcap(h.ds_cnes) AS ds_cnes,
    h.tp_risco,
    h.nm_municip,
    h.ds_bairro,
    h.addr_ct,
    h.geom_hospital,
    h.distance,
        aux_data."QTLEITP1",
    aux_data."QTLEITP2",
    aux_data."QTLEITP3",
    "QTLEITP1"+"QTLEITP2"+"QTLEITP3" AS beds,
    aux_data."NAT_JUR",
    CASE
        WHEN "NAT_JUR" IN (3069,3999)
        THEN 'Private' ELSE 'Public'  
    END AS nat_jur_cat,
    initcap(h.nm_razao_s) AS nm_razao_s
FROM 
    etlcnes_hospital_selected AS aux_data
JOIN
    municipalities_ghs_hospitals AS h
ON
    h.cd_cnes = aux_data."CNES"; ---- Execution Time: 43.901 ms
    
--- Municipality of the AoI with additonial data from:
------- A) IBGR: "https://www.ibge.gov.br/cidades-e-estados/rs/"
--------B) DEE-SPGG: "https://mup.rs.gov.br/" 
--- -> ***Input for leaflet map****
EXPLAIN ANALYZE
CREATE TABLE municipalities_ghs_leaflet AS
SELECT shapename,
     wkb_geometry
FROM municipalities_ghs; ----  8.511 ms
--- Add new fields (A,B)
ALTER TABLE municipalities_ghs_leaflet
    ADD COLUMN "Pop.Aff" text,
    ADD COLUMN "GDP" text ; --- 0.012S execute time
--- Populate columns
EXPLAIN ANALYZE
    UPDATE municipalities_ghs_leaflet
SET "Pop.Aff" = data."Pop.Aff",
    "GDP" = data."GDP"
FROM (VALUES
    ('Alvorada', '26K (29%)', '15K'),
    ('Cachoeirinha', '12K (9%)', '49K'),
    ('Canoas', '157K (45%)', '63K'),
    ('Esteio', '20K (26%)', '45K'),
    ('Gravataí', '6K (2%)', '36K'),
    ('Nova Santa Rita', '7K (24%)', '81K'),
    ('Porto Alegre', '125K (9%)', '55K'),
    ('Sapucaia do Sul', '6K (4%)', '29K'),
    ('Viamão', '2K (1%)', '17K')
) AS data(shapename, "Pop.Aff", "GDP")
WHERE municipalities_ghs_leaflet.shapename = data.shapename; --- Execution Time: 0.596 ms

8.5 Finding urban arterias in ORS using OSM address

Show the code

planet_osm_roads <- sf::st_read(connection,
                                Id(schema="public", table = "planet_osm_roads"))
library(stringr)
library(sf)
freeway <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Freeway"), ]
avenida_salgado <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Avenida Senador Salgado Filho"), ]
castelo_branco <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Castello Branco"), ]
pereira_de_vargas <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Pereira de Vargas"), ]
rua_florianopolis <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Rua Florianópolis"), ]

sf::st_write(dsn=connection,obj=freeway)
sf::st_write(dsn=connection,obj=castelo_branco)
sf::st_write(dsn=connection,obj=pereira_de_vargas)
sf::st_write(dsn=connection,obj=rua_florianopolis)
sf::st_write(dsn=connection,obj=avenida_salgado)


avenida_salgado_buffer <- st_buffer(avenida_salgado, dist = 30)
freeway_buffer <- st_buffer(freeway, dist = 30)
castelo_branco_buffer <- st_buffer(castelo_branco, dist = 30)
pereira_de_vargas_buffer <- st_buffer(pereira_de_vargas, dist = 30)
rua_florianopolis_buffer <- st_buffer(rua_florianopolis, dist = 30)
rua_sanga_funda_buffer <- st_buffer(rua_sanga_funda, dist = 30)

## Freeway
ic_pre_freeway <-  st_intersection(st_transform(local_connectivity_pre,3857), freeway_buffer)
ic_post_freeway <-  st_intersection(st_transform(local_connectivity_post,3857), freeway_buffer)
(sum(ic_post_freeway$centrality) - sum(ic_pre_freeway$centrality))/sum(ic_pre_freeway$centrality)*100

## avenida_salgado
ic_pre_avenida_salgado <-  st_intersection(st_transform(local_connectivity_pre,3857), avenida_salgado_buffer)
ic_post_avenida_salgado <-  st_intersection(st_transform(local_connectivity_post,3857), avenida_salgado_buffer)
(sum(ic_post_avenida_salgado$centrality) - sum(ic_pre_avenida_salgado$centrality))/sum(ic_pre_avenida_salgado$centrality)*100

## pereira_de_vargas
ic_pre_pereira_de_vargas <-  st_intersection(st_transform(local_connectivity_pre,3857), pereira_de_vargas_buffer)
ic_post_pereira_de_vargas <-  st_intersection(st_transform(local_connectivity_post,3857), pereira_de_vargas_buffer)
(sum(ic_post_pereira_de_vargas$centrality) - sum(ic_pre_pereira_de_vargas$centrality))/sum(ic_pre_pereira_de_vargas$centrality)*100
## castelo_branco
ic_pre_castelo_branco <-  st_intersection(st_transform(local_connectivity_pre,3857), castelo_branco_buffer)
ic_post_castelo_branco <-  st_intersection(st_transform(local_connectivity_post,3857), castelo_branco_buffer)
(sum(ic_post_castelo_branco$centrality) - sum(ic_pre_castelo_branco$centrality))/sum(ic_pre_castelo_branco$centrality)*100
## rua_florianopolis
ic_pre_rua_florianopolis <-  st_intersection(st_transform(local_connectivity_pre,3857), rua_florianopolis)
ic_post_rua_florianopolis <-  st_intersection(st_transform(local_connectivity_post,3857), rua_florianopolis)
(sum(ic_post_rua_florianopolis$centrality) - sum(ic_pre_rua_florianopolis$centrality))/sum(ic_pre_rua_florianopolis$centrality)*100

[ACR-7] How to create regular samples to calculate the Core Metropolitan Connectivity

EXPLAIN ANALYZE
CREATE TABLE regular_point_od AS (
WITH multipoint_regular AS(
SELECT 
    I_Grid_Point_Series(geom, 0.037,0.037, false) AS geom
    FROM core_metropolitan_bbox AS geom),
point_regular AS(
SELECT 
    st_setsrid((st_dump(geom)).geom, 4326)::geometry(Point, 4326) AS geom
FROM  multipoint_regular)
SELECT 
    row_number() over() AS id,
    geom
FROM 
    point_regular);
--- Time: 505.147 ms

# Extras ## Municipality population ```{r} #| echo: true #| eval: false municipality_pop_geom <- left_join(municipalities_ghs, population_2010, by = "row") |> select(c(shapename,"Population_2010.y", geom)) |> rename("population" = "Population_2010.y") |> mutate(n_od_sampling = ceiling(municipality_pop_geom$population/sum(municipality_pop_geom$population) *n_samples_ghs)) municipalities_ghs_leaflet <- sf::st_read(connection, Id(schema="agile_gis_2025_rs", table="municipalities_ghs_leaflet")) tabela1378_bairros <- read_csv("/home/ricardors/heigit_data/tabela1378_bairros.csv") library(stringr) filtered_bairros <- data.frame() for (x in 1:length(municipalities_ghs$shapename)) { # Select places that are in the municipality of the AoI a= tabela1378_bairros[str_which(tabela1378_bairros$place, municipalities_ghs$shapename[x]),] # Filter places that have code of 7 characters a_filtered <- a[nchar(a$code) == 7, ] |> # Remove space and the (RS) that indicates Rio Grande do Sul mutate(place=str_remove(place, " \$RS\$")) # Create dataset filtered_bairros <- bind_rows(filtered_bairros, a_filtered) } # Add the population to municipalities_ghs municipalities_ghs$Population_2010 <- filtered_bairros$Population_2010 # municipalities_ghs <- municipalities_ghs |> mutate(n_od_sampling = ceiling( municipalities_ghs$Population_2010/sum(municipalities_ghs$Population_2010) *n_samples)) ``` ## Figures ### Using overture building data for the weighted OD figure. Buildings from overture ```{r} #| eval: false #| echo: true ## Import data library(overturemapsr) core_metropolitan_area <- st_read(connection, Id(schema="agile_gis_2025_rs", table="core_metropolitan_area")) overture_buildings <- record_batch_reader(schema_type = 'building', bbox = sf::st_bbox(core_metropolitan_area)) ## Subset data overture_roads_subset <- overture_roads |> dplyr::select(c(id, geometry,class,update_time, subtype)) ## Export data ### Local file library(arrow) library(sfarrow) sfarrow::st_write_parquet(overture_roads, "overture_roads.parquet") ### Database library(DBI) library(nanoarrow) DBI::dbWriteTable(eisenberg_connection, "overture_roads_subset", overture_roads_subset) ``` ### Redundancy base ```{r} #| eval: false #| echo: true library(units) ## options(scipen=999) plot_resilience_v2 <- resilience_visintini_table |> mutate(rank = rank(resilience_visintini_table$lack_pre), municipality = resilience_visintini_table$shapename, letter = LETTERS[1:length(resilience_visintini_table$ds_cnes)], area_hm_2 = round(st_area(isochrone_ghs_aoi)), lack_norm = (lack_pre - min(lack_pre)) / (max(lack_pre) - min(lack_pre)), ds_cnes=stringr::str_to_title(ds_cnes)) |> sf::st_drop_geometry() |> select(c(rank,letter,ds_cnes, municipality, lack_pre,beds, lack_post,lack_norm, beds, code, area_hm_2)) |> arrange(lack_pre) plot_resilience_v2$area_hm_2 <- units::set_units(plot_resilience_v2$area_hm_2, "hm^2") x_mid <- mean(c(max(plot_resilience_v2$lack_pre, na.rm = TRUE), min(plot_resilience_v2$lack_pre, na.rm = TRUE))) y_mid <- mean(c(max(plot_resilience_v2$area_hm_2, na.rm = TRUE), min(plot_resilience_v2$area_hm_2, na.rm = TRUE))) ggplot(plot_resilience_v2, aes(x = lack_pre, y = area_hm_2, shape=code, colour=municipality)) + geom_vline(xintercept = x_mid, linetype="dashed", linewidth=0.5, colour="darkgray") + geom_hline(yintercept = y_mid, linetype="dashed", linewidth=0.5, colour="darkgray") + ggplot2::geom_text(aes(label=letter), vjust=-1.5, size=5, show.legend = FALSE) + geom_point(aes(shape = code, colour = municipality, fill=municipality, size = beds)) + scale_size(range = c(3.5,10)) + scale_shape_manual(values = c(23, 24)) + expand_limits(y= units::set_units(c(2800, 9500),"hm^2"), x=c(150,850)) + scale_x_reverse() + annotate("text", x = filter(plot_resilience_v2, lack_post ==0)$lack_pre[1], y = filter(plot_resilience_v2, lack_post ==0)$area_hm_2[1], label=filter(plot_resilience_v2, lack_post ==0)$ds_cnes[1], colour="red") + annotate("text", x = filter(plot_resilience_v2, lack_post ==0)$lack_pre[2], y = filter(plot_resilience_v2, lack_post ==0)$area_hm_2[2], label=filter(plot_resilience_v2, lack_post ==0)$ds_cnes[2], colour="red") + annotate("text", x = filter(plot_resilience_v2, lack_post ==0)$lack_pre[3], y = filter(plot_resilience_v2, lack_post ==0)$area_hm_2[3], label=filter(plot_resilience_v2, lack_post ==0)$ds_cnes[3], colour="red") + xlab("Lack of redundancy") + ylab("10-minutes isochrone coverage") + theme_minimal() + theme(legend.position = "bottom") ``` ## Snapping healthcare facilities ```{sql} #| eval: false #| echo: true CREATE TABLE municipalities_ghs_hospitals AS WITH municipalities_ghs_united AS( SELECT st_union(geom) AS geom_unite FROM municipalities_ghs), ---Use the bounding box to select hospitals hospital_rs_porto AS ( SELECT h.* FROM hospitals_bed_rs AS h, municipalities_ghs_united geom_ghs_aoi WHERE st_intersects(h.geom, geom_ghs_aoi.geom_unite)) SELECT DISTINCT ON (h.cd_cnes) cd_cnes, ds_cnes, f.id, f.the_geom <-> h.geom AS distance, h.geom AS geom_hospital, f.the_geom AS geom_node FROM hospital_rs_porto h ---- Snapping the hospitals to the closest vertices in the network LEFT JOIN LATERAL (SELECT id, the_geom FROM ghs_aoi_net_largest_vertices_pgr AS net ORDER BY net.the_geom <-> h.geom LIMIT 1) AS f ON true ``` ```{sql} #| eval: false #| echo: false SELECT * FROM ghs_aoi_net_largest; CREATE TABLE public.ghs_aoi_net_largest_renamed AS SELECT id AS id, "fromId" AS source, "toId" AS target, weight AS cost, "undrctI" , "bdrctId", geom AS the_geom FROM ghs_aoi_net_largest ; SELECT pgr_createverticesTable('public.ghs_aoi_net_largest_renamed'); SELECT * FROM hospitals_poi; select * from municipalities_ghs; CREATE TABLE municipalities_ghs_hospitals AS WITH municipalities_ghs_united AS( SELECT st_union(wkb_geometry) AS geom_unite FROM municipalities_ghs), ---Use the bounding box to select hospitals hospital_rs_porto AS ( SELECT h.* FROM hospitals_poi AS h, municipalities_ghs_united geom_ghs_aoi WHERE st_intersects(h.geom, geom_ghs_aoi.geom_unite)) SELECT DISTINCT ON (h.cd_cnes) cd_cnes, ds_cnes, f.id, f.the_geom <-> h.geom AS distance, h.geom AS geom_hospital, f.the_geom AS geom_node FROM hospital_rs_porto h ---- Snapping the hospitals to the closest vertices in the network LEFT JOIN LATERAL (SELECT id, the_geom FROM ghs_aoi_net_largest_vertices_pgr AS net ORDER BY net.the_geom <-> h.geom LIMIT 1) AS f ON true CREATE TABLE public.ghs_aoi_net_largest_renamed_vertices_pgr AS SELECT * FROM ghs_aoi_net_largest_renamed_vertices_pgr; ---- creating indexes agile_gis --- ghs_aoi_net_largest_renamed CREATE INDEX idx_ghs_aoi_net_largest_renamed ON ghs_aoi_net_largest_renamed USING gist(the_geom); CREATE INDEX idx_ghs_aoi_net_largest_renamed_source ON ghs_aoi_net_largest_renamed USING btree(source); CREATE INDEX idx_ghs_aoi_net_largest_renamed_target ON ghs_aoi_net_largest_renamed USING btree(target); CREATE INDEX idx_ghs_aoi_net_largest_renamed_id ON ghs_aoi_net_largest_renamed USING btree(id); --- ghs_aoi_net_largest_vertices_pgr CREATE INDEX idx_ghs_aoi_net_largest_vertices_pgr ON ghs_aoi_net_largest_renamed_vertices_pgr USING gist(the_geom); CREATE INDEX idx_ghs_aoi_net_largest_vertices_id ON ghs_aoi_net_largest_renamed_vertices_pgr USING btree(id); --- hospitals_poi CREATE INDEX idx_municipalities_ghs_hospitals_id ON municipalities_ghs_hospitals USING btree(id); ---- creating indexes public --- ghs_aoi_net_largest_renamed CREATE INDEX idx_ghs_aoi_net_largest_renamed ON public.ghs_aoi_net_largest_renamed USING gist(the_geom); CREATE INDEX idx_ghs_aoi_net_largest_renamed_source ON public.ghs_aoi_net_largest_renamed USING btree(source); CREATE INDEX idx_ghs_aoi_net_largest_renamed_target ON public.ghs_aoi_net_largest_renamed USING btree(target); CREATE INDEX idx_ghs_aoi_net_largest_renamed_id ON public.ghs_aoi_net_largest_renamed USING btree(id); --- ghs_aoi_net_largest_vertices_pgr CREATE INDEX idx_ghs_aoi_net_largest_vertices_pgr ON public.ghs_aoi_net_largest_renamed_vertices_pgr USING gist(the_geom); CREATE INDEX idx_ghs_aoi_net_largest_vertices_id ON public.ghs_aoi_net_largest_renamed_vertices_pgr USING btree(id); --- hospitals_poi CREATE INDEX idx_municipalities_ghs_hospitals_id ON public.municipalities_ghs_hospitals USING btree(id); -- municipalities_ghs_hospitals SELECT * FROM ghs_aoi_net_largest_vertices_pgr; SELECT * FROM ghs_aoi_net_largest_renamed; SELECT * FROM municipalities_ghs_hospitals; CREATE TABLE public.regular_sampling_isochrones_snapped_ghs_aoi AS SELECT * FROM regular_sampling_isochrones_snapped_ghs_aoi; SELECT * FROM table_end_v2; ``` ## Leaflet input: ### Hospitals with bed capacity ```{sql} #| eval: false #| echo: true ---- Hospitals with ICU and their bed capacity -> ***Input for leaflet map**** EXPLAIN ANALYZE CREATE TABLE hospital_bed_aux_leaflet AS SELECT h.fid, initcap(h.ds_cnes) AS ds_cnes, h.tp_risco, h.nm_municip, h.ds_bairro, h.addr_ct, h.geom_hospital, h.distance, aux_data."QTLEITP1", aux_data."QTLEITP2", aux_data."QTLEITP3", "QTLEITP1"+"QTLEITP2"+"QTLEITP3" AS beds, aux_data."NAT_JUR", CASE WHEN "NAT_JUR" IN (3069,3999) THEN 'Private' ELSE 'Public' END AS nat_jur_cat, initcap(h.nm_razao_s) AS nm_razao_s FROM etlcnes_hospital_selected AS aux_data JOIN municipalities_ghs_hospitals AS h ON h.cd_cnes = aux_data."CNES"; ---- Execution Time: 43.901 ms --- Municipality of the AoI with additonial data from: ------- A) IBGR: "https://www.ibge.gov.br/cidades-e-estados/rs/" --------B) DEE-SPGG: "https://mup.rs.gov.br/" --- -> ***Input for leaflet map**** EXPLAIN ANALYZE CREATE TABLE municipalities_ghs_leaflet AS SELECT shapename, wkb_geometry FROM municipalities_ghs; ---- 8.511 ms --- Add new fields (A,B) ALTER TABLE municipalities_ghs_leaflet ADD COLUMN "Pop.Aff" text, ADD COLUMN "GDP" text ; --- 0.012S execute time --- Populate columns EXPLAIN ANALYZE UPDATE municipalities_ghs_leaflet SET "Pop.Aff" = data."Pop.Aff", "GDP" = data."GDP" FROM (VALUES ('Alvorada', '26K (29%)', '15K'), ('Cachoeirinha', '12K (9%)', '49K'), ('Canoas', '157K (45%)', '63K'), ('Esteio', '20K (26%)', '45K'), ('Gravataí', '6K (2%)', '36K'), ('Nova Santa Rita', '7K (24%)', '81K'), ('Porto Alegre', '125K (9%)', '55K'), ('Sapucaia do Sul', '6K (4%)', '29K'), ('Viamão', '2K (1%)', '17K') ) AS data(shapename, "Pop.Aff", "GDP") WHERE municipalities_ghs_leaflet.shapename = data.shapename; --- Execution Time: 0.596 ms ``` ## Finding urban arterias in ORS using OSM address ```{r} #| eval: false #| echo: true planet_osm_roads <- sf::st_read(connection, Id(schema="public", table = "planet_osm_roads")) library(stringr) library(sf) freeway <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Freeway"), ] avenida_salgado <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Avenida Senador Salgado Filho"), ] castelo_branco <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Castello Branco"), ] pereira_de_vargas <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Pereira de Vargas"), ] rua_florianopolis <- planet_osm_roads[stringr::str_which(planet_osm_roads$name, "Rua Florianópolis"), ] sf::st_write(dsn=connection,obj=freeway) sf::st_write(dsn=connection,obj=castelo_branco) sf::st_write(dsn=connection,obj=pereira_de_vargas) sf::st_write(dsn=connection,obj=rua_florianopolis) sf::st_write(dsn=connection,obj=avenida_salgado) avenida_salgado_buffer <- st_buffer(avenida_salgado, dist = 30) freeway_buffer <- st_buffer(freeway, dist = 30) castelo_branco_buffer <- st_buffer(castelo_branco, dist = 30) pereira_de_vargas_buffer <- st_buffer(pereira_de_vargas, dist = 30) rua_florianopolis_buffer <- st_buffer(rua_florianopolis, dist = 30) rua_sanga_funda_buffer <- st_buffer(rua_sanga_funda, dist = 30) ## Freeway ic_pre_freeway <- st_intersection(st_transform(local_connectivity_pre,3857), freeway_buffer) ic_post_freeway <- st_intersection(st_transform(local_connectivity_post,3857), freeway_buffer) (sum(ic_post_freeway$centrality) - sum(ic_pre_freeway$centrality))/sum(ic_pre_freeway$centrality)*100 ## avenida_salgado ic_pre_avenida_salgado <- st_intersection(st_transform(local_connectivity_pre,3857), avenida_salgado_buffer) ic_post_avenida_salgado <- st_intersection(st_transform(local_connectivity_post,3857), avenida_salgado_buffer) (sum(ic_post_avenida_salgado$centrality) - sum(ic_pre_avenida_salgado$centrality))/sum(ic_pre_avenida_salgado$centrality)*100 ## pereira_de_vargas ic_pre_pereira_de_vargas <- st_intersection(st_transform(local_connectivity_pre,3857), pereira_de_vargas_buffer) ic_post_pereira_de_vargas <- st_intersection(st_transform(local_connectivity_post,3857), pereira_de_vargas_buffer) (sum(ic_post_pereira_de_vargas$centrality) - sum(ic_pre_pereira_de_vargas$centrality))/sum(ic_pre_pereira_de_vargas$centrality)*100 ## castelo_branco ic_pre_castelo_branco <- st_intersection(st_transform(local_connectivity_pre,3857), castelo_branco_buffer) ic_post_castelo_branco <- st_intersection(st_transform(local_connectivity_post,3857), castelo_branco_buffer) (sum(ic_post_castelo_branco$centrality) - sum(ic_pre_castelo_branco$centrality))/sum(ic_pre_castelo_branco$centrality)*100 ## rua_florianopolis ic_pre_rua_florianopolis <- st_intersection(st_transform(local_connectivity_pre,3857), rua_florianopolis) ic_post_rua_florianopolis <- st_intersection(st_transform(local_connectivity_post,3857), rua_florianopolis) (sum(ic_post_rua_florianopolis$centrality) - sum(ic_pre_rua_florianopolis$centrality))/sum(ic_pre_rua_florianopolis$centrality)*100 ``` ```{sql} #| eval: false #| echo: true #| code-summary: "[ACR-7] How to create regular samples to calculate the Core Metropolitan Connectivity" EXPLAIN ANALYZE CREATE TABLE regular_point_od AS ( WITH multipoint_regular AS( SELECT I_Grid_Point_Series(geom, 0.037,0.037, false) AS geom FROM core_metropolitan_bbox AS geom), point_regular AS( SELECT st_setsrid((st_dump(geom)).geom, 4326)::geometry(Point, 4326) AS geom FROM multipoint_regular) SELECT row_number() over() AS id, geom FROM point_regular); --- Time: 505.147 ms ```