SQL-R-howto/SQL-Server-connector_dummy-code.R at main · mtorrey-NYCDOHMH/SQL-R-howto · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
## SQL-Server-connector-_dummy-code.R
## Last modified: 2023-04-21 17:20


########################################
## First step: connect to the SQL server and database

library(DBI) ## package with commands for connecting to the database

## con will be the database connection.
con <- dbConnect(odbc::odbc(),
	Driver="ODBC Driver 18 for SQL Server",
	Server="YOURSERVERNAME",
	Database="YOURDATABASENAME",
	TrustServerCertificate="YES", ## Need this because SQL server name doesn't match it's certificate
	Trusted_Connection="YES", ## This tells dbConnect to use Kerberos authentication
	timeout = 10)

## With Kerberos, it should automatically authenticate with the SQL server, so there's no reason to include UID or password information in the database connection.

## Disconnect with dbDisconnect(con)


########################################
## Second step: select catalog and tables you want to access

library(odbc) ## package with commands for probing database

## List top-level objects (catalogs) in db:
odbcListObjects(con)

## This gives you a list of available schemas for your chosed catalog. You need to know the schema to access the tables you want.
odbcListObjects(con, catalog="YOURCATALOGNAME")
## In our case, the schema we want is YOURSCHEMANAME

## List tables in catalog for YOURSCHEMANAME schema -- this is the command you will use any time you want to see what tables are available on the SQL server.
available_tables <- odbcListObjects(con, catalog="YOURCATALOGNAME", schema="YOURSCHEMANAME")

## After you know what table you want, fill it in for this command to get a data frame with the column names and types:
col_names_types <- odbcListColumns(con, catalog="YOURCATALOGNAME", schema="YOURSCHEMANAME", table="YOURTABLENAME")
print(col_names_types)

## Pull out the column names as a list so you can paste them into your SQL query when you pull your sf object from the database:
col_names <- col_names_types[, "name"]
print(col_names)


########################################
## Third step: pull obect with geometry from SQL with st_read()

library(sf)

## Fill in the table you want to pull from in the SQL query.
## Fill in the column names you want to pull from the table in the SQL query (refer to col_names object)
## Check that the geometry column is named "Shape" and if not adjust the Shape.STAsBinary() call in the SQL query
SHAPE_FROM_SQL <- st_read(con, geometry_column="Shape", query="SELECT YOURCOL1, YOURCOL2, YOURCOL3, YOURCOL4, Shape.STAsBinary() AS Shape FROM YOURSCHEMANAME.YOURTABLENAME")
## st_read is supposed to automatically figure out which column has geometry, so you shouldn't need the geometry_column unless there's two columns with geometry info.


## Plot test, fill in column name:
plot(SHAPE_FROM_SQL["YOURCOLNAME"], col = sf.colors(5, categorical = TRUE), border = NA)