PostgreSQL Tabelle mit Tankstellendaten

Um die Kraftstoffpreise nicht nur im zeitlichen Verlauf zu analysieren, sondern auch nach verschiedenen Regionen oder Marken sind die Tankstelleninformationen notwendig. Tankerkönig stellt auch diese Daten in seinem git-Repository zur Verfügung.

Wie schon die Kraftstoffpreise liegen auch die Tankstellendaten als csv-Datei vor. Täglich wird eine neue csv-Datei veröffentlicht, welche alle aktuellen Tankstellen Daten enthält. Diese Daten können ebenfalls mit einem git pull täglich abgerufen werden.

Tabelle erstellen

Die csv-Dateien enthalten nicht nur die Namen der Tankstellen, sondern auch die Adresse (Straße, Hausnummer, Stadt, Postleitzahl), die geographische Position (Längen- und Breitengrad), Marke und das Datum der Eröffnung. Weiter werden auch die aktuellen Öffnungszeiten im JSON-Format hinterlegt, diese überspringe ich allerdings. Die Adressdaten sind leider nicht einheitlich formatiert und enthalten Tippfehler, dies gilt ebenso für Name und Marke. Die Längen- und Breitengrad Informationen sind hier am zuverlässigsten.

Um in späteren Analysen die Tankstellen Bundesländern, Kreisen und Gemeinden zuzuordnen ergänze ich die Tankstellendaten um Bundesland-, Kreis- und Gemeindekennziffer sowie Name der jeweiligen Region. Aus dieser Datenstruktur ergibt sich folgende PostgreSQL Tabelle:

CREATE TABLE stations (
  uuid			uuid					NOT NULL,	
  name			character varying(250)	NOT NULL,
  brand			character varying(150),				
  street		character varying(250),			
  house_number	character varying(150),			
  post_code		char(5),								
  city			character varying(150),	
  latitude		numeric(15,12)			NOT NULL,	
  longitude		numeric(15,12)			NOT NULL,	
  first_active	timestamptz,
  bl_kz			char(2),
  bl_name		character varying(150),
  kreis_kz      char(5),
  kreis_name    character varying(250),
  gemeinde_kz   char(12),
  gemeinde_name character varying(250)
);

Einlesen der Tankstellendaten mit R

Zur Verarbeitung der Daten benutze ich folgende R-Pakete

  • RPostgreSQL, um eine Verbindung zur Datenbank herzustellen
  • readr, zum einlesen der csv-Dateien
  • sp und rgdal, zur Verarbeitung der Geoinformationen
# Packages
library(RPostgreSQL)
library(readr)
library(sp)
library(rgdal)

Die aktuellsten Tankstelleninformationen können am einfachsten in Abhängigkeit des aktuellen Datums eingelesen werden, sofern man den letzten Datenstand heruntergeladen hat. Der Name der csv-Datei sowie der genaue Dateipfad lässt sich auf Basis des gestrigen Datums erstellen.

# File name and path
yesterday <- Sys.Date()-1
year <- format(yesterday,"%Y")
month <- format(yesterday,"%m")
day <- format(yesterday,"%d")
pathtoserver <- file.path("//192.168.1.10", "dbdata", "tankerkoenig", "stations", year, month)
filename <- paste(pathtoserver, "/", paste(year, month, day, sep = "-"), "-stations.csv", sep = "")

Anschließend kann die csv-Datei geladen werden und die Daten in einen Dataframe gespeichert werden.

# Stations data
stations <- read_csv(filename, 
                     col_types = cols(brand = col_character(), 
                                      city = col_character(), 
                                      first_active = col_character(),
                                      house_number = col_character(), 
                                      latitude = col_number(), 
                                      longitude = col_number(), 
                                      name = col_character(), 
                                      openingtimes_json = col_skip(), 
                                      post_code = col_character(), 
                                      street = col_character(),
                                      uuid = col_character()),
                     skip = 0,
                     na = c("nicht","Nicht"))

Die zusätzlichen Geoinformationen entnehme ich dem Shapefile der Verwaltungsgrenzen (WGS84), welcher als Begleitmaterial zum Zensus 2011 veröffentlicht wurde. Aktuellere Daten können beim Bundesamt für Kartographie und Geodäsie heruntergeladen werden.

