Einführung in SQL

class: center, middle, title-slide

# Einführung in SQL
## Datentypen
### Prof. Dr. Jan Kirenz
### HdM Stuttgart

---

layout: true
  
<div class="my-footer"><span> Einführung in SQL | Prof. Dr. Jan Kirenz </span></div>

---

# Setup

### Verbindung zu Datenbank herstellen

* Das Passwort `pw` muss zuvor als Objekt gespeichert werden:

```
pw = "platzhalter_für_eigenes_passwort"
```

```r
library(DBI)
library(RPostgres)

con <- dbConnect(RPostgres::Postgres(),
                 dbname = "postgres",
                  host = "localhost", 
                  port = 5432,
                  user = "postgres", 
                  password = pw)
```

* Die in dieser Präsentation verwendeten Beispiele basieren auf dem Buch "A Beginner's Guide to Storytelling with Data" von Anthony DeBarros (2018).

---

# Datentypen

* Bei der Erstellung von neuen Tabellen muss jeweils der Datentyp angegeben werden.

* Der Datentyp wird immer nach der Bezeichnung einer Spalte definiert. Hier ein Beispiel:

```sql
CREATE TABLE eagle_watch (
    observed_date date,
    eagles_seen integer
);
```

* **Characters**: Jeder Buchstabe oder Symbole
* **Numbers**: ganze Zahlen und Brüche
* **Dates and Times**: Zeitbezogene Informationen

---

# Datentypen
## Characters

- **char(n)**: Eine Spalte bei welcher der Inhalt Zeichen mit einer fixen Länge n sind.

- Eine Spalte die mit char(20) definiert ist, kann bis zu 20 Zeichen in einer Reihe speichern.

- Falls weniger als 20 Zeichen eingegeben werden, fügt PostgreSQL automatische Leerzeichen ein.

- Dieser Datentyp kann auch als `character(n)` definiert werden - dies ist die übliche Vorgehensweise.

---
# Datentypen
## Characters

* **varchar(n)**: Mit n wird die maximale Länge definiert.

- Wenn weniger eingegeben wird, wird der Inhalt nicht mit Leerzeichen aufgefüllt. `character varying (n)`

---
# Datentypen
## Characters

* **text**: keine Limitierung - maximal 1 GB. Dies ist nicht Teil des SQL-Standards, wird aber bspw. bei Microsoft SQL und MySQL genutzt.

---

# Datentypen
## Characters

* In PostgreSQL exisitert kein wesentlicher Unterschied zwischen den drei oben genannten Datentypen.

* Aufgrund der Effizienz und Flexibilität ist es in der Regel sinnvoll, __varchar__ oder __text__ zu nutzen.

* Eine Ausnahme stellen Eingaben dar, die immer eine bestimmte Länge aufweisen (wie bspw. PLZ).

---

# Datentypen

* Schritt 1

```sql
CREATE TABLE char_data_types (
    varchar_column varchar(10),
    char_column char(10),
    text_column text
);
```

* Schritt 2

```sql
INSERT INTO char_data_types
VALUES
    ('abc', 'abc', 'abc'),
    ('defghi', 'defghi','defghi');
```

* Schritt 3

```sql
COPY char_data_types TO '/tmp/typetest.txt'
WITH (FORMAT CSV, HEADER, DELIMITER '|');
```

---

# Datentypen

## Zahlen (Numbers)

Im Gegensatz zu Characters (dort können auch Zahlen abgespeichert werden) können mit numerischen Zahlen mathematische Operationen durchgeführt werden.

* **Integers**: Ganze Zahlen
* **Fixed-point** und 
* **floating-point**:

---

# Datentypen

### Integers

* smallint: 2 bytes; integer: 4 bytes; bigint: 8 bytes

* Falls sehr große Datenwerte vorhanden sind (Datenformate größer als 2.1 Mrd), ist es  empfehlenswert, mit `bigint` als Standard zu arbeiten.

* Ansonsten ist `integer` eine gute Wahl.

* Falls eine größere Zahl eingegeben wird, erscheint der Hinweis `out of range`.

---

# Datentypen

### Auto-incrementing integers

Diese Typen sind Spezialfälle der unterschiedlichen Zahlentypen. Der Index beginnt jeweils bei 1 und erhöht sich inkremmentell. Hier ein Beispiel mit `serial`:

- smallserial, 2 bytes, 1 bis 32767
- serial, 4 bytes, 1 bis 2.147.483.647
- bigserial, 8 bytes

```sql
CREATE TABLE people (
    id serial,
    person_name varchar(100) 
);
```

???
* Immer wenn eine neue Person zu der Tabelle hinzugefügt wird, erhöht sich der entsprechende Wert in der Spalte `id` um 1. Diese Funktion wird daher häufig genutzt, um einen einzigartigen Schlüssel (unique key) zu erzeugen. Bei diesem Vorgehen muss jedoch beachtete werden, dass eine gelöschte Zeile und der dazugehörige id-Wert nie ersetzt werden. Ebenso erhöht sich die `id` auch dann, wenn das Einfügen einer neuen Zeile abgebrochen wird.

---

# Datentypen
### Dezimalzahlen
#### Fixed-Point Numbers

* `numeric(precision,scale)` (arbitrary-precision type)

* __Precision__ beschreibt die maximale Anzahl an Stellen links und rechts von dem Komma (US: decimal point).

* __Scale__ beschreibt die maximale Anzahl an Stellen rechts von dem Komma (US: right of the decimal point).

---

# Datentypen
### Dezimalzahlen
#### Fixed-Point Numbers

* `Decimal(precision,scale)`

* Alternativ zu numeric kann auch decimal genutzt werden.

* Beide sind Teil des ANSI SQL Standards.

* Falls der **Scale** Wert nicht definiert wird, wird dieser automatisch auf 0 gesetzt (d.h. er wird zu einem integer).

* Falls **Precision** und **Scale** nicht definiert werden, wird die höchst mögliche Anzahl genutzt.

---

# Datentypen

### Floating-Point Types

* Dieser Typ wird auch **variable-precision-type** genannt.

* Die Datenbank speichert dabei die Zahl als Nummer und einen Exponenten, der angibt, an welcher Stelle sich der Dezimalpunkt befindet.

* Es muss dabei kein `precision` und `scale` angegeben werden.

