11-warehouse.Rmd

# Databases, warehouses & lakes {#warehouse}

*TODO - definitions, open to debate*

## Corporate data warehouse

## Postgres

*TODO - cross-ref to corporate spatial database*

## SQLite

Access to an SQLite database is controlled by permissions on the folder where it is stored. Unlike other databases, you do not create users who are authenticated by the database, and you do not grant privileges on specific datasets to other users.

SQLite is also different from other databases in that fields are not assigned specific data types and data type definitions are not strictly enforced. Instead, SQLite uses storage classes in which values of different data types can be stored. In particular, date types are handled differently. ArcGIS handles SQLite dates without needing any special attention, but reading dates from SQLite in to R or Power BI needs attention. For example, by using the `JD()` function from the `insol` R package:

```{=html}
<script src="https://gist.github.com/laurie-platt/29dc747a558844b8fc916b68be06a1d3.js"></script>
```

### SpatiaLite

[SpatiaLite](https://www.gaia-gis.it/fossil/libspatialite/index) extends SQLite to support spatial data. The [RSQLite](https://cran.r-project.org/web/packages/RSQLite/vignettes/RSQLite.html) package includes the driver for SpatiaLite.

Robin Lovelace has a [chapter on spatial databases](https://geocompr.robinlovelace.net/read-write.html) in his book Geocomputation with R.

After testing different options, SpatiaLite is the best file based database for access to spatial data via tools such as R and Power BI without the complication of ArcGIS licenses, but it also provides reliable access for ArcGIS. So far I've only tested [SpatiaLite databases created in ArcGIS](https://desktop.arcgis.com/en/arcmap/10.3/analyze/arcpy-functions/createsqlitedb.htm)