Die Daten für Bundesländer, Kreise und Gemeinden werden einzeln in R geladen.

# Geo data
kreise <- readOGR(dsn="data/map_zensus2011", layer="VG250_Kreise", encoding = "UTF-8")
laender <- readOGR(dsn="data/map_zensus2011", layer="VG250_Bundeslaender", encoding = "UTF-8")
gemeinde <- readOGR(dsn="data/map_zensus2011", layer="VG250_Gemeinden", encoding = "UTF-8")

Zum abrufen der zusätzlichen Geoinformationen für jede Tankstelle nutze ich die kurze Funktion getinfo(), welche die benötigten Daten aus den Geodaten ausliest.

getinfo <- function(in.spat){
  out = vector(mode="character", length=6)
  out[1] = as.character(over(in.spat, laender)$RS)
  out[2] = as.character(over(in.spat, laender)$GEN)
  out[3] = as.character(over(in.spat, kreise)$RS)
  out[4] = as.character(over(in.spat, kreise)$GEN)
  out[5] = as.character(over(in.spat, gemeinde)$RS)
  out[6] = as.character(over(in.spat, gemeinde)$GEN)
  return(out)
}

Die zusätzlichen Informationen speichere ich in einzelnen Vektoren, welche anschließend mittels cbind() den bestehenden Tankstellendaten hinzugefügt werden.

# Initialize vectors
bl_kz <- vector(mode = "character", length = dim(stations)[1])
bl_name <- vector(mode = "character", length = dim(stations)[1])
kreis_kz <- vector(mode = "character", length = dim(stations)[1])
kreis_name <- vector(mode = "character", length = dim(stations)[1])
gemeinde_kz <- vector(mode = "character", length = dim(stations)[1])
gemeinde_name <- vector(mode = "character", length = dim(stations)[1])

Um das auslesen der Geoinformationen zu beschleunigen extrahiere ich zunächst die Längen- und Breitengrade der Tankstellen, um nicht bei jedem Zugriff den ganzen Dataframe mit allen Daten öffnen zu müssen. Die extrahierten Längen- und Breitengrade transformieren ich dann in ein Spatial-Objekt mit dem geodätischen Referenzsystem WGS84.

# Geodetic data
lat <- stations$latitude
lon <- stations$longitude
coords <- as.data.frame(cbind(lon,lat))
coords.spat <- SpatialPoints(coords)
proj4string(coords.spat) <- proj4string(kreise)

Anschließend kann für jede Tankstelle die benötigten Zusatzinformationen (Kennziffer und Name der Region) abgerufen und gespeichert werden. Hierbei nutze ich zudem einen Fortschrittsbalken, da der Prozess einige Minuten dauern kann.

# Get additonal geoinformation
progress <- txtProgressBar(min=1, max=dim(stations)[1], style = 3, width = 100)

for(i in 1:dim(stations)[1]){
  info <- getinfo(coords.spat[i,])
  bl_kz[i] <- info[1]
  bl_name[i] <- info[2]
  kreis_kz[i] <- info[3]
  kreis_name[i] <- info[4]
  gemeinde_kz[i] <- info[5]
  gemeinde_name[i] <- info[6]
  setTxtProgressBar(progress, i)
}

Mittels cbind() werden dann die Geoinformationen zu den bestehenden Tankstelleninformationen hinzugefügt.

stations <- cbind(stations, bl_kz, bl_name, kreis_kz, kreis_name, gemeinde_kz, gemeinde_name)

Abschließend können die Daten in die PostgreSQL Tabelle überführt werden, hierzu stellt man zunächst eine Verbindung zur Datenbank her und überträgt anschließend die Tankstelleninformationen.

# Connect to database
drv <- dbDriver("PostgreSQL") # DBDriver
dbn <- "fuelprice" # Database name
dbh <- "192.168.1.10" # DB Host
dbp <- 5432 # DB Port
dbu <- "dbuser" # DB User
dbpw <- "dbpassword" # DB Password

# Connection
dbcon <- dbConnect(drv = drv, dbname=dbn, host=dbh ,port=dbp, user=dbu, password=dbpw)