* **real**, insgesamt 6 Zahlen vor und nach dem Komma.
 * **double precision**, insgesamt 15 Zahlen vor und nach dem Komma.

* Der Unterschied der beiden besteht also darin, wieviel Daten sie speichern können.

---

# Datentypen
### Vergleich der numerischen Typen

* Wir erzeugen eine Tabelle um die unterschiedlichen Formate zu vergleichen:

```sql
CREATE TABLE number_data_types (
    numeric_column numeric(20,5),
    real_column real,
    double_column double precision
);
```

---

# Datentypen

```sql
INSERT INTO number_data_types
VALUES
    (.7, .7, .7),
    (2.13579, 2.13579, 2.13579),
    (2.1357987654, 2.1357987654, 2.1357987654);
```

---

# Datentypen

```sql
SELECT * 
FROM number_data_types;
```

* Ausgabe einer standard SQL-Abfrage (bspw. mit pgAdmin)

| numeric_column  |real_column   | double_column   
|---|---|---|---|---|
|0.70000    |0.7   |0.7   
|2.13579   |2.13579    |2.13579   
|2.13580    |2.1358   | 2.1357987654

--
* Ausgabe des R-SQL-Pakets (darin exisiteren nur numeric Werte)

<table>
 <thead>
  <tr>
   <th style="text-align:right;"> numeric_column </th>
   <th style="text-align:right;"> real_column </th>
   <th style="text-align:right;"> double_column </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 0.70000 </td>
   <td style="text-align:right;"> 0.700000 </td>
   <td style="text-align:right;"> 0.700000 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2.13579 </td>
   <td style="text-align:right;"> 2.135790 </td>
   <td style="text-align:right;"> 2.135790 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2.13580 </td>
   <td style="text-align:right;"> 2.135799 </td>
   <td style="text-align:right;"> 2.135799 </td>
  </tr>
</tbody>
</table>

---

# Datentypen

```sql
SELECT
    numeric_column * 10000000 AS "Fixed",
    real_column * 10000000 AS "Float"
FROM number_data_types
WHERE numeric_column = .7;
```

* Ausgabe einer standard SQL-Abfrage (bspw. mit pgAdmin)

|Fixed   | Float
|---|---|
| 7000000.00000 | 6999999.88079071

* Aus diesem Grund werden "Floating-Types" als nicht exakt bezeichnet

* Ausgabe des R-SQL-Pakets

<table>
 <thead>
  <tr>
   <th style="text-align:right;"> Fixed </th>
   <th style="text-align:right;"> Float </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 7e+06 </td>
   <td style="text-align:right;"> 7e+06 </td>
  </tr>
</tbody>
</table>

---

# Datentypen

## Datum und Uhrzeit

* PostgeSQL unterstützt die wichtigsten Zeitfromen

* __timestamp__ speichert Datum und Zeit

* __date Records__  speichert nur das Datum.

* **time Records** nur die Uhrzeit.

???
timeline. Typically, you’ll want to add the keywords with time zone to ensure that the time recorded for an event includes the time zone where it occurred. Otherwise, times recorded in various places around the globe become impossible to compare.

