<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta charset="utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="generator" content="pandoc" />
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />

<meta name="viewport" content="width=device-width, initial-scale=1">

<meta name="author" content="RS-eco" />

<meta name="date" content="2019-09-09" />

<title>Create GBIF Database</title>



<style type="text/css">code{white-space: pre;}</style>
<style type="text/css" data-origin="pandoc">
a.sourceLine { display: inline-block; line-height: 1.25; }
a.sourceLine { pointer-events: none; color: inherit; text-decoration: inherit; }
a.sourceLine:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode { white-space: pre; position: relative; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
code.sourceCode { white-space: pre-wrap; }
a.sourceLine { text-indent: -1em; padding-left: 1em; }
}
pre.numberSource a.sourceLine
  { position: relative; left: -4em; }
pre.numberSource a.sourceLine::before
  { content: attr(data-line-number);
    position: relative; left: -1em; text-align: right; vertical-align: baseline;
    border: none; pointer-events: all; display: inline-block;
    -webkit-touch-callout: none; -webkit-user-select: none;
    -khtml-user-select: none; -moz-user-select: none;
    -ms-user-select: none; user-select: none;
    padding: 0 4px; width: 4em;
    color: #aaaaaa;
  }
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa;  padding-left: 4px; }
div.sourceCode
  {  }
@media screen {
a.sourceLine::before { text-decoration: underline; }
}
code span.al { color: #ff0000; font-weight: bold; } /* Alert */
code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } /* Annotation */
code span.at { color: #7d9029; } /* Attribute */
code span.bn { color: #40a070; } /* BaseN */
code span.bu { } /* BuiltIn */
code span.cf { color: #007020; font-weight: bold; } /* ControlFlow */
code span.ch { color: #4070a0; } /* Char */
code span.cn { color: #880000; } /* Constant */
code span.co { color: #60a0b0; font-style: italic; } /* Comment */
code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } /* CommentVar */
code span.do { color: #ba2121; font-style: italic; } /* Documentation */
code span.dt { color: #902000; } /* DataType */
code span.dv { color: #40a070; } /* DecVal */
code span.er { color: #ff0000; font-weight: bold; } /* Error */
code span.ex { } /* Extension */
code span.fl { color: #40a070; } /* Float */
code span.fu { color: #06287e; } /* Function */
code span.im { } /* Import */
code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } /* Information */
code span.kw { color: #007020; font-weight: bold; } /* Keyword */
code span.op { color: #666666; } /* Operator */
code span.ot { color: #007020; } /* Other */
code span.pp { color: #bc7a00; } /* Preprocessor */
code span.sc { color: #4070a0; } /* SpecialChar */
code span.ss { color: #bb6688; } /* SpecialString */
code span.st { color: #4070a0; } /* String */
code span.va { color: #19177c; } /* Variable */
code span.vs { color: #4070a0; } /* VerbatimString */
code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } /* Warning */

</style>
<script>
// apply pandoc div.sourceCode style to pre.sourceCode instead
(function() {
  var sheets = document.styleSheets;
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].ownerNode.dataset["origin"] !== "pandoc") continue;
    try { var rules = sheets[i].cssRules; } catch (e) { continue; }
    for (var j = 0; j < rules.length; j++) {
      var rule = rules[j];
      // check if there is a div.sourceCode rule
      if (rule.type !== rule.STYLE_RULE || rule.selectorText !== "div.sourceCode") continue;
      var style = rule.style.cssText;
      // check if color or background-color is set
      if (rule.style.color === '' && rule.style.backgroundColor === '') continue;
      // replace div.sourceCode by a pre.sourceCode rule
      sheets[i].deleteRule(j);
      sheets[i].insertRule('pre.sourceCode{' + style + '}', j);
    }
  }
})();
</script>



<style type="text/css">body {
background-color: #fff;
margin: 1em auto;
max-width: 700px;
overflow: visible;
padding-left: 2em;
padding-right: 2em;
font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
font-size: 14px;
line-height: 1.35;
}
#header {
text-align: center;
}
#TOC {
clear: both;
margin: 0 0 10px 10px;
padding: 4px;
width: 400px;
border: 1px solid #CCCCCC;
border-radius: 5px;
background-color: #f6f6f6;
font-size: 13px;
line-height: 1.3;
}
#TOC .toctitle {
font-weight: bold;
font-size: 15px;
margin-left: 5px;
}
#TOC ul {
padding-left: 40px;
margin-left: -1.5em;
margin-top: 5px;
margin-bottom: 5px;
}
#TOC ul ul {
margin-left: -2em;
}
#TOC li {
line-height: 16px;
}
table {
margin: 1em auto;
border-width: 1px;
border-color: #DDDDDD;
border-style: outset;
border-collapse: collapse;
}
table th {
border-width: 2px;
padding: 5px;
border-style: inset;
}
table td {
border-width: 1px;
border-style: inset;
line-height: 18px;
padding: 5px 5px;
}
table, table th, table td {
border-left-style: none;
border-right-style: none;
}
table thead, table tr.even {
background-color: #f7f7f7;
}
p {
margin: 0.5em 0;
}
blockquote {
background-color: #f6f6f6;
padding: 0.25em 0.75em;
}
hr {
border-style: solid;
border: none;
border-top: 1px solid #777;
margin: 28px 0;
}
dl {
margin-left: 0;
}
dl dd {
margin-bottom: 13px;
margin-left: 13px;
}
dl dt {
font-weight: bold;
}
ul {
margin-top: 0;
}
ul li {
list-style: circle outside;
}
ul ul {
margin-bottom: 0;
}
pre, code {
background-color: #f7f7f7;
border-radius: 3px;
color: #333;
white-space: pre-wrap; 
}
pre {
border-radius: 3px;
margin: 5px 0px 10px 0px;
padding: 10px;
}
pre:not([class]) {
background-color: #f7f7f7;
}
code {
font-family: Consolas, Monaco, 'Courier New', monospace;
font-size: 85%;
}
p > code, li > code {
padding: 2px 0px;
}
div.figure {
text-align: center;
}
img {
background-color: #FFFFFF;
padding: 2px;
border: 1px solid #DDDDDD;
border-radius: 3px;
border: 1px solid #CCCCCC;
margin: 0 5px;
}
h1 {
margin-top: 0;
font-size: 35px;
line-height: 40px;
}
h2 {
border-bottom: 4px solid #f7f7f7;
padding-top: 10px;
padding-bottom: 2px;
font-size: 145%;
}
h3 {
border-bottom: 2px solid #f7f7f7;
padding-top: 10px;
font-size: 120%;
}
h4 {
border-bottom: 1px solid #f7f7f7;
margin-left: 8px;
font-size: 105%;
}
h5, h6 {
border-bottom: 1px solid #ccc;
font-size: 105%;
}
a {
color: #0033dd;
text-decoration: none;
}
a:hover {
color: #6666ff; }
a:visited {
color: #800080; }
a:visited:hover {
color: #BB00BB; }
a[href^="http:"] {
text-decoration: underline; }
a[href^="https:"] {
text-decoration: underline; }

code > span.kw { color: #555; font-weight: bold; } 
code > span.dt { color: #902000; } 
code > span.dv { color: #40a070; } 
code > span.bn { color: #d14; } 
code > span.fl { color: #d14; } 
code > span.ch { color: #d14; } 
code > span.st { color: #d14; } 
code > span.co { color: #888888; font-style: italic; } 
code > span.ot { color: #007020; } 
code > span.al { color: #ff0000; font-weight: bold; } 
code > span.fu { color: #900; font-weight: bold; }  code > span.er { color: #a61717; background-color: #e3d2d2; } 
</style>




</head>

<body>




<h1 class="title toc-ignore">Create GBIF Database</h1>
<h4 class="author">RS-eco</h4>
<h4 class="date">2019-09-09</h4>



<p>Download GBIF data from gbif.org</p>
<div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><a class="sourceLine" id="cb1-1" data-line-number="1"><span class="co">#download.file(url=&quot;http://api.gbif.org/v1/occurrence/download/request/0000275-171219132708484.zip&quot;,</span></a>
<a class="sourceLine" id="cb1-2" data-line-number="2"><span class="co">#              destfile=&quot;/home/mbiber/data/0000275-171219132708484.zip&quot;)</span></a>
<a class="sourceLine" id="cb1-3" data-line-number="3"><span class="co">#unzip()</span></a></code></pre></div>
<p>Create SQLite database</p>
<div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><a class="sourceLine" id="cb2-1" data-line-number="1"><span class="co">#' Load data.table library</span></a>
<a class="sourceLine" id="cb2-2" data-line-number="2"><span class="kw">library</span>(data.table)</a>
<a class="sourceLine" id="cb2-3" data-line-number="3"></a>
<a class="sourceLine" id="cb2-4" data-line-number="4"><span class="co">#' Set file directory</span></a>
<a class="sourceLine" id="cb2-5" data-line-number="5"><span class="co">#filedir &lt;- &quot;E:/Data&quot;</span></a>
<a class="sourceLine" id="cb2-6" data-line-number="6">filedir &lt;-<span class="st"> &quot;/home/mbiber/data&quot;</span></a>
<a class="sourceLine" id="cb2-7" data-line-number="7"></a>
<a class="sourceLine" id="cb2-8" data-line-number="8"><span class="co"># Specify file path</span></a>
<a class="sourceLine" id="cb2-9" data-line-number="9"><span class="co">#gbif_file &lt;- paste0(filedir, &quot;/0002839-170627171947987.csv&quot;) #Old version</span></a>
<a class="sourceLine" id="cb2-10" data-line-number="10">gbif_file &lt;-<span class="st"> </span><span class="kw">paste0</span>(filedir, <span class="st">&quot;/0000275-171219132708484.csv&quot;</span>)</a>
<a class="sourceLine" id="cb2-11" data-line-number="11"></a>
<a class="sourceLine" id="cb2-12" data-line-number="12"><span class="co">#' Read GBIF Data into R, has 437 GB of size, so only read specify subset of rows</span></a>
<a class="sourceLine" id="cb2-13" data-line-number="13">gbif_data &lt;-<span class="st"> </span>data.table<span class="op">::</span><span class="kw">fread</span>(gbif_file, <span class="dt">nrow=</span><span class="dv">10</span>)</a>
<a class="sourceLine" id="cb2-14" data-line-number="14"></a>
<a class="sourceLine" id="cb2-15" data-line-number="15"><span class="co">#' See first 5 data entries</span></a>
<a class="sourceLine" id="cb2-16" data-line-number="16"><span class="kw">head</span>(gbif_data)</a>
<a class="sourceLine" id="cb2-17" data-line-number="17"><span class="co">#' See column names</span></a>
<a class="sourceLine" id="cb2-18" data-line-number="18"><span class="kw">colnames</span>(gbif_data)</a>
<a class="sourceLine" id="cb2-19" data-line-number="19"></a>
<a class="sourceLine" id="cb2-20" data-line-number="20"><span class="co">#' Get colClasses</span></a>
<a class="sourceLine" id="cb2-21" data-line-number="21">(colClass_gbif &lt;-<span class="st"> </span><span class="kw">as.character</span>(rmngb<span class="op">::</span><span class="kw">colClasses</span>(gbif_data)))</a>
<a class="sourceLine" id="cb2-22" data-line-number="22"><span class="co">#' Define eventdate as date class</span></a>
<a class="sourceLine" id="cb2-23" data-line-number="23">colClass_gbif[<span class="dv">25</span>] &lt;-<span class="st"> &quot;date&quot;</span></a>
<a class="sourceLine" id="cb2-24" data-line-number="24"></a>
<a class="sourceLine" id="cb2-25" data-line-number="25"><span class="co">#' Only select certain columns</span></a>
<a class="sourceLine" id="cb2-26" data-line-number="26">gbif_data &lt;-<span class="st"> </span>data.table<span class="op">::</span><span class="kw">fread</span>(gbif_file,</a>
<a class="sourceLine" id="cb2-27" data-line-number="27">                    <span class="dt">select=</span><span class="kw">c</span>(<span class="dv">1</span><span class="op">:</span><span class="dv">10</span>,<span class="dv">14</span>,<span class="dv">15</span>,<span class="dv">17</span><span class="op">:</span><span class="dv">31</span>,<span class="dv">40</span>,<span class="dv">44</span>), <span class="dt">nrow=</span><span class="dv">10</span>)</a>
<a class="sourceLine" id="cb2-28" data-line-number="28"></a>
<a class="sourceLine" id="cb2-29" data-line-number="29"><span class="co">#' Get column names</span></a>
<a class="sourceLine" id="cb2-30" data-line-number="30">colnames_gbif &lt;-<span class="st"> </span><span class="kw">colnames</span>(gbif_data)</a>
<a class="sourceLine" id="cb2-31" data-line-number="31"></a>
<a class="sourceLine" id="cb2-32" data-line-number="32"><span class="co">#' Read only 1000 entries at a time and only required columns</span></a>
<a class="sourceLine" id="cb2-33" data-line-number="33">gbif_data &lt;-<span class="st"> </span>data.table<span class="op">::</span><span class="kw">fread</span>(gbif_file,</a>
<a class="sourceLine" id="cb2-34" data-line-number="34">                    <span class="dt">select=</span><span class="kw">c</span>(<span class="dv">1</span><span class="op">:</span><span class="dv">10</span>,<span class="dv">14</span>,<span class="dv">15</span>,<span class="dv">17</span><span class="op">:</span><span class="dv">31</span>,<span class="dv">40</span>,<span class="dv">44</span>), </a>
<a class="sourceLine" id="cb2-35" data-line-number="35">                    <span class="dt">col.names=</span>colnames_gbif, </a>
<a class="sourceLine" id="cb2-36" data-line-number="36">                    <span class="dt">colClasses=</span>colClass_gbif,</a>
<a class="sourceLine" id="cb2-37" data-line-number="37">                    <span class="dt">skip=</span><span class="dv">0</span>, <span class="dt">nrow=</span><span class="dv">1000</span>, <span class="dt">data.table=</span><span class="ot">FALSE</span>)</a>
<a class="sourceLine" id="cb2-38" data-line-number="38"></a>
<a class="sourceLine" id="cb2-39" data-line-number="39"><span class="co">#' Turn Date column into date format</span></a>
<a class="sourceLine" id="cb2-40" data-line-number="40">gbif_data<span class="op">$</span>eventdate &lt;-<span class="st"> </span><span class="kw">as.Date</span>(gbif_data<span class="op">$</span>eventdate)</a>
<a class="sourceLine" id="cb2-41" data-line-number="41"></a>
<a class="sourceLine" id="cb2-42" data-line-number="42"><span class="co">#' Specify date and datetime columns for Database</span></a>
<a class="sourceLine" id="cb2-43" data-line-number="43"><span class="kw">library</span>(dplyr, <span class="dt">quiet=</span><span class="ot">TRUE</span>)</a>
<a class="sourceLine" id="cb2-44" data-line-number="44">date_cols &lt;-<span class="st"> </span>gbif_data <span class="op">%&gt;%</span><span class="st"> </span></a>
<a class="sourceLine" id="cb2-45" data-line-number="45"><span class="st">  </span><span class="kw">select_if</span>(lubridate<span class="op">::</span>is.Date) <span class="op">%&gt;%</span><span class="st"> </span></a>
<a class="sourceLine" id="cb2-46" data-line-number="46"><span class="st">  </span><span class="kw">colnames</span>()</a>
<a class="sourceLine" id="cb2-47" data-line-number="47">gbif_data[ , date_cols] &lt;-<span class="st"> </span><span class="kw">as.character.Date</span>(gbif_data[ , date_cols])</a>
<a class="sourceLine" id="cb2-48" data-line-number="48"></a>
<a class="sourceLine" id="cb2-49" data-line-number="49"><span class="co">#' Create gbif database (chunk by chunk)</span></a>
<a class="sourceLine" id="cb2-50" data-line-number="50">con &lt;-<span class="st"> </span><span class="kw">src_sqlite</span>(<span class="kw">paste0</span>(filedir, <span class="st">&quot;/gbif_database.sqlite&quot;</span>), <span class="dt">create=</span><span class="ot">TRUE</span>)</a></code></pre></div>
<p>Add data chunks to database</p>
<div class="sourceCode" id="cb3"><pre class="sourceCode r"><code class="sourceCode r"><a class="sourceLine" id="cb3-1" data-line-number="1"><span class="co">#' Connect to database</span></a>
<a class="sourceLine" id="cb3-2" data-line-number="2"><span class="kw">library</span>(DBI)</a>
<a class="sourceLine" id="cb3-3" data-line-number="3">con &lt;-<span class="st"> </span><span class="kw">dbConnect</span>(RSQLite<span class="op">::</span><span class="kw">SQLite</span>(), <span class="dt">dbname =</span> <span class="kw">paste0</span>(filedir, <span class="st">&quot;/gbif_database.sqlite&quot;</span>))</a>
<a class="sourceLine" id="cb3-4" data-line-number="4"></a>
<a class="sourceLine" id="cb3-5" data-line-number="5"><span class="co"># write this first batch of lines to SQLITE table, </span></a>
<a class="sourceLine" id="cb3-6" data-line-number="6"><span class="co"># converting dates to string representation</span></a>
<a class="sourceLine" id="cb3-7" data-line-number="7"><span class="co">#dbWriteTable(con, &quot;gbif&quot;, gbif_data, overwrite=TRUE)</span></a>
<a class="sourceLine" id="cb3-8" data-line-number="8"></a>
<a class="sourceLine" id="cb3-9" data-line-number="9"><span class="co"># Function that appends new sections to the table</span></a>
<a class="sourceLine" id="cb3-10" data-line-number="10">append_to_sqlite &lt;-<span class="st"> </span><span class="cf">function</span>(x, pos) {</a>
<a class="sourceLine" id="cb3-11" data-line-number="11">  x &lt;-<span class="st"> </span><span class="kw">as.data.frame</span>(x)</a>
<a class="sourceLine" id="cb3-12" data-line-number="12">  <span class="co">#x[ , date_cols] &lt;- as.character.Date(x[ , date_cols])</span></a>
<a class="sourceLine" id="cb3-13" data-line-number="13">  <span class="kw">dbWriteTable</span>(con, <span class="st">&quot;gbif&quot;</span>, x, <span class="dt">append =</span> <span class="ot">TRUE</span>)</a>
<a class="sourceLine" id="cb3-14" data-line-number="14">}</a>
<a class="sourceLine" id="cb3-15" data-line-number="15"></a>
<a class="sourceLine" id="cb3-16" data-line-number="16"><span class="co"># readr chunk functionality</span></a>
<a class="sourceLine" id="cb3-17" data-line-number="17"><span class="kw">library</span>(readr)</a>
<a class="sourceLine" id="cb3-18" data-line-number="18"><span class="kw">read_delim_chunked</span>(<span class="dt">file=</span>gbif_file, <span class="dt">skip=</span><span class="dv">377805260</span>, <span class="dt">callback=</span>append_to_sqlite, <span class="dt">delim=</span><span class="st">&quot;,&quot;</span>,</a>
<a class="sourceLine" id="cb3-19" data-line-number="19">                 <span class="dt">chunk_size =</span> <span class="dv">100000</span>, </a>
<a class="sourceLine" id="cb3-20" data-line-number="20">                 <span class="dt">col_names =</span> colnames_gbif, </a>
<a class="sourceLine" id="cb3-21" data-line-number="21">                   <span class="dt">col_types =</span> <span class="kw">cols</span>(</a>
<a class="sourceLine" id="cb3-22" data-line-number="22">                     <span class="dt">gbifid =</span> <span class="kw">col_integer</span>(),</a>
<a class="sourceLine" id="cb3-23" data-line-number="23">                     <span class="dt">datasetkey =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-24" data-line-number="24">                     <span class="dt">occurrenceid =</span> <span class="kw">col_logical</span>(),</a>
<a class="sourceLine" id="cb3-25" data-line-number="25">                     <span class="dt">kingdom =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-26" data-line-number="26">                     <span class="dt">phylum =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-27" data-line-number="27">                     <span class="dt">class =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-28" data-line-number="28">                     <span class="dt">order =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-29" data-line-number="29">                     <span class="dt">family =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-30" data-line-number="30">                     <span class="dt">genus =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-31" data-line-number="31">                     <span class="dt">species =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-32" data-line-number="32">                     <span class="dt">countrycode =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-33" data-line-number="33">                     <span class="dt">locality =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-34" data-line-number="34">                     <span class="dt">decimallatitude =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-35" data-line-number="35">                     <span class="dt">decimallongitude =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-36" data-line-number="36">                     <span class="dt">coordinateuncertaintyinmeters =</span> <span class="kw">col_logical</span>(),</a>
<a class="sourceLine" id="cb3-37" data-line-number="37">                     <span class="dt">coordinateprecision =</span> <span class="kw">col_logical</span>(),</a>
<a class="sourceLine" id="cb3-38" data-line-number="38">                     <span class="dt">elevation =</span> <span class="kw">col_logical</span>(),</a>
<a class="sourceLine" id="cb3-39" data-line-number="39">                     <span class="dt">elevationaccuracy =</span> <span class="kw">col_logical</span>(),</a>
<a class="sourceLine" id="cb3-40" data-line-number="40">                     <span class="dt">depth =</span> <span class="kw">col_logical</span>(),</a>
<a class="sourceLine" id="cb3-41" data-line-number="41">                     <span class="dt">depthaccuracy =</span> <span class="kw">col_logical</span>(),</a>
<a class="sourceLine" id="cb3-42" data-line-number="42">                     <span class="dt">eventdate =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-43" data-line-number="43">                     <span class="dt">day =</span> <span class="kw">col_integer</span>(),</a>
<a class="sourceLine" id="cb3-44" data-line-number="44">                     <span class="dt">month =</span> <span class="kw">col_integer</span>(),</a>
<a class="sourceLine" id="cb3-45" data-line-number="45">                     <span class="dt">year =</span> <span class="kw">col_integer</span>(),</a>
<a class="sourceLine" id="cb3-46" data-line-number="46">                     <span class="dt">taxonkey =</span> <span class="kw">col_integer</span>(),</a>
<a class="sourceLine" id="cb3-47" data-line-number="47">                     <span class="dt">specieskey =</span> <span class="kw">col_integer</span>(),</a>
<a class="sourceLine" id="cb3-48" data-line-number="48">                     <span class="dt">basisofrecord =</span> <span class="kw">col_character</span>(),</a>
<a class="sourceLine" id="cb3-49" data-line-number="49">                     <span class="dt">typestatus =</span> <span class="kw">col_logical</span>(),</a>
<a class="sourceLine" id="cb3-50" data-line-number="50">                     <span class="dt">issue =</span> <span class="kw">col_character</span>()</a>
<a class="sourceLine" id="cb3-51" data-line-number="51">                   ))</a>
<a class="sourceLine" id="cb3-52" data-line-number="52"></a>
<a class="sourceLine" id="cb3-53" data-line-number="53"><span class="co">#' Disconnect database</span></a>
<a class="sourceLine" id="cb3-54" data-line-number="54"><span class="kw">dbDisconnect</span>(con)</a></code></pre></div>
<p>Create indexes</p>
<div class="sourceCode" id="cb4"><pre class="sourceCode r"><code class="sourceCode r"><a class="sourceLine" id="cb4-1" data-line-number="1"><span class="co">#' Connect to database</span></a>
<a class="sourceLine" id="cb4-2" data-line-number="2">con &lt;-<span class="st"> </span><span class="kw">dbConnect</span>(RSQLite<span class="op">::</span><span class="kw">SQLite</span>(), <span class="dt">dbname =</span> <span class="kw">paste0</span>(filedir, <span class="st">&quot;/gbif_database.sqlite&quot;</span>))</a>
<a class="sourceLine" id="cb4-3" data-line-number="3"></a>
<a class="sourceLine" id="cb4-4" data-line-number="4"><span class="co">#' Create indexes</span></a>
<a class="sourceLine" id="cb4-5" data-line-number="5"><span class="kw">db_create_indexes</span>(<span class="dt">con=</span>con, <span class="dt">table=</span><span class="st">&quot;gbif&quot;</span>, <span class="dt">indexes =</span> <span class="kw">list</span>(<span class="st">&quot;gbifid&quot;</span>, <span class="st">&quot;species&quot;</span>, <span class="st">&quot;decimallatitude&quot;</span>, </a>
<a class="sourceLine" id="cb4-6" data-line-number="6">                                              <span class="st">&quot;decimallongitude&quot;</span>, <span class="st">&quot;eventdate&quot;</span>, </a>
<a class="sourceLine" id="cb4-7" data-line-number="7">                                              <span class="st">&quot;taxonkey&quot;</span>, <span class="st">&quot;specieskey&quot;</span>))</a>
<a class="sourceLine" id="cb4-8" data-line-number="8"></a>
<a class="sourceLine" id="cb4-9" data-line-number="9"><span class="co">#' See if table really exists</span></a>
<a class="sourceLine" id="cb4-10" data-line-number="10"><span class="kw">dbListTables</span>(con)</a>
<a class="sourceLine" id="cb4-11" data-line-number="11"></a>
<a class="sourceLine" id="cb4-12" data-line-number="12"><span class="co">#' Now that we have copied the data, we can use tbl() to take a reference to it:</span></a>
<a class="sourceLine" id="cb4-13" data-line-number="13">gbif_db &lt;-<span class="st"> </span>dplyr<span class="op">::</span><span class="kw">tbl</span>(con, <span class="st">&quot;gbif&quot;</span>)</a>
<a class="sourceLine" id="cb4-14" data-line-number="14"><span class="kw">head</span>(gbif_db)</a>
<a class="sourceLine" id="cb4-15" data-line-number="15"></a>
<a class="sourceLine" id="cb4-16" data-line-number="16"><span class="co">#' Disconnect from database</span></a>
<a class="sourceLine" id="cb4-17" data-line-number="17"><span class="kw">dbDisconnect</span>(con)</a></code></pre></div>



<!-- code folding -->


<!-- dynamically load mathjax for compatibility with self-contained -->
<script>
  (function () {
    var script = document.createElement("script");
    script.type = "text/javascript";
    script.src  = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
    document.getElementsByTagName("head")[0].appendChild(script);
  })();
</script>

</body>
</html>
