library(tidyverse)
library(sf)
library(sp)
library(geojsonsf)

EPSG_TRIPS=31468

trips <- read_delim('drt_trips_drt.csv.gz', delim=";")
geo <- st_read("berlin-plz.geojson")

trips_from <- trips %>%
  st_as_sf(coords=c("fromX", "fromY"), crs=EPSG_TRIPS) %>%
  st_transform(crs=4326)

trips_to <- trips %>%
  st_as_sf(coords=c("toX", "toY"), crs=EPSG_TRIPS) %>%
  st_transform(crs=4326)

# attach polygons to points
from_in_plz <- st_join(trips_from, geo, join=st_within)
# count points in polygons
from_count <- count(as_tibble(from_in_plz), plz) %>% rename(fromPLZ=n)

# attach polygons to points
to_in_plz <- st_join(trips_to, geo, join=st_within)
# count points in polygons
to_count <- count(as_tibble(to_in_plz), plz) %>% rename(toPLZ=n)

# merge from and to

plz_counts <- full_join(from_count, to_count)
print(plz_counts)