interval Holds a value representing a unit of time expressed in the format quantity unit. It doesn’t record the start or end of a time period, only its length. Examples include 12 days or 8 hours. (The PostgreSQL documentation at https://www.postgresql.org/docs/current/static/datatype-datetime.html lists unit values ranging from microsecond to millennium.) You’ll typically use this type for calculations or filtering on other date and time columns.

---

# Datentypen

```sql
CREATE TABLE date_time_types (
    timestamp_column timestamp with time zone,
    interval_column interval
);
```

```sql
INSERT INTO date_time_types
VALUES
    ('2018-12-31 01:00 EST','2 days'),
    ('2018-12-31 01:00 PST','1 month'),
    ('2018-12-31 01:00 Australia/Melbourne','1 century'),
    (now(),'1 week');
```

---

# Datentypen

```sql
SELECT * 
FROM date_time_types;
```

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> timestamp_column </th>
   <th style="text-align:left;"> interval_column </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> 2018-12-31 07:00:00 </td>
   <td style="text-align:left;"> 2 days </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2018-12-31 10:00:00 </td>
   <td style="text-align:left;"> 1 mon </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2018-12-30 15:00:00 </td>
   <td style="text-align:left;"> 100 years </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2021-01-03 18:07:00 </td>
   <td style="text-align:left;"> 7 days </td>
  </tr>
</tbody>
</table>

???
PostgreSQL changed 1 century to 100 years and 1 week to 7 days because of its preferred default settings for interval display.

---

# Datentypen

## Interval Data Type

```sql
SELECT
    timestamp_column,
    interval_column,
    timestamp_column - interval_column AS new_date
FROM date_time_types;
```

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> timestamp_column </th>
   <th style="text-align:left;"> interval_column </th>
   <th style="text-align:left;"> new_date </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> 2018-12-31 07:00:00 </td>
   <td style="text-align:left;"> 2 days </td>
   <td style="text-align:left;"> 2018-12-29 07:00:00 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2018-12-31 10:00:00 </td>
   <td style="text-align:left;"> 1 mon </td>
   <td style="text-align:left;"> 2018-11-30 10:00:00 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2018-12-30 15:00:00 </td>
   <td style="text-align:left;"> 100 years </td>
   <td style="text-align:left;"> 1918-12-30 15:00:00 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2021-01-03 18:07:00 </td>
   <td style="text-align:left;"> 7 days </td>
   <td style="text-align:left;"> 2020-12-27 18:07:00 </td>
  </tr>
</tbody>
</table>

???
* Computed columns are called expressions; we’ll use this technique often.

* example, let’s say you have a column that holds the date a client signed a contract. Using interval data, you can add 90 days to each contract date to determine when to follow up with the client.

---

## Datentransformationen mit CAST()

* Die CAST-Funktion kann nur angewendet werden, wenn die originären Werte in das gewünschte Format überführt werden können.

* Beispielsweise können Zahlen als Text gespeichert werden, nicht jedoch Text als Zahlen.

* Hier werden die ersten zehn Ziffern der Spalte in ein neues Format umgewandelt

---

## Datentransformationen mit CAST()

```sql
SELECT timestamp_column, 
       CAST(timestamp_column AS varchar(10))
FROM date_time_types;
```

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> timestamp_column </th>
   <th style="text-align:left;"> timestamp_column..2 </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> 2018-12-31 07:00:00 </td>
   <td style="text-align:left;"> 2018-12-31 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2018-12-31 10:00:00 </td>
   <td style="text-align:left;"> 2018-12-31 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2018-12-30 15:00:00 </td>
   <td style="text-align:left;"> 2018-12-30 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2021-01-03 18:07:00 </td>
   <td style="text-align:left;"> 2021-01-03 </td>
  </tr>
</tbody>
</table>

---

## Datentransformationen mit CAST()

```sql
SELECT numeric_column,
       CAST(numeric_column AS integer),
       CAST(numeric_column AS varchar(6))
FROM number_data_types;
```

<table>
 <thead>
  <tr>
   <th style="text-align:right;"> numeric_column </th>
   <th style="text-align:right;"> numeric_column..2 </th>
   <th style="text-align:left;"> numeric_column..3 </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 0.70000 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:left;"> 0.7000 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2.13579 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> 2.1357 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2.13580 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> 2.1358 </td>
  </tr>
</tbody>
</table>

---
## Datentransformationen mit CAST()

### CAST Shortcut

* Der Befehl CAST kann (nur!) in PostgreSQL mit `::` abgekürzt werden

```sql
SELECT timestamp_column, CAST(timestamp_column AS varchar(10))
FROM date_time_types;
```

---
## Datentransformationen mit CAST()

### CAST Shortcut

```sql
SELECT timestamp_column::varchar(10)
FROM date_time_types;
```

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> timestamp_column </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> 2018-12-31 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2018-12-31 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2018-12-30 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> 2021-01-03 </td>
  </tr>
</tbody>
</table>

---

# Datenimport und Datenexport

* In PostgreSQL kann der Befehl COPY für das Einlesen und Exportieren verwendet werden

* Überblick über [Datenkonvertierungen in PostgreSQL]( https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL)

* Vorgehensweise für den Import von Daten:

1. Quelldaten als CSV bereitstellen 
1. Eine Tabelle für die Datenspeicherung vorbereiten  
1. Den COPY-Befehl für den Import der Daten schreiben

* Beispiel einer CSV-Datei:

```
FIRSTNAME,LASTNAME,STREET,CITY,STATE,PHONE
John,Doe,"123 Main St., Apartment 200",Hyde Park,NY,845-555-1212
```

???

CSV 
A delimited text file contains rows of data, and each row represents one row in a table. In each row, a character separates, or delimits, each data column.

I’ve seen all kinds of characters used as delimiters, from ampersands to pipes, but the comma is most commonly used; hence the name of a file type you’ll see often: comma-separated values (CSV). The terms CSV and comma-delimited are interchangeable.

---

# Datenimport und Datenexport

### CSV Daten

* Falls in den CSV-Daten Kommas verwendet werden, müssen die entsprechenden Werte in den Spalten mit Anführungszeichen versehen werden.

```
John,Doe,"123 Main St., Apartment 200",Hyde Park,NY,845-555-1212
```

---

## Datenimport mit COPY

* Beipsielhafter Syntax

WINDOWS:

```
COPY table_name
FROM 'C:\YourDirectory\your_file.csv'
WITH (FORMAT CSV, HEADER);
```

MAC oder Linux

```
COPY table_name
FROM 'Users/YourDirectory/your_file.csv'
WITH (FORMAT CSV, HEADER);
```

---

### Import von Daten am Beispiel von Zensusdaten (USA 2010)

* Für mehr Informationen zu den Daten, siehe das [Data dictionary](http://www.census.gov/prod/cen2010/doc/pl94-171.pdf)

```sql
CREATE TABLE us_counties_2010 (
    geo_name varchar(90),                    -- Name of the geography
    state_us_abbreviation varchar(2),        -- State/U.S. abbreviation
    summary_level varchar(3),                -- Summary Level
    region smallint,                         -- Region
    division smallint,                       -- Division
    state_fips varchar(2),                   -- State FIPS code
    county_fips varchar(3),                  -- County code
    area_land bigint,                        -- Area (Land) in square meters
    area_water bigint,                        -- Area (Water) in square meters
    population_count_100_percent integer,    -- Population count (100%)
    housing_unit_count_100_percent integer,  -- Housing Unit count (100%)
    internal_point_lat numeric(10,7),        -- Internal point (latitude)
    internal_point_lon numeric(10,7),        -- Internal point (longitude)

-- This section is referred to as P1. Race:
    p0010001 integer,   -- Total population
    p0010002 integer,   -- Population of one race:
    p0010003 integer,       -- White Alone
    p0010004 integer,       -- Black or African American alone
    p0010005 integer,       -- American Indian and Alaska Native alone
    p0010006 integer,       -- Asian alone
    p0010007 integer,       -- Native Hawaiian and Other Pacific Islander alone
    p0010008 integer,       -- Some Other Race alone
    p0010009 integer,   -- Population of two or more races
    p0010010 integer,   -- Population of two races:
    p0010011 integer,       -- White; Black or African American
    p0010012 integer,       -- White; American Indian and Alaska Native
    p0010013 integer,       -- White; Asian
    p0010014 integer,       -- White; Native Hawaiian and Other Pacific Islander
    p0010015 integer,       -- White; Some Other Race
    p0010016 integer,       -- Black or African American; American Indian and Alaska Native
    p0010017 integer,       -- Black or African American; Asian
    p0010018 integer,       -- Black or African American; Native Hawaiian and Other Pacific Islander
    p0010019 integer,       -- Black or African American; Some Other Race
    p0010020 integer,       -- American Indian and Alaska Native; Asian
    p0010021 integer,       -- American Indian and Alaska Native; Native Hawaiian and Other Pacific Islander
    p0010022 integer,       -- American Indian and Alaska Native; Some Other Race
    p0010023 integer,       -- Asian; Native Hawaiian and Other Pacific Islander
    p0010024 integer,       -- Asian; Some Other Race
    p0010025 integer,       -- Native Hawaiian and Other Pacific Islander; Some Other Race
    p0010026 integer,   -- Population of three races
    p0010047 integer,   -- Population of four races
    p0010063 integer,   -- Population of five races
    p0010070 integer,   -- Population of six races

-- This section is referred to as P2. HISPANIC OR LATINO, AND NOT HISPANIC OR LATINO BY RACE
    p0020001 integer,   -- Total
    p0020002 integer,   -- Hispanic or Latino
    p0020003 integer,   -- Not Hispanic or Latino:
    p0020004 integer,   -- Population of one race:
    p0020005 integer,       -- White Alone
    p0020006 integer,       -- Black or African American alone
    p0020007 integer,       -- American Indian and Alaska Native alone
    p0020008 integer,       -- Asian alone
    p0020009 integer,       -- Native Hawaiian and Other Pacific Islander alone
    p0020010 integer,       -- Some Other Race alone
    p0020011 integer,   -- Two or More Races
    p0020012 integer,   -- Population of two races
    p0020028 integer,   -- Population of three races
    p0020049 integer,   -- Population of four races
    p0020065 integer,   -- Population of five races
    p0020072 integer,   -- Population of six races

-- This section is referred to as P3. RACE FOR THE POPULATION 18 YEARS AND OVER
    p0030001 integer,   -- Total
    p0030002 integer,   -- Population of one race:
    p0030003 integer,       -- White alone
    p0030004 integer,       -- Black or African American alone
    p0030005 integer,       -- American Indian and Alaska Native alone
    p0030006 integer,       -- Asian alone
    p0030007 integer,       -- Native Hawaiian and Other Pacific Islander alone
    p0030008 integer,       -- Some Other Race alone
    p0030009 integer,   -- Two or More Races
    p0030010 integer,   -- Population of two races
    p0030026 integer,   -- Population of three races
    p0030047 integer,   -- Population of four races
    p0030063 integer,   -- Population of five races
    p0030070 integer,   -- Population of six races

-- This section is referred to as P4. HISPANIC OR LATINO, AND NOT HISPANIC OR LATINO BY RACE
    -- FOR THE POPULATION 18 YEARS AND OVER
    p0040001 integer,   -- Total
    p0040002 integer,   -- Hispanic or Latino
    p0040003 integer,   -- Not Hispanic or Latino:
    p0040004 integer,   -- Population of one race:
    p0040005 integer,   -- White alone
    p0040006 integer,   -- Black or African American alone
    p0040007 integer,   -- American Indian and Alaska Native alone
    p0040008 integer,   -- Asian alone
    p0040009 integer,   -- Native Hawaiian and Other Pacific Islander alone
    p0040010 integer,   -- Some Other Race alone
    p0040011 integer,   -- Two or More Races
    p0040012 integer,   -- Population of two races
    p0040028 integer,   -- Population of three races
    p0040049 integer,   -- Population of four races
    p0040065 integer,   -- Population of five races
    p0040072 integer,   -- Population of six races

-- This section is referred to as H1. OCCUPANCY STATUS
    h0010001 integer,   -- Total housing units
    h0010002 integer,   -- Occupied
    h0010003 integer    -- Vacant
);
```

---

### Import von Daten am Beispiel von Zensusdaten (USA 2010)

* Benötigte CSV: [us_counties_2010.csv](https://github.com/kirenz/datasets/blob/master/us_counties_2010.csv)

```sql
COPY us_counties_2010
FROM '/tmp/us_counties_2010.csv'
WITH (FORMAT CSV, HEADER);
```

```sql
SELECT * 
FROM us_counties_2010
LIMIT 40;
```

---
class: center, middle

<div id="htmlwidget-b621d88bb8c9a77c60a9" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-b621d88bb8c9a77c60a9">{"x":{"filter":"none","fillContainer":false,"data":[["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"],["Autauga County","Baldwin County","Barbour County","Bibb County","Blount County","Bullock County","Butler County","Calhoun County","Chambers County","Cherokee County","Chilton County","Choctaw County","Clarke County","Clay County","Cleburne County","Coffee County","Colbert County","Conecuh County","Coosa County","Covington County","Crenshaw County","Cullman County","Dale County","Dallas County","DeKalb County","Elmore County","Escambia County","Etowah County","Fayette County","Franklin County","Geneva County","Greene County","Hale County","Henry County","Houston County","Jackson County","Jefferson County","Lamar County","Lauderdale County","Lawrence County"],["AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL","AL"],["050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050","050"],[3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3],[6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6],["01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01","01"],["001","003","005","007","009","011","013","015","017","019","021","023","025","027","029","031","033","035","037","039","041","043","045","047","049","051","053","055","057","059","061","063","065","067","069","071","073","075","077","079"],[1539582278,4117521611,2291818968,1612480789,1669961855,1613056905,2011976894,1569189995,1545009282,1434075952,1794483586,2365954748,3207609488,1564252280,1450652767,1758528786,1534877333,2201894769,1685891075,2668869505,1576888006,1903228333,1453371157,2534807386,2012662359,1601867593,2447746932,1385618994,1625631609,1641587713,1487710425,1676007032,1667803914,1454926258,1501737720,2791664869,2878192209,1566544654,1729327931,1788846568],[25775735,1133190229,50864716,9289057,15157440,6056528,2726814,16624267,17048142,119858898,20586383,19059251,36542656,5284628,2365681,3944730,76430813,6644940,39946646,34526309,5376392,52266921,4016457,39134826,4123588,99862541,20873208,35322991,4392821,32897905,11765293,33231212,32525967,16986789,4739984,126657194,32474487,1600679,138027479,68688464],[54571,182265,27457,22915,57322,10914,20947,118572,34215,25989,43643,13859,25833,13932,14972,49948,54428,13228,11539,37765,13906,80406,50251,43820,71109,79303,38319,104430,17241,31704,26790,9045,15760,17302,101547,53227,658466,14564,92709,34339],[22135,104061,11829,8981,23887,4493,9964,53289,17004,16267,19278,7269,12638,6776,6718,22330,25758,7093,6478,18829,6735,37054,22677,20208,31109,32657,16486,47454,8437,14022,12687,5007,7655,8891,45319,24786,300552,7354,43791,15229],[32.5363818,30.6592183,31.8706701,33.0158929,33.9774479,32.1017589,31.751667,33.7717061,32.9179433,34.0695153,32.8540595,31.9910082,31.6855211,33.2678085,33.671981,31.4021833,34.703112,31.4282925,32.9314453,31.2439873,31.7328256,34.1319229,31.4306536,32.3335397,34.460929,32.5972286,31.1217475,34.0476377,33.7322485,34.4419884,31.0908664,32.8444965,32.7527958,31.5169779,31.1581928,34.7635224,33.5534439,33.7870852,34.9041221,34.5297759],[-86.6444901,-87.7460666,-85.4054562,-87.1271475,-86.5672464,-85.7172613,-86.6819689,-85.8225133,-85.3918121,-85.6542417,-86.7266275,-88.2488874,-87.8186244,-85.862051,-85.516109,-85.9892014,-87.8014569,-86.9920291,-86.2434818,-86.4487206,-86.3192219,-86.8692666,-85.609476,-87.1143556,-85.803992,-86.1427385,-87.168429,-86.0344203,-87.7520486,-87.8428147,-85.8243464,-87.9642005,-87.6230608,-85.2399712,-85.2963976,-85.9774003,-86.8965359,-88.0874309,-87.6509966,-87.3218651],[54571,182265,27457,22915,57322,10914,20947,118572,34215,25989,43643,13859,25833,13932,14972,49948,54428,13228,11539,37765,13906,80406,50251,43820,71109,79303,38319,104430,17241,31704,26790,9045,15760,17302,101547,53227,658466,14564,92709,34339],[53702,179542,27199,22712,56638,10828,20786,116597,33830,25602,43137,13801,25657,13697,14812,48701,53583,13096,11436,37243,13704,79517,48746,43521,69535,78159,37739,102885,17070,31174,26374,9002,15672,17126,99788,51849,651191,14381,91403,32861],[42855,156153,13180,17381,53068,2507,11399,88840,20112,24081,36713,7731,14070,11380,14079,37330,43789,6788,7648,32022,10097,76133,37236,12769,60087,60455,23784,83823,14910,26320,23127,1575,6266,11865,71053,48348,349166,12626,80112,26647],[9643,17105,12875,5047,761,7666,9095,24382,13257,1208,4230,6012,11336,2066,498,8359,8768,6149,3582,4716,3254,856,9679,30423,1078,15900,12220,15796,1969,1228,2539,7370,9301,4942,26176,1781,276525,1643,9257,3938],[232,1216,114,64,307,23,60,540,69,135,155,18,103,55,51,645,267,44,40,214,57,408,371,85,969,285,1288,448,52,215,224,17,25,57,446,719,1740,24,338,1953],[474,1348,107,22,117,20,177,845,168,54,130,12,77,24,23,644,229,17,16,155,189,343,534,149,203,518,92,672,37,63,67,15,35,54,820,185,9158,4,685,42],[32,89,29,13,38,43,7,96,10,1,55,0,4,0,10,100,20,1,15,1,8,18,44,11,147,63,12,171,0,10,8,0,1,2,49,48,244,2,34,6],[466,3631,894,185,2347,569,48,1894,214,123,1854,28,67,172,151,1623,510,97,135,135,99,1759,882,84,7051,938,343,1975,102,3338,409,25,44,206,1244,768,14358,82,977,275],[869,2723,258,203,684,86,161,1975,385,387,506,58,176,235,160,1247,845,132,103,522,202,889,1505,299,1574,1144,580,1545,171,530,416,43,88,176,1759,1378,7275,183,1306,1478],[814,2583,254,195,662,82,153,1848,373,376,487,52,157,227,156,1144,813,122,100,508,197,860,1369,271,1521,1084,564,1487,169,516,394,41,75,168,1641,1361,6778,177,1265,1428],[219,658,76,50,112,12,58,676,162,94,158,22,53,86,59,284,316,38,24,204,84,150,453,133,192,339,158,544,82,122,128,16,14,73,581,227,2069,62,526,118],[262,1035,49,77,330,28,41,520,101,206,142,8,45,98,57,398,330,43,40,217,80,495,361,34,969,322,265,549,58,178,172,5,27,51,459,942,1450,66,404,1174],[177,336,7,16,74,3,17,223,27,19,44,7,11,6,18,218,51,8,6,36,7,69,241,7,67,196,36,111,7,18,25,2,7,12,174,54,1130,12,116,40],[11,35,2,3,8,0,1,49,1,12,5,0,1,2,0,18,8,0,1,1,0,20,17,2,19,20,6,25,2,4,1,0,0,2,17,9,53,2,9,11],[50,311,34,16,102,3,13,143,37,35,97,1,11,19,15,111,55,10,11,28,7,102,127,7,199,93,19,140,17,153,36,4,14,10,165,71,709,9,114,35],[32,63,54,9,12,8,10,67,11,9,10,10,18,10,0,28,20,9,12,11,9,4,67,31,3,41,41,25,3,3,22,13,3,10,95,30,509,16,47,27],[19,34,2,13,2,5,4,48,10,0,6,3,5,0,1,30,16,9,0,0,4,1,22,17,2,14,11,20,0,4,0,0,7,3,56,1,252,0,9,1],[9,7,4,5,0,2,3,20,0,0,5,0,3,2,0,3,0,1,1,0,0,1,9,19,0,7,3,6,0,0,1,0,2,2,20,0,59,0,5,1],[16,38,7,4,6,9,3,46,22,0,5,1,9,3,1,21,14,4,1,4,1,4,32,20,9,23,10,36,0,13,5,1,1,5,39,8,262,5,14,2],[0,12,0,0,3,0,0,8,0,0,0,0,0,1,4,11,0,0,0,2,1,3,7,1,5,0,4,4,0,0,1,0,0,0,5,2,38,1,3,14],[0,0,1,0,0,0,0,2,0,0,0,0,0,0,0,1,0,0,4,1,0,1,0,0,6,0,3,0,0,0,0,0,0,0,5,1,4,0,0,0],[5,15,2,2,2,3,3,4,0,0,10,0,0,0,1,4,2,0,0,2,2,1,2,0,18,5,4,10,0,18,1,0,0,0,8,9,44,0,2,4],[5,11,1,0,4,0,0,17,0,0,1,0,0,0,0,8,0,0,0,0,0,1,16,0,5,8,0,2,0,0,2,0,0,0,8,3,29,2,9,0],[8,14,5,0,7,0,0,17,1,1,1,0,1,0,0,3,1,0,0,2,2,1,7,0,11,9,3,13,0,3,0,0,0,0,9,3,128,2,6,0],[1,14,10,0,0,9,0,8,1,0,3,0,0,0,0,6,0,0,0,0,0,7,8,0,16,7,1,2,0,0,0,0,0,0,0,1,42,0,1,1],[49,128,3,8,21,4,8,109,11,11,19,6,15,8,2,99,31,5,3,13,4,26,129,27,52,59,16,58,2,13,18,2,13,6,104,17,450,5,35,43],[6,11,0,0,1,0,0,17,1,0,0,0,4,0,1,4,1,0,0,1,1,2,4,0,1,0,0,0,0,0,1,0,0,2,12,0,43,0,5,3],[0,1,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,5,0,0,0,1,3,1,0,1,0,0,0,1,3,0,0,0,2,0,4,1,1,4],[0,0,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],[54571,182265,27457,22915,57322,10914,20947,118572,34215,25989,43643,13859,25833,13932,14972,49948,54428,13228,11539,37765,13906,80406,50251,43820,71109,79303,38319,104430,17241,31704,26790,9045,15760,17302,101547,53227,658466,14564,92709,34339],[1310,7992,1387,406,4626,777,191,3893,536,320,3420,73,271,399,307,3180,1093,161,230,483,204,3454,2821,309,9690,2129,718,3447,204,4710,920,69,140,389,2995,1339,25488,180,2082,574],[53261,174273,26070,22509,52696,10137,20756,114679,33679,25669,40223,13786,25562,13533,14665,46768,53335,13067,11309,37282,13702,76952,47430,43511,61419,77174,37601,100983,17037,26994,25870,8976,15620,16913,98552,51888,632978,14384,90627,33765],[52500,171976,25861,22328,52129,10078,20614,112975,33352,25322,39870,13730,25406,13317,14528,45725,52571,12953,11221,36794,13509,76184,46223,43233,60133,76170,37062,99651,16885,26666,25499,8937,15544,16752,97107,50606,626925,14210,89479,32336],[42154,152200,12837,17191,50952,2392,11324,87285,19893,23929,35395,7700,13943,11186,13956,36134,43334,6764,7604,31751,10020,74568,35705,12676,57997,59449,23508,82789,14832,25249,22692,1562,6213,11731,69731,47937,340213,12542,79228,26420],[9595,16966,12820,5024,724,7637,9047,24177,13206,1206,4171,5990,11280,2048,494,8257,8702,6122,3556,4685,3241,821,9546,30314,1029,15814,12162,15716,1958,1211,2523,7349,9265,4915,26038,1771,275511,1635,9190,3917],[217,1146,60,64,285,20,59,480,57,122,134,18,96,55,50,602,262,44,40,196,56,397,337,78,865,269,1274,372,47,121,197,11,23,54,393,680,1431,24,323,1928],[467,1340,107,22,115,20,174,830,166,49,125,12,74,23,23,629,224,17,14,148,189,339,527,144,166,514,86,657,37,51,64,15,35,45,806,165,9085,4,675,41],[22,79,24,7,18,4,7,94,7,1,11,0,4,0,1,51,14,0,0,1,1,18,42,6,35,50,12,30,0,2,8,0,1,2,43,29,154,1,23,5],[45,245,13,20,35,5,3,109,23,15,34,10,9,5,4,52,35,6,7,13,2,41,66,15,41,74,20,87,11,32,15,0,7,5,96,24,531,4,40,25],[761,2297,209,181,567,59,142,1704,327,347,353,56,156,216,137,1043,764,114,88,488,193,768,1207,278,1286,1004,539,1332,152,328,371,39,76,161,1445,1282,6053,174,1148,1429],[719,2205,206,175,547,56,139,1602,317,337,345,50,144,208,135,967,747,110,86,475,188,747,1112,254,1241,956,523,1286,151,319,349,38,65,153,1355,1270,5656,169,1110,1389],[36,87,2,6,19,3,3,93,10,10,8,6,12,8,0,74,16,4,2,12,4,19,92,24,44,47,16,46,1,8,18,1,11,6,80,12,357,5,33,40],[6,5,0,0,1,0,0,8,0,0,0,0,0,0,1,2,1,0,0,1,1,1,3,0,1,0,0,0,0,0,1,0,0,2,8,0,38,0,5,0],[0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,0,0,1,0,0,0,1,3,0,0,0,2,0,2,0,0,0],[0,0,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],[39958,140367,21442,17714,43216,8484,15891,91446,26512,20423,32683,10718,19444,10788,11421,37864,42394,10186,9169,29242,10598,61765,37774,32225,52743,60596,29640,80444,13404,23837,20799,6851,11845,13384,76621,41242,503938,11337,72727,26375],[39530,138905,21275,17584,42810,8432,15811,90441,26332,20216,32442,10685,19351,10683,11356,37257,41986,10112,9100,28999,10499,61205,37040,32074,51765,59988,29351,79646,13314,23562,20591,6831,11784,13308,75767,40381,499863,11242,72065,25442],[31910,122238,10855,13403,40515,2164,9109,70313,16182,19090,28046,6083,11056,8966,10820,29045,34706,5578,6216,25296,7835,58901,28954,10506,46173,46838,18870,66048,11673,20401,18202,1357,5013,9391,55679,37853,280999,9944,63737,20866],[6767,12272,9647,3975,549,5838,6504,17746,9798,909,2991,4562,8123,1528,379,6090,6598,4410,2734,3335,2405,655,6743,21322,796,11866,9203,11493,1507,925,1901,5427,6699,3713,18237,1321,200810,1217,6880,3059],[180,923,86,47,227,21,44,451,58,105,114,16,69,46,41,470,204,34,33,161,44,320,283,70,660,227,957,346,44,147,175,17,15,44,351,577,1344,22,265,1337],[346,1002,80,14,91,17,119,665,138,35,97,9,54,19,16,526,166,17,14,116,135,257,453,118,144,402,71,499,27,45,55,13,29,36,622,138,7042,4,576,34],[23,70,24,11,23,31,3,70,6,1,42,0,2,0,9,74,10,0,9,1,5,17,37,11,83,42,7,97,0,10,4,0,1,2,39,37,183,1,26,6],[304,2400,583,134,1405,361,32,1196,150,76,1152,15,47,124,91,1052,302,73,94,90,75,1055,570,47,3909,613,243,1163,63,2034,254,17,27,122,839,455,9485,54,581,140],[428,1462,167,130,406,52,80,1005,180,207,241,33,93,105,65,607,408,74,69,243,99,560,734,151,978,608,289,798,90,275,208,20,61,76,854,861,4075,95,662,933],[404,1398,163,128,394,49,76,939,173,202,234,30,83,101,63,559,395,68,67,234,96,538,666,134,947,575,279,764,88,266,192,19,54,71,782,857,3789,90,627,916],[22,60,3,2,12,3,4,62,6,5,7,3,10,4,0,46,12,3,2,9,3,20,62,16,30,32,10,34,2,8,16,1,7,5,64,4,258,4,29,13],[2,3,0,0,0,0,0,3,1,0,0,0,0,0,1,2,1,0,0,0,0,1,3,0,1,0,0,0,0,0,0,0,0,0,6,0,24,0,5,2],[0,1,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,3,0,0,0,1,3,1,0,1,0,0,0,1,0,0,0,0,2,0,4,1,1,2],[0,0,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],[39958,140367,21442,17714,43216,8484,15891,91446,26512,20423,32683,10718,19444,10788,11421,37864,42394,10186,9169,29242,10598,61765,37774,32225,52743,60596,29640,80444,13404,23837,20799,6851,11845,13384,76621,41242,503938,11337,72727,26375],[828,5186,925,310,2724,485,130,2473,364,171,2098,55,183,261,185,2007,643,113,164,325,149,2079,1756,209,5286,1422,493,2011,131,2799,530,41,90,234,1945,771,17090,116,1262,307],[39130,135181,20517,17404,40492,7999,15761,88973,26148,20252,30585,10663,19261,10527,11236,35857,41751,10073,9005,28917,10449,59686,36018,32016,47457,59174,29147,78433,13273,21038,20269,6810,11755,13150,74676,40471,486848,11221,71465,26068],[38746,133937,20382,17284,40141,7963,15694,88088,25995,20051,30421,10631,19177,10430,11176,35330,41377,10006,8946,28688,10356,59176,35421,31875,46605,58644,28878,77733,13194,20858,20080,6792,11701,13079,73984,39646,483434,11129,70880,25148],[31461,119671,10624,13247,39285,2102,9062,69294,16037,19005,27246,6058,10965,8849,10740,28288,34433,5562,6181,25110,7781,57953,27980,10444,45072,46153,18688,65421,11622,19800,17968,1353,4979,9305,54818,37615,274788,9892,63181,20737],[6738,12193,9605,3963,524,5821,6468,17621,9761,909,2953,4543,8089,1516,377,6025,6565,4391,2719,3317,2395,630,6662,21242,774,11809,9163,11454,1500,916,1891,5415,6677,3698,18162,1317,200129,1210,6843,3043],[169,876,47,47,212,18,43,409,48,99,105,16,63,46,40,439,200,34,33,150,43,309,266,63,601,214,946,311,41,90,158,11,13,43,315,552,1134,22,255,1328],[341,994,80,14,89,17,117,653,136,32,94,9,51,18,16,517,163,17,12,109,135,256,447,115,128,399,65,487,27,40,52,13,29,29,612,125,6994,4,566,33],[15,62,21,5,14,3,3,69,3,1,9,0,2,0,0,40,7,0,0,1,1,17,35,6,19,37,7,21,0,2,4,0,1,2,33,23,119,1,18,5],[22,141,5,8,17,2,1,42,10,5,14,5,7,1,3,21,9,2,1,1,1,11,31,5,11,32,9,39,4,10,7,0,2,2,44,14,270,0,17,2],[384,1244,135,120,351,36,67,885,153,201,164,32,84,97,60,527,374,67,59,229,93,510,597,141,852,530,269,700,79,180,189,18,54,71,692,825,3414,92,585,920],[363,1198,132,119,341,33,65,826,148,196,162,29,75,93,58,487,366,65,58,221,90,493,551,125,825,502,259,673,78,174,173,18,47,66,638,821,3188,88,553,907],[19,46,2,1,10,3,2,56,5,5,2,3,9,4,0,39,7,2,1,8,3,15,44,16,26,27,10,27,1,5,16,0,7,5,47,4,202,4,27,13],[2,0,0,0,0,0,0,2,0,0,0,0,0,0,1,1,1,0,0,0,0,1,2,0,1,0,0,0,0,0,0,0,0,0,5,0,22,0,5,0],[0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,0,0,1,0,0,0,1,0,0,0,0,2,0,2,0,0,0],[0,0,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],[22135,104061,11829,8981,23887,4493,9964,53289,17004,16267,19278,7269,12638,6776,6718,22330,25758,7093,6478,18829,6735,37054,22677,20208,31109,32657,16486,47454,8437,14022,12687,5007,7655,8891,45319,24786,300552,7354,43791,15229],[20221,73180,9820,7953,21578,3745,8491,47331,13933,10626,16558,5866,10337,5670,5891,19849,22773,5625,4794,15531,5652,31864,20065,17064,26842,28301,14157,42036,7100,12286,10920,3764,6273,6994,40969,21513,263568,6103,38680,13654],[1914,30881,2009,1028,2309,748,1473,5958,3071,5641,2720,1403,2301,1106,827,2481,2985,1468,1684,3298,1083,5190,2612,3144,4267,4356,2329,5418,1337,1736,1767,1243,1382,1897,4350,3273,36984,1251,5111,1575]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>geo_name<\/th>\n      <th>state_us_abbreviation<\/th>\n      <th>summary_level<\/th>\n      <th>region<\/th>\n      <th>division<\/th>\n      <th>state_fips<\/th>\n      <th>county_fips<\/th>\n      <th>area_land<\/th>\n      <th>area_water<\/th>\n      <th>population_count_100_percent<\/th>\n      <th>housing_unit_count_100_percent<\/th>\n      <th>internal_point_lat<\/th>\n      <th>internal_point_lon<\/th>\n      <th>p0010001<\/th>\n      <th>p0010002<\/th>\n      <th>p0010003<\/th>\n      <th>p0010004<\/th>\n      <th>p0010005<\/th>\n      <th>p0010006<\/th>\n      <th>p0010007<\/th>\n      <th>p0010008<\/th>\n      <th>p0010009<\/th>\n      <th>p0010010<\/th>\n      <th>p0010011<\/th>\n      <th>p0010012<\/th>\n      <th>p0010013<\/th>\n      <th>p0010014<\/th>\n      <th>p0010015<\/th>\n      <th>p0010016<\/th>\n      <th>p0010017<\/th>\n      <th>p0010018<\/th>\n      <th>p0010019<\/th>\n      <th>p0010020<\/th>\n      <th>p0010021<\/th>\n      <th>p0010022<\/th>\n      <th>p0010023<\/th>\n      <th>p0010024<\/th>\n      <th>p0010025<\/th>\n      <th>p0010026<\/th>\n      <th>p0010047<\/th>\n      <th>p0010063<\/th>\n      <th>p0010070<\/th>\n      <th>p0020001<\/th>\n      <th>p0020002<\/th>\n      <th>p0020003<\/th>\n      <th>p0020004<\/th>\n      <th>p0020005<\/th>\n      <th>p0020006<\/th>\n      <th>p0020007<\/th>\n      <th>p0020008<\/th>\n      <th>p0020009<\/th>\n      <th>p0020010<\/th>\n      <th>p0020011<\/th>\n      <th>p0020012<\/th>\n      <th>p0020028<\/th>\n      <th>p0020049<\/th>\n      <th>p0020065<\/th>\n      <th>p0020072<\/th>\n      <th>p0030001<\/th>\n      <th>p0030002<\/th>\n      <th>p0030003<\/th>\n      <th>p0030004<\/th>\n      <th>p0030005<\/th>\n      <th>p0030006<\/th>\n      <th>p0030007<\/th>\n      <th>p0030008<\/th>\n      <th>p0030009<\/th>\n      <th>p0030010<\/th>\n      <th>p0030026<\/th>\n      <th>p0030047<\/th>\n      <th>p0030063<\/th>\n      <th>p0030070<\/th>\n      <th>p0040001<\/th>\n      <th>p0040002<\/th>\n      <th>p0040003<\/th>\n      <th>p0040004<\/th>\n      <th>p0040005<\/th>\n      <th>p0040006<\/th>\n      <th>p0040007<\/th>\n      <th>p0040008<\/th>\n      <th>p0040009<\/th>\n      <th>p0040010<\/th>\n      <th>p0040011<\/th>\n      <th>p0040012<\/th>\n      <th>p0040028<\/th>\n      <th>p0040049<\/th>\n      <th>p0040065<\/th>\n      <th>p0040072<\/th>\n      <th>h0010001<\/th>\n      <th>h0010002<\/th>\n      <th>h0010003<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":6,"columnDefs":[{"className":"dt-right","targets":[4,5,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,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91]},{"orderable":false,"targets":0}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[6,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>

---

## Zensusdaten (USA 2010)

```sql
SELECT geo_name, state_us_abbreviation, area_land
FROM us_counties_2010
ORDER BY area_land DESC
LIMIT 3;
```

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> geo_name </th>
   <th style="text-align:left;"> state_us_abbreviation </th>
   <th style="text-align:right;"> area_land </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Yukon-Koyukuk Census Area </td>
   <td style="text-align:left;"> AK </td>
   <td style="text-align:right;"> 376855656455 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> North Slope Borough </td>
   <td style="text-align:left;"> AK </td>
   <td style="text-align:right;"> 229720054439 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Bethel Census Area </td>
   <td style="text-align:left;"> AK </td>
   <td style="text-align:right;"> 105075822708 </td>
  </tr>
</tbody>
</table>

---

## Zensusdaten (USA 2010)

```sql
SELECT geo_name, state_us_abbreviation, internal_point_lon
FROM us_counties_2010
ORDER BY internal_point_lon DESC
LIMIT 5;
```

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> geo_name </th>
   <th style="text-align:left;"> state_us_abbreviation </th>
   <th style="text-align:right;"> internal_point_lon </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Aleutians West Census Area </td>
   <td style="text-align:left;"> AK </td>
   <td style="text-align:right;"> 178.33881 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Washington County </td>
   <td style="text-align:left;"> ME </td>
   <td style="text-align:right;"> -67.60935 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Hancock County </td>
   <td style="text-align:left;"> ME </td>
   <td style="text-align:right;"> -68.37070 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Aroostook County </td>
   <td style="text-align:left;"> ME </td>
   <td style="text-align:right;"> -68.64941 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Penobscot County </td>
   <td style="text-align:left;"> ME </td>
   <td style="text-align:right;"> -68.65749 </td>
  </tr>
</tbody>
</table>

---

## Import einer Datenteilmenge (Supervisor salaries)

* CREATE TABLE:

```sql
CREATE TABLE supervisor_salaries (
    town varchar(30),
    county varchar(30),
    supervisor varchar(30),
    start_date date,
    salary money,
    benefits money
);
```

???
Creating a table to track supervisor salaries

---
### Import einer Datenteilmenge (Supervisor salaries)

* Daten sind hier verfügbar: [supervisor_salaries.csv](https://github.com/kirenz/datasets/blob/master/supervisor_salaries.csv)

```sql
COPY supervisor_salaries (town, supervisor, salary)
FROM '/tmp/supervisor_salaries.csv'
WITH (FORMAT CSV, HEADER);
```

```sql
-- Check the data
SELECT * 
FROM supervisor_salaries 
LIMIT 2;
```

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> town </th>
   <th style="text-align:left;"> county </th>
   <th style="text-align:left;"> supervisor </th>
   <th style="text-align:left;"> start_date </th>
   <th style="text-align:right;"> salary </th>
   <th style="text-align:right;"> benefits </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Anytown </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:left;"> Jones </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> NA </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Bumblyburg </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:left;"> Baker </td>
   <td style="text-align:left;"> NA </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> NA </td>
  </tr>
</tbody>
</table>

---
## Datenexport

### Export aller Daten

* Export der Daten mit RPostgreSQL:

* Erzeugung eines R-Objekts

```r
data_postgres <- dbGetQuery(con, "SELECT * FROM us_counties_2010")
```

* Speicherung des R-Objekts

```r
write.table(
  data_postgres, file='/tmp/us_counties_export.txt', 
  col.names = TRUE, sep = "," , fileEncoding="UTF-8")
```

---

class: center, middle

## Vielen Dank!

[Prof. Dr. Jan Kirenz](https://www.kirenz.com)

HdM Stuttgart  
Nobelstraße 10  
70569 Stuttgart

![](https://upload.wikimedia.org/wikipedia/commons/5/5e/HdM_Logo.svg)