210127_artikkel/functions/spatial_indeksite_lisamine.R

# ruut::db_schema_tablenames()
# ## Schemade nimekiri
# ruut::db_all_schemas()
# schema = "pk_hiiumaa"
# tabel = tabels_list[139]
# tabels_list = tabels_list[-3]


## Spatial indeksite lisamine
lisa_spatial_indeksid <- function(schema = schema) {
  conf <- ruut::get_config()
  conf$schema <- schema
  tabels_list <- ruut::db_schema_tablenames(conf = conf)
  conn <- ruut::db_connect()
  for (tabel in tabels_list) {
    print(tabel)
    # q <- sprintf("SELECT * FROM %s.%s LIMIT 1;", schema, tabel)
    # cat(sprintf("\n-----------------\n%s\n\n", q))
    # DBI::dbGetQuery(conn, q)
    ## ---------- Vacuum -----------
    q <-
      sprintf("VACUUM (FULL) \"%s\".\"%s\";", schema, tabel)
    cat(sprintf("\n-----------------\n%s\n\n", q))
    DBI::dbSendQuery(conn, q)
    ## ---------- Drop spatial index -----------
    q <-
      sprintf("drop index if exists \"%s\".\"%s_geom_idx\";",
              schema,
              tabel)
    cat(sprintf("\n-----------------\n%s\n\n", q))
    DBI::dbSendQuery(conn, q)
    ## ---------- Add spatial index -----------
    # If geom column exists
    q <- sprintf(
      "SELECT EXISTS (SELECT 1
                   FROM information_schema.columns
                   WHERE table_schema='%s' AND table_name='%s' AND column_name='geom');",
      schema,
      tabel
    )
    cat(sprintf("\n-----------------\n%s\n\n", q))
    res <- DBI::dbGetQuery(conn, q)[1,1]
    if (res) {
      q <-
        sprintf(
          "create index \"%s_geom_idx\" on \"%s\".\"%s\" using GIST (geom);",
          tabel,
          schema,
          tabel
        )
      cat(sprintf("\n-----------------\n%s\n\n", q))
      DBI::dbSendQuery(conn, q)
    }
  }
  DBI::dbDisconnect(conn)
}
# lisa_spatial_indeksid(schema = "eesti")
# lisa_spatial_indeksid(schema = "teeregister_wfs")
# lisa_spatial_indeksid(schema = "maaamet")
# lisa_spatial_indeksid(schema = "osm_shp")
# lisa_spatial_indeksid(schema = "gtfs")
#
# lisa_spatial_indeksid(schema = "pk_hiiumaa")