SPUGeo

class: center, middle, inverse, title-slide

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

---

background-image: url(https://upload.wikimedia.org/wikipedia/commons/b/be/Sharingan_triple.svg)

---
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         ref           integer         YES                       NA
## 5   concedida           boolean         YES                       NA
## 6        area           numeric         YES                       NA
## 7        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)
```

```
## 5 out of 5 columns of the data frame match database table columns and will be formatted.
```

```
## Using writeWKT from rgeos package...
```

```
## Data inserted into table "espacoaquatico"."certdisp"
```

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

---
# 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

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

```
## Linking to GEOS 3.5.1, GDAL 2.1.3, PROJ 4.9.2
```

```r
cessao <- st_read(conn, 
                  query = "select * from espacoaquatico.cessao where municipio = 8319;")
```

```
## Warning: RS-DBI driver warning: (unrecognized PostgreSQL field type money
## (id:790) in column 12)
```

```
## Warning: RS-DBI driver warning: (unrecognized PostgreSQL field type
## geometry (id:16392) in column 15)
```

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

```r
library(knitr)
library(kableExtra)
kable(cessao, format = 'html') %>%
  scroll_box(width = "100%", height = "200px")
```

<div style="border: 1px solid #ddd; padding: 0px; overflow-y: scroll; height:200px; overflow-x: scroll; width:100%; "><table>
 <thead>
  <tr>
   <th style="text-align:right;position: sticky; top:0; background-color: #FFFFFF;"> id </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> interessado </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> cnpj </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> cpf </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> protocolo </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> nup </th>
   <th style="text-align:right;position: sticky; top:0; background-color: #FFFFFF;"> ref </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> onerosa </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> concedida </th>
   <th style="text-align:right;position: sticky; top:0; background-color: #FFFFFF;"> area </th>
   <th style="text-align:right;position: sticky; top:0; background-color: #FFFFFF;"> municipio </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> logradouro </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> aval </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> dataaval </th>
   <th style="text-align:right;position: sticky; top:0; background-color: #FFFFFF;"> refaval </th>
   <th style="text-align:left;position: sticky; top:0; background-color: #FFFFFF;"> geom </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:left;"> Golar Power Latam Participações e Comércio Ltda </td>
   <td style="text-align:left;"> 24.360.766/0001-45 </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:left;"> SC04142/2018 </td>
   <td style="text-align:left;"> 04972.010861/2018-05 </td>
   <td style="text-align:right;"> 7881639 </td>
   <td style="text-align:left;"> TRUE </td>
   <td style="text-align:left;"> FALSE </td>
   <td style="text-align:right;"> 807112.69 </td>
   <td style="text-align:right;"> 8319 </td>
   <td style="text-align:left;"> 08-00 </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:right;"> NA </td>
   <td style="text-align:left;"> list(c(742739.4319, 743689.4419, 744102.8256, 743152.8156, 742739.4319, 7102378.0901, 7103579.0401, 7103252.0335, 7102051.0835, 7102378.0901)) </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> TGSC - Terminal de Granéis de Santa Catarina </td>
   <td style="text-align:left;"> 08.504.106/0001-34 </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:left;"> 1025412 </td>
   <td style="text-align:left;"> 04972.206372/2015-04 </td>
   <td style="text-align:right;"> 3817298 </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:left;"> TRUE </td>
   <td style="text-align:right;"> 67865.12 </td>
   <td style="text-align:right;"> 8319 </td>
   <td style="text-align:left;"> 07-00 </td>
   <td style="text-align:left;"> $268,343.96 </td>
   <td style="text-align:left;"> 17182 </td>
   <td style="text-align:right;"> 3088819 </td>
   <td style="text-align:left;"> list(c(736383.53255482, 736430.57934544, 736365.75481727, 736427.47260475, 736498.52792884, 736467.62732194, 736410.48457999, 736120.21139993, 736079.64552348, 736383.53255482, 7097051.88192019, 7097001.80399831, 7096940.90311031, 7096874.53448153, 7096603.91112134, 7096595.46902995, 7096803.66997702, 7096676.28255105, 7096768.3896767, 7097051.88192019)) </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:left;"> TGB - Terminal Graneleiro da Babitonga S.A. </td>
   <td style="text-align:left;"> 16.920.552/0001-58 </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:left;"> 1852685 </td>
   <td style="text-align:left;"> 04972.003703/2016-29 </td>
   <td style="text-align:right;"> 5789942 </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:left;"> TRUE </td>
   <td style="text-align:right;"> 48038.66 </td>
   <td style="text-align:right;"> 8319 </td>
   <td style="text-align:left;"> 07-00 </td>
   <td style="text-align:left;"> $57,358.28 </td>
   <td style="text-align:left;"> 17591 </td>
   <td style="text-align:right;"> 5622667 </td>
   <td style="text-align:left;"> list(list(c(732467.398, 732437.718, 731988.236, 732017.916, 732467.398, 7092712.069, 7092739.441, 7092252.066, 7092224.694, 7092712.069)), list(c(732480.63, 732467.398, 732017.916, 732031.148, 732480.63, 7092699.866, 7092712.069, 7092224.694, 7092212.49, 7092699.866)), list(c(732654.079, 732644.727, 732635.023, 732249.952, 732257.975, 732654.079, 7091706.016, 7091713.56, 7091718.274, 7092449.74, 7092458.439, 7091706.016))) </td>
  </tr>
</tbody>
</table></div>

---
# Confecção de mapas

```r
library(leaflet)
cessao %>%
  st_transform(crs = 4326) %>%
  leaflet(options = leafletOptions(zoomControl = FALSE)) %>%
  addTiles() %>%
  addPolygons() %>%  
  addMarkers(lng = c(-48.63385, -48.562, -48.67696), 
             lat = c(-26.2267, -26.1666, -26.27073), 
             label = ~NUP,
             labelOptions = c(permanent = TRUE, textOnly = TRUE, 
                              direction = "left")                              
             ) %>%
  addScaleBar(position = 'bottomleft', 
              options = list(imperial = FALSE)) %>%
  addGraticule(interval = .025, style = list(weight = .2)) %>%
  addMiniMap(position = "topleft", zoomLevelOffset = -3) 
```

---
class: center, middle
<div id="htmlwidget-1cc63dd713a5b7c65095" style="width:504px;height:504px;" class="leaflet html-widget"></div>
<script type="application/json" data-for="htmlwidget-1cc63dd713a5b7c65095">{"x":{"options":{"crs":{"crsClass":"L.CRS.EPSG3857","code":null,"proj4def":null,"projectedBounds":null,"options":{}},"zoomControl":false},"calls":[{"method":"addTiles","args":["//{s}.tile.openstreetmap.org/{z}/{x}/{y}.png",null,null,{"minZoom":0,"maxZoom":18,"tileSize":256,"subdomains":"abc","errorTileUrl":"","tms":false,"noWrap":false,"zoomOffset":0,"zoomReverse":false,"opacity":1,"zIndex":1,"detectRetina":false,"attribution":"© <a href=\"http://openstreetmap.org\">OpenStreetMap<\/a> contributors, <a href=\"http://creativecommons.org/licenses/by-sa/2.0/\">CC-BY-SA<\/a>"}]},{"method":"addPolygons","args":[[[[{"lng":[-48.571272858469,-48.5619998997718,-48.5578056971205,-48.5670784432262,-48.571272858469],"lat":[-26.1775928758648,-26.1665979017134,-26.1694778833502,-26.1804731124137,-26.1775928758648]}]],[[{"lng":[-48.6338511937926,-48.6333714375054,-48.6340087532974,-48.6333792614805,-48.6326190250435,-48.6329265900678,-48.6335362473294,-48.6364166505928,-48.6368392476156,-48.6338511937926],"lat":[-26.2267041845187,-26.2271482280837,-26.2277083468416,-26.2282969452799,-26.2307267533505,-26.2308080102589,-26.2289390728291,-26.2301361460267,-26.2293118444272,-26.2267041845187]}]],[[{"lng":[-48.6722443705953,-48.6725462944929,-48.6769565384442,-48.6766546106041,-48.6722443705953],"lat":[-26.2664981171073,-26.2662559824598,-26.2707259855517,-26.2709681291855,-26.2664981171073]}],[{"lng":[-48.6721097658554,-48.6722443705953,-48.6766546106041,-48.6765200039268,-48.6721097658554],"lat":[-26.2666060654664,-26.2664981171073,-26.2709681291855,-26.271076090573,-26.2666060654664]}],[{"lng":[-48.670195030601,-48.670289979556,-48.6703879409638,-48.674373095716,-48.6742943753605,-48.670195030601],"lat":[-26.275544442125,-26.2754778996363,-26.2754369465884,-26.2689001392475,-26.2688203553183,-26.275544442125]}]]],null,null,{"interactive":true,"className":"","stroke":true,"color":"#03F","weight":5,"opacity":0.5,"fill":true,"fillColor":"#03F","fillOpacity":0.2,"smoothFactor":1,"noClip":false},null,null,null,{"interactive":false,"permanent":false,"direction":"auto","opacity":1,"offset":[0,0],"textsize":"10px","textOnly":false,"className":"","sticky":true},null]},{"method":"addMarkers","args":[[-26.2267,-26.1666,-26.27073],[-48.63385,-48.562,-48.67696],null,null,null,{"interactive":true,"draggable":false,"keyboard":true,"title":"","alt":"","zIndexOffset":0,"opacity":1,"riseOnHover":false,"riseOffset":250},null,null,null,null,["04972.010861/2018-05","04972.206372/2015-04","04972.003703/2016-29"],{"permanent":"TRUE","textOnly":"TRUE","direction":"left"},null]},{"method":"addScaleBar","args":[{"imperial":false,"position":"bottomleft"}]},{"method":"addGraticule","args":[0.025,false,{"weight":0.2},null,null,{"interactive":false,"pointerEvents":"none","className":""}]},{"method":"addMiniMap","args":[null,null,"topleft",150,150,19,19,-3,false,false,false,false,false,false,{"color":"#ff7800","weight":1,"clickable":false},{"color":"#000000","weight":1,"clickable":false,"opacity":0,"fillOpacity":0},{"hideText":"Hide MiniMap","showText":"Show MiniMap"},[]]}],"limits":{"lat":[-26.275544442125,-26.1665979017134],"lng":[-48.67696,-48.5578056971205]}},"evals":[],"jsHooks":[]}</script>