# Write to db
dbWriteTable(dbcon, c("stations"), value = stations, append = T, row.names = F)

# Disconnect
dbDisconnect(dbcon)

# Clear Working Space
rm(list=ls())

Der komplette Code

# Packages
library(RPostgreSQL)
library(readr)
library(sp)
library(rgdal)

# File name and path
yesterday <- Sys.Date()-1
year <- format(yesterday,"%Y")
month <- format(yesterday,"%m")
day <- format(yesterday,"%d")
pathtoserver <- file.path("//192.168.1.10", "dbdata", "tankerkoenig", "stations", year, month)
filename <- paste(pathtoserver, "/", paste(year, month, day, sep = "-"), "-stations.csv", sep = "")

# Stations data
stations <- read_csv(filename, 
                     col_types = cols(brand = col_character(), 
                                      city = col_character(), 
                                      first_active = col_character(),
                                      house_number = col_character(), 
                                      latitude = col_number(), 
                                      longitude = col_number(), 
                                      name = col_character(), 
                                      openingtimes_json = col_skip(), 
                                      post_code = col_character(), 
                                      street = col_character(),
                                      uuid = col_character()),
                     skip = 0,
                     na = c("nicht","Nicht"))

# Geo data
kreise <- readOGR(dsn="data/map_zensus2011", layer="VG250_Kreise", encoding = "UTF-8")
laender <- readOGR(dsn="data/map_zensus2011", layer="VG250_Bundeslaender", encoding = "UTF-8")
gemeinde <- readOGR(dsn="data/map_zensus2011", layer="VG250_Gemeinden", encoding = "UTF-8")

getinfo <- function(in.spat){
  out = vector(mode="character", length=6)
  out[1] = as.character(over(in.spat, laender)$RS)
  out[2] = as.character(over(in.spat, laender)$GEN)
  out[3] = as.character(over(in.spat, kreise)$RS)
  out[4] = as.character(over(in.spat, kreise)$GEN)
  out[5] = as.character(over(in.spat, gemeinde)$RS)
  out[6] = as.character(over(in.spat, gemeinde)$GEN)
  return(out)
}

# Initialize vectors
bl_kz <- vector(mode = "character", length = dim(stations)[1])
bl_name <- vector(mode = "character", length = dim(stations)[1])
kreis_kz <- vector(mode = "character", length = dim(stations)[1])
kreis_name <- vector(mode = "character", length = dim(stations)[1])
gemeinde_kz <- vector(mode = "character", length = dim(stations)[1])
gemeinde_name <- vector(mode = "character", length = dim(stations)[1])

# Geodetic data
lat <- stations$latitude
lon <- stations$longitude
coords <- as.data.frame(cbind(lon,lat))
coords.spat <- SpatialPoints(coords)
proj4string(coords.spat) <- proj4string(kreise)

# Get additonal geoinformation
progress <- txtProgressBar(min=1, max=dim(stations)[1], style = 3, width = 100)

for(i in 1:dim(stations)[1]){
  info <- getinfo(coords.spat[i,])
  bl_kz[i] <- info[1]
  bl_name[i] <- info[2]
  kreis_kz[i] <- info[3]
  kreis_name[i] <- info[4]
  gemeinde_kz[i] <- info[5]
  gemeinde_name[i] <- info[6]
  setTxtProgressBar(progress, i)
}

stations <- cbind(stations, bl_kz, bl_name, kreis_kz, kreis_name, gemeinde_kz, gemeinde_name)

# Connect to database
drv <- dbDriver("PostgreSQL") # DBDriver
dbn <- "fuelprice" # Database name
dbh <- "192.168.1.10" # DB Host
dbp <- 5432 # DB Port
dbu <- "dbuser" # DB User
dbpw <- "dbpassword" # DB Password

# Connection
dbcon <- dbConnect(drv = drv, dbname=dbn, host=dbh ,port=dbp, user=dbu, password=dbpw)

# Write to db
dbWriteTable(dbcon, c("stations"), value = stations, append = T, row.names = F)

# Disconnect
dbDisconnect(dbcon)

# Clear Working Space
rm(list=ls())

Veröffentlicht in Kraftstoffpreise und verschlagwortet mit , , .