SPUGeo

class: center, middle, inverse, title-slide

# SPUGeo
## Banco de Dados Espacial
### Luiz Fernando Palin Droubi
### SPU/SC
### 2019/06/03 (updated: 2019-06-06)

---

---
class: inverse, center, middle

# Introdução

---
# Banco de Dados Espacial (BDE)

1. PostgreSQL
  - Esquemas:
      + Espaço Aquático
      + Demarcação
      + Etc.
2. PostGIS
  - Extensão ao PostGreSQL
  - Adicionada ao Banco de Dados
3. Administração
  - PgAdmin
    + Permite a criação do BDE, esquemas, tabelas, etc.
    + Permite a criação de usuários
    + Permite a criação de Queries
    + Permite a edição e inserção de dados, porém não ideal.
4. Armazenamento em nuvem
  - Digital Ocean
    + IP: 104.248.57.29
    + porta: 5432
  - Google Cloud, Amazon AWS, Uol Cloud Computing, etc.
  
---
# Edição do BDE

1. Possibilidades:
  - QGIS
      + Preferível por ser possível a visualização e edição simultâneas
      + Controle de versão disponível através de plugins (versioning, pgversion)
      + Na SPU, problemas com proxy.
  - R
      + Possibilidade de automação
      + Possibilidade de controle de versão (git)
      + Pode ser utilizado na nuvem
      + P. ex.: https://rstudio.cloud
  - javascript? Outros?
  
2. 
  
---
# Conexão do BDE no R

```r
conn <- RPostgreSQL::dbConnect("PostgreSQL",
                               host = "104.248.57.29", 
                               dbname = "spugeo",
                               port = "5432",
                               user = "postgres", 
                               password = "********")
```

---

## Criar tabelas no BDE

```sql
CREATE TABLE espacoaquatico.certdisp
(
  ID SERIAL,
  interessado varchar NOT NULL,
  nup varchar,
  ref integer,
  concedida boolean,  
  area numeric(10,2),
  geom geometry,
  CONSTRAINT certdisp_pkey PRIMARY KEY (ID)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE espacoaquatico.certdisp
  OWNER TO lfpdroubi;
  ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(geom) = (31982));
```

---
# Listagem das tabelas do BDE

```r
library(rpostgis)
```

```
## Loading required package: RPostgreSQL
```

```
## Loading required package: DBI
```

```r
# Objetos Vetoriais
pgListGeom(conn)
```

```
##      schema_name table_name geom_column geometry_type     type
## 1 espacoaquatico   certdisp        geom      GEOMETRY GEOMETRY
## 2 espacoaquatico     cessao        geom      GEOMETRY GEOMETRY
```

```r
#Objetos Raster
pgListRast(conn)
```

```
## data frame with 0 columns and 0 rows
```

---

## Informações sobre uma tabela específica do BDE

```r
dbTableInfo(conn, name = c("espacoaquatico", "certdisp"))
```

```
##   column_name         data_type is_nullable character_maximum_length
## 1          id           integer          NO                       NA
## 2 interessado character varying          NO                       NA
## 3         nup character varying         YES                       NA
## 4   protocolo character varying          NO                       NA
## 5         ref           integer         YES                       NA
## 6   concedida           boolean         YES                       NA
## 7        area           numeric         YES                       NA
## 8   municipio           integer         YES                       NA
## 9        geom      USER-DEFINED         YES                       NA
```

---
# Alimentação do BDE

## Digitação da Geometria

```r
# Digitar Coordenadas dos vértices
#
#                             E             N
coords <- matrix(data = c(740231.895, 7102091.398, #1
                          740713.725, 7102258.548, #2
                          740809.427, 7101982.676, #3
                          740327.596, 7101815.526, #4
                          740231.895, 7102091.398),#1
                 ncol = 2, byrow = TRUE)
```

---
# Alimentação do BDE

## Criação da Feição

```r
# Atentar para a definição do CRS apropriado
# 
#
# Exemplos:
# WGS 84 = 4326
# SAD 69 22S = 29192
# SIRGAS 2000 22S = 31982
#
library(sp)
pl <- Polygon(coords)
spl <- SpatialPolygons(list(Polygons(list(pl), ID = 1)),
                      proj4string = CRS("+init=epsg:31982")) 
```

---
# Alimentação do BDE

## Digitação dos metadados

```r
#
# As colunas dos metadados devem ser as mesmas presentes no BDE.
#
dados <- data.frame(
  interessado = "DSP Empreendimentos Imobiliários EIRELI",
  nup = "04972.0097273/2018-11",
  ref = 8385248,
  concedida = TRUE,
  area = pl@area)
```

## União dos metadados e da geometria

```r
spl_df <- SpatialPolygonsDataFrame(spl, dados)
```

---
# Alimentação do BDE

## Adicionar Tabela ao BDE

```r
# Caso as colunas do objeto criado confira com as colunas do BDE,
# o resultado será positivo.
#
pgInsert(conn = conn, 
         name = c(schema="espacoaquatico", table = "certdisp"), 
         data.obj = spl_df)
```

---
# Alimentação do BDE

## Desconectar do BDE

```r
dbDisconnect(conn)
```

```
## [1] TRUE
```

---
# Obtenção dos dados contidos no BDE

## Conexão

---
# Listagem das tabelas do BDE

```r
library(rpostgis)
# Objetos Vetoriais
pgListGeom(conn)
```

```r
#Objetos Raster
pgListRast(conn)
```

```
## data frame with 0 columns and 0 rows
```

---

## Informações sobre uma tabela específica do BDE

```r
dbTableInfo(conn, name = c("espacoaquatico", "certdisp"))
```

---
# Obtenção dos dados contidos no BDE

Os dados podem ser obtidos no R com queries SQL.

Por exemplo, para baixar do BDE apenas as cessões de espaço aquático do município de São Francisco do Sul (município 8319), faz-se:

```r
library(rpostgis)
library(sf)
```

```
## Linking to GEOS 3.6.2, GDAL 2.2.3, PROJ 4.9.3
```

```r
# Ler no BDE todos os polígonos de cessão e certidão de disponibilidade
# que estejam localizados nos seguintes municípios:
# 8319 (SFS) ou 9985 (Itapoá)
# E transformar as coordenadas de SIRGAS2000 (31982) para WGS84 (4326)
cessao <- st_read(conn, 
                  query = "SELECT * FROM espacoaquatico.cessao 
                  WHERE municipio = 8319 OR municipio = 9985;") %>%
    st_transform(crs = 4326)
certdisp <- st_read(conn, 
                  query = "SELECT * FROM espacoaquatico.certdisp
                  WHERE municipio = 8319 OR municipio = 9985;") %>%
    st_transform(crs = 4326)
```

---
# Visualização dos dados contidos no BDE

```r
certdisp %>%
  st_drop_geometry() %>%
  DT::datatable(rownames = FALSE, options = list(autoWidth = F)) %>%
  DT::formatStyle(columns = setdiff(colnames(certdisp), "geom"), fontSize = '60%')
```

<div id="htmlwidget-80210d3448e3dbe00eb3" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-80210d3448e3dbe00eb3">{"x":{"filter":"none","data":[[1],["DSP Empreendimentos Imobiliários EIRELI"],["04972.0097273/2018-11"],["SC03017/2018"],[8385248],[true],[148920.05],[9985]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th>id<\/th>\n      <th>interessado<\/th>\n      <th>nup<\/th>\n      <th>protocolo<\/th>\n      <th>ref<\/th>\n      <th>concedida<\/th>\n      <th>area<\/th>\n      <th>municipio<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"autoWidth":false,"columnDefs":[{"className":"dt-right","targets":[0,4,6,7]}],"order":[],"orderClasses":false,"rowCallback":"function(row, data) {\nvar value=data[0]; $(this.api().cell(row, 0).node()).css({'font-size':'60%'});\nvar value=data[1]; $(this.api().cell(row, 1).node()).css({'font-size':'60%'});\nvar value=data[2]; $(this.api().cell(row, 2).node()).css({'font-size':'60%'});\nvar value=data[3]; $(this.api().cell(row, 3).node()).css({'font-size':'60%'});\nvar value=data[4]; $(this.api().cell(row, 4).node()).css({'font-size':'60%'});\nvar value=data[5]; $(this.api().cell(row, 5).node()).css({'font-size':'60%'});\nvar value=data[6]; $(this.api().cell(row, 6).node()).css({'font-size':'60%'});\nvar value=data[7]; $(this.api().cell(row, 7).node()).css({'font-size':'60%'});\n}"}},"evals":["options.rowCallback"],"jsHooks":[]}</script>

---
# Confecção de mapas

```r
library(leaflet)
cessao %>%
  leaflet(options = leafletOptions(zoomControl = FALSE)) %>%
  # Base Groups
  addTiles(group = "OSM (default)") %>%
  addProviderTiles(providers$Esri.NatGeoWorldMap, group = "Esri") %>%
  addProviderTiles(providers$Stamen.TonerLite, group = "Toner Lite") %>%
  # Overlay groups
  # Cessões 
  addPolygons(group = "Cessões") %>%  
  addMarkers(lng = c(-48.562,  -48.63385, -48.67696, -48.53463, -48.66203), 
             lat = c(-26.1666, -26.2267, -26.27073, -26.17286, -26.26179), 
             label = ~nup,
             labelOptions = c(permanent = TRUE, textOnly = TRUE, direction = "left"),                              
             group = "Cessões") %>%
  # Certidões de Disponibilidade
  addPolygons(data = certdisp, color = "red", fillColor = "red", 
              group = "Certidões de Disponibilidade") %>%
  addMarkers(lng = -48.5915, lat = -26.17901, label = certdisp$nup,
             labelOptions = c(permanent = TRUE, textOnly = TRUE, 
                              direction = "left"), 
           group = "Certidões de Disponibilidade") %>%
  # Controles (Barra de Escala, Quadriculado e Minimapa)
  addScaleBar(position = 'bottomleft', options = list(imperial = FALSE)) %>%
  addGraticule(interval = .025, style = list(weight = .2)) %>%
  addMiniMap(position = "topleft", zoomLevelOffset = -3) %>%
  # Layers control
  addLayersControl(
    baseGroups = c("OSM (default)", "Esri", "Toner Lite"),
    overlayGroups = c("Cessões", "Certidões de Disponibilidade"),
    options = layersControlOptions(collapsed = FALSE)
  )
```

---
class: center, middle