# Appendix: Writing SQL: Some tips and tricks {#tips}

```{r message=FALSE, warning=FALSE}
require(RPostgreSQL)
require(rpostgis)
require(sf)
require(tmap)
require(mapview)
conn <- dbConnect("PostgreSQL", host = "postgis",
  dbname = "gis_course" ,user = "gis_course", password = "gis_course123")
```
## General advice

The gis_course user connection is limited to 30 seconds. If a query is written that over runs this limit it will time out with no results. This can occur when you are new to postgis.

1. Find out what you are looking for. You can't write a correct query if you don't know table and field names. See \@ref(findtable), \@ref(findfields),  \@ref(findvals).
2. Know the size of the table you are working with. Postgis is designed for big data. However very big data might not all load into memory or may not display as a map. \@ref(countrecs) helps. 
3. Test queries carefully setting limits at first if the table is large \@ref(safelimits)
4. Use the right sort of call to the data base from R \@ref(dbqueries)
5. Learn to use sprintf in R to help build complex queries from sub queries \@ref(sprintf)
6. Apparently slow queries may actually be slow because the results take time to render. Learn to use st_simplify with complex geometries \@ref(stsimplify). 



## Find table names in R {#findtable}

It is a good idea to pipe this into a searchable table when writing in markdown. Write a few letters in the search tab to find your table.

```{r}
dbListTables(conn) %>% data.frame() %>% DT::datatable()
```

## Find field names in R {#findfields}

```{r}
dbListFields(conn,"sssi") %>% data.frame() %>% DT::datatable()
```

## Count the number of records in a table {#countrecs}

You can choose any named field to get a count. 

```{r}
query<-"select count(gid) from sssi"
dbGetQuery(conn,query)
```


## Find all possible values of a categorical field {#findvals}

```{r}
query<- "select distinct sssi_name from sssi"
dbGetQuery(conn,query)  %>% as.data.frame() %>% DT::datatable()
```


## Set limits to queries when testing them {#safelimits}

The safe way to design queries is to set a limit when writing them. This applies to R or QGIS db manager. A useful trick is to include order by random if you don't want to get just the first 10 values.

```{r}
query<- "select * from sssi order by random() limit 10"
sssi<-st_read(conn,query=query)
```

This can be turned into a little function for safe testing a query.

```{r}
safe_test<-function(sql=sql,n=10){
  query<-sprintf("%s order by random() limit %s",sql,n)
  st_read(conn,query=query) %>% st_set_geometry(NULL) %>% DT::datatable()
}
query<- "select * from sssi"
safe_test(query)
```


## Add a unique identifier {#uniqueids}

Tables in postgis might not have a unique ID column for many reasons. You don't need one in R. However you do in QGIS in order to load the table.

Just add ROW_NUMBER () OVER () id to the query. ** Note this assumes that id does not exist as a name. If it does, change the alias**

Demo in R, but for use in QGIS db manager.

```{r}
query<-"select ROW_NUMBER () OVER () id , sssi_name,geom from sssi"
safe_test(query)
```

```{r}
safe_test("select * from nat_earth_uk",100)
```


## DbSendQuery, DbGetQuery and st_read {#dbqueries}

There are three functions for working with queries in R. 

### DbSendQuery {#dbsendquery}

DBSendQuery does not return data, but sets things up in the data base. For example this query sets up a table for Dorset, Poole and Bournemouth that may be useful when subsetting.

```{r}
dbSendQuery(conn, "drop table if exists dorset;")
dbSendQuery(conn, "create table dorset as
           select ROW_NUMBER () OVER () id,
            ctyua16nm nm, geom from counties 
            where ctyua16nm in ('Dorset','Poole','Bournemouth')")
```

### st_read {#stread}

To get the results with the geometries for mapping use st_read. By default this reads a whole table. If you want a query you must state query= query. 

```{r}
st_read(conn,"dorset") %>% qtm()
## Same with a query
#st_read(conn,query= "select * from dorset") %>% qtm() 
```


## dbGetQuery {#dbgetquery}

The st_read function is designed for spatial data. It won't work correctly if there is no geometry column. Use DbGetQuery in this case. For example to find the 10 largest SSSIs without returning their geometry.

```{r}
dbGetQuery(conn,"select sssi_name, 
round(st_area(geom)/10000) areaha from 
sssi order by areaha desc limit 10")

```

## Checking coordinate reference system 

Piping a single geometry to st_crs is one quick way of finding out the crs used by postgis when working in R.

```{r}
st_read(conn, query ="select geometry from gbif limit 1") %>% st_crs() 
st_read(conn, query ="select geom from counties limit 1") %>% st_crs() 
st_read(conn, query ="select geometry from waterways limit 1") %>% st_crs()  
```


## Using sprintf {#sprintf}

The sprintf function in R can help when building large queries. This function acts like paste, but is easier to use in many respects. The logic is that the **%s** place markers in a sprintf query gets filled in with the values of the variables placed at the end.

### Saving typing field names {#quickfields}

Here's one useful trick with sprintf. The * operator in sql selects all the fields. However it is not good practice to pull in lots of information we don't want with a query. Annoyingly in SQL there is no way of dropping just the geometry field if we don't want it. 
We can get all the fields on a table with dbListFields.

```{r}
fields<-unlist(dbListFields(conn,"nat_earth_uk"))
fields

```

Now we can use sprintf to choose which of the fields from 1 to 84 we want to include without typing them out.

```{r}
query<-sprintf("select %s from nat_earth_uk",paste(fields[c(3,9,84)],collapse=","))

```

The query now reads

**`r query `** 


```{r}
st_read(conn,query=query) %>% mapview()
```

### Using sprintf to join sub queries {#subqueries}

This is a very handy trick which avoids typing complex queries in one go. It is especially useful for spatial intersects and geoprocessing involving two or more tables.


```{r}
focal_area<-"select geom from counties 
            where ctyua16nm in 
('Hampshire','Wiltshire')"

st_read(conn,query=focal_area) %>% qtm()
```


```{r}
query<-sprintf("select st_simplify(s.geom, 100) 
  from sssi s, (%s) l where 
  st_intersects(s.geom, l.geom) ",focal_area)


```

The query now reads 

**`r query`**

```{r}
st_read(conn,query=query) %>% qtm()
```

## Using st_simplify {#stsimplify}

Complex geoemtries are slow to render and can freeze up the system even though the query does not take long to run. Geometries should be simplified to the appropriate scale for map making. For example if just want to draw the county boundaries of England and Wales we can simplify radically. 

```{r}
st_read(conn, query="select ctyua16nm,st_simplify(geom,1000) from counties") %>% qtm()
```

Notice that the process can be taken too far.


```{r}
st_read(conn, query="select st_simplify(geom,10000) from counties") %>% qtm()
```



```{r,message=FALSE,results='hide',warning=FALSE,echo=FALSE}
if(exists("conn"))dbDisconnect(conn)
rm(list=ls())
lapply(paste('package:',names(sessionInfo()$otherPkgs),sep=""),detach,character.only=TRUE,unload=TRUE)
```


