Repository: mie-lab/trackintel (python)

Issue:
trackintel-postgis connector fails when using mixed timezones
reading staypoints from a postgis database with timestamps with mixed timezones results in the following error when using `assert spts.as_staypoints`: 
```
  File "C:\Users\henry\.conda\envs\graph_rep\lib\site-packages\trackintel\model\staypoints.py", line 60, in _validate
    assert pd.api.types.is_datetime64tz_dtype(
AssertionError: dtype of started_at is object but has to be tz aware datetime64
```
This seems to be a pandas-posgresql problem where we can not do much about it but we can solve the problem by transforming data to UTC after reading it from the database.
```python
for col in ['started_at', 'finished_at']:
     spts[col] = pd.to_datetime(spts[col], utc=True)
```
I have ran some quick tests and what it does is that transforms the timezone to UTC without losing the time information (e.g., 13:33+02 -->11:33+00) however I am not sure what happens if no tz is provided

Pull request description:
BUG: Fix reading multiple timezones from postgis 
closes #249 



Patch:
diff --git a/README.md b/README.md
index b1f8b60..25d3f4b 100644
--- a/README.md
+++ b/README.md
@@ -116,6 +116,7 @@ ti.print_version()
 * tqdm
 * OSMnx
 * similaritymeasures
+* pygeos
 
 ## Development
 You can find the development roadmap under `ROADMAP.md` and further development guidelines under `CONTRIBUTING.md`.
diff --git a/docs/conf.py b/docs/conf.py
index 5946bc4..8ec6909 100644
--- a/docs/conf.py
+++ b/docs/conf.py
@@ -50,6 +50,7 @@ autodoc_mock_imports = [
     "geoalchemy2",
     "tqdm",
     "similaritymeasures",
+    "pygeos",
 ]
 
 
diff --git a/docs/environment.yml b/docs/environment.yml
index 1392606..9957364 100644
--- a/docs/environment.yml
+++ b/docs/environment.yml
@@ -19,3 +19,4 @@ dependencies:
   - sphinx_rtd_theme
   - tqdm
   - similaritymeasures
+  - pygeos
diff --git a/docs/modules/io.rst b/docs/modules/io.rst
index 09083d7..0c2d291 100644
--- a/docs/modules/io.rst
+++ b/docs/modules/io.rst
@@ -61,6 +61,8 @@ PostGIS Import
 
 .. autofunction:: trackintel.io.postgis.read_trips_postgis
 
+.. autofunction:: trackintel.io.postgis.read_tours_postgis
+
 CSV File Export
 ===============
 
@@ -89,6 +91,8 @@ PostGIS Export
 
 .. autofunction:: trackintel.io.postgis.write_trips_postgis
 
+.. autofunction:: trackintel.io.postgis.write_tours_postgis
+
 Predefined dataset readers
 ==========================
 We also provide functionality to parse well-known datasets directly into the trackintel framework.
diff --git a/environment.yml b/environment.yml
index 775c6bd..e13e8f7 100644
--- a/environment.yml
+++ b/environment.yml
@@ -17,3 +17,4 @@ dependencies:
 - psycopg2
 - tqdm
 - similaritymeasures
+- pygeos
diff --git a/requirements.txt b/requirements.txt
index 3297999..fe00f6d 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -9,4 +9,5 @@ geoalchemy2
 osmnx 
 psycopg2
 tqdm
-similaritymeasures
\ No newline at end of file
+similaritymeasures
+pygeos
\ No newline at end of file
diff --git a/setup.py b/setup.py
index bc20126..98a920f 100644
--- a/setup.py
+++ b/setup.py
@@ -33,6 +33,7 @@ REQUIRED = [
     "scikit-learn",
     "tqdm",
     "similaritymeasures",
+    "pygeos",
 ]
 
 install_requires = [
@@ -48,6 +49,7 @@ install_requires = [
     "tqdm",
     "geopandas",
     "similaritymeasures",
+    "pygeos",
 ]
 
 # What packages are optional?
diff --git a/trackintel/io/__init__.py b/trackintel/io/__init__.py
index bc8260f..540233e 100644
--- a/trackintel/io/__init__.py
+++ b/trackintel/io/__init__.py
@@ -30,6 +30,9 @@ from .from_geopandas import read_trips_gpd
 
 from .file import read_tours_csv
 from .file import write_tours_csv
+from .postgis import read_tours_postgis
+from .postgis import write_tours_postgis
+from .from_geopandas import read_tours_gpd
 
 from .dataset_reader import read_geolife
 from .dataset_reader import geolife_add_modes_to_triplegs
diff --git a/trackintel/io/dataset_reader.py b/trackintel/io/dataset_reader.py
index 7a9b826..ebb3f2e 100644
--- a/trackintel/io/dataset_reader.py
+++ b/trackintel/io/dataset_reader.py
@@ -1,8 +1,9 @@
 # -*- coding: utf-8 -*-
 
 import glob
-import ntpath
 import os
+from collections import defaultdict
+from functools import partial
 
 import geopandas as gpd
 import numpy as np
@@ -11,12 +12,11 @@ from shapely.geometry import Point
 from sklearn.neighbors import NearestNeighbors
 from tqdm import tqdm
 
-FEET2METER = 0.3048
+from trackintel.preprocessing.util import calc_temp_overlap
 
+FEET2METER = 0.3048
 CRS_WGS84 = "epsg:4326"
 
-from trackintel.preprocessing.util import calc_temp_overlap
-
 
 def read_geolife(geolife_path, print_progress=False):
     """
@@ -27,7 +27,7 @@ def read_geolife(geolife_path, print_progress=False):
     Parameters
     ----------
     geolife_path: str
-        path to the directory with the geolife data
+        Path to the directory with the geolife data
 
     print_progress: Bool, default False
         Show per-user progress if set to True.
@@ -42,9 +42,8 @@ def read_geolife(geolife_path, print_progress=False):
 
     Notes
     -----
-    The geopandas dataframe has the following columns and datatype: 'latitude': float64, Latitude WGS84;
-    'longitude': float64, Longitude WGS84; 'elevation': float64, in meters; 'tracked_at': datetime64[ns];
-    'user_id': int64; 'geom': geopandas/shapely geometry; 'accuracy': None;
+    The geopandas dataframe has the following columns and datatype: 'elevation': float64 (in meters); 'tracked_at': datetime64[ns];
+    'user_id': int64; 'geom': shapely geometry; 'accuracy': None;
 
     For some users, travel mode labels are provided as .txt file. These labels are read and returned as label dictionary.
     The label dictionary contains the user ids as keys and DataFrames with the available labels as values.
@@ -93,80 +92,105 @@ def read_geolife(geolife_path, print_progress=False):
     >>> from trackintel.io.dataset_reader import read_geolife
     >>> pfs, mode_labels = read_geolife(os.path.join('downloads', 'Geolife Trajectories 1.3'))
     """
-    geolife_path = os.path.join(geolife_path, "*")
-    user_folder = sorted(glob.glob(geolife_path))
-
-    df_list_users = []
-    label_dict = dict()
-
-    if len(user_folder) == 0:
-        raise NameError("No folders found with working directory {} and path {}".format(os.getcwd(), geolife_path))
-
-    for user_folder_this in tqdm(user_folder, disable=not print_progress):
+    # u are strings in the format "052", "002".
+    uids = [u for u in os.listdir(geolife_path) if os.path.isdir(os.path.join(geolife_path, u))]
 
-        # skip files
-        if not os.path.isdir(user_folder_this):
-            continue
+    if len(uids) == 0:
+        raise FileNotFoundError("No user folders found at path {}".format(geolife_path))
 
-        # check if labels are available
-        try:
-            labels = pd.read_csv(os.path.join(user_folder_this, "labels.txt"), delimiter="\t")
-            rename_dict = {"Start Time": "started_at", "End Time": "finished_at", "Transportation Mode": "mode"}
-            labels.rename(rename_dict, axis=1, inplace=True)
-            labels["started_at"] = pd.to_datetime(labels["started_at"], format="%Y/%m/%d %H:%M:%S", utc=True)
-            labels["finished_at"] = pd.to_datetime(labels["finished_at"], format="%Y/%m/%d %H:%M:%S", utc=True)
-        except OSError:
-            labels = pd.DataFrame(columns=["started_at", "finished_at", "mode"])
-
-        # extract user id from path
-        _, tail = ntpath.split(user_folder_this)
+    for user_id in uids:
         try:
-            user_id = int(tail)
+            int(user_id)
         except ValueError as err:
             errmsg = (
                 "Invalid user_id '{}' found in geolife path '{}'. The geolife path can only contain folders"
-                " named with integers that represent the user id.".format(tail, user_folder_this)
+                " named with integers that represent the user id.".format(user_id, os.path.join(geolife_path, user_id))
             )
             raise ValueError(errmsg) from err
 
-        input_files = sorted(glob.glob(os.path.join(user_folder_this, "Trajectory", "*.plt")))
-        df_list_days = []
+    labels = _get_labels(geolife_path, uids)
+    # get the dfs in form of an generator and concatinate them
+    gdf = pd.concat(_get_df(geolife_path, uids, print_progress), axis=0, ignore_index=True)
+    gdf = gpd.GeoDataFrame(gdf, geometry="geom", crs=CRS_WGS84)
+    gdf["accuracy"] = np.nan
+    gdf.index.name = "id"
+    return gdf, labels
+
 
-        # read every day of every user and concatenate input files
-        for input_file_this in input_files:
-            data_this = pd.read_csv(
-                input_file_this,
-                skiprows=6,
-                header=None,
-                names=["latitude", "longitude", "zeros", "elevation", "date days", "date", "time"],
-            )
+def _get_labels(geolife_path, uids):
+    """Generate dictionary with the available mode labels.
 
-            data_this["tracked_at"] = pd.to_datetime(
-                data_this["date"] + " " + data_this["time"], format="%Y-%m-%d %H:%M:%S", utc=True
-            )
+    Parameters
+    ----------
+    geolife_path : str
+        Path to the directory with the geolife data.
+    uids : iterable
+        User folders in the geolife data directory.
 
-            data_this.drop(["zeros", "date days", "date", "time"], axis=1, inplace=True)
-            data_this["user_id"] = user_id
-            data_this["elevation"] = data_this["elevation"] * FEET2METER
+    Returns
+    -------
+    dict
+        dict containing the mode labels with the uids in the keys.
 
-            data_this["geom"] = list(zip(data_this["longitude"], data_this["latitude"]))
-            data_this["geom"] = data_this["geom"].apply(Point)
+    Notes
+    -----
+    No further checks are done on user ids, they must be convertable to ints.
+    """
+    labels_rename = {"Start Time": "started_at", "End Time": "finished_at", "Transportation Mode": "mode"}
+    label_dict = defaultdict(
+        partial(pd.DataFrame, columns=["started_at", "finished_at", "mode"])
+    )  # output dict for the labels
+    # TODO: change geolife_add_modes_to_triplegs so that we can use a dict instead.
 
-            df_list_days.append(data_this)
+    # get paths to all "labels.txt" files.
+    possible_label_paths = ((os.path.join(geolife_path, user_id, "labels.txt"), user_id) for user_id in uids)
+    label_paths = ((path, user_id) for path, user_id in possible_label_paths if os.path.exists(path))
 
-        # concat all days of a user into a single dataframe
-        df_user_this = pd.concat(df_list_days, axis=0, ignore_index=True)
-        label_dict[user_id] = labels
+    # insert all labels into the output dict
+    for path, user_id in label_paths:
+        labels = pd.read_csv(path, delimiter="\t")
+        labels.rename(columns=labels_rename, inplace=True)
+        labels["started_at"] = pd.to_datetime(labels["started_at"], format="%Y/%m/%d %H:%M:%S", utc=True)
+        labels["finished_at"] = pd.to_datetime(labels["finished_at"], format="%Y/%m/%d %H:%M:%S", utc=True)
+        label_dict[int(user_id)] = labels
+    return label_dict
 
-        df_list_users.append(df_user_this)
 
-    df = pd.concat(df_list_users, axis=0, ignore_index=True)
-    gdf = gpd.GeoDataFrame(df, geometry="geom", crs=CRS_WGS84)
-    gdf["accuracy"] = np.nan
+def _get_df(geolife_path, uids, print_progress):
+    """Create a generator that yields single trajectory dataframes.
 
-    gdf.index.name = "id"
+    Parameters
+    ----------
+    geolife_path : str
+        Path to the directory with the geolife data.
+    uids : iterable
+        User folders in the geolife data directory.
+    print_progress : bool
+        Show per-user progress if set to True.
+
+    Yields
+    -------
+    pd.DataFrame
+        A single DataFrame from a single trajectory file.
 
-    return gdf, label_dict
+    Notes
+    -----
+    No further checks are done on user ids, they must be convertable to ints.
+    """
+    disable = not print_progress
+    names = ["latitude", "longitude", "zeros", "elevation", "date days", "date", "time"]
+    usecols = ["latitude", "longitude", "elevation", "date", "time"]
+
+    for user_id in tqdm(uids, disable=disable):
+        pattern = os.path.join(geolife_path, user_id, "Trajectory", "*.plt")
+        for traj_file in glob.glob(pattern):
+            data = pd.read_csv(traj_file, skiprows=6, header=None, names=names, usecols=usecols)
+            data["tracked_at"] = pd.to_datetime(data["date"] + " " + data["time"], format="%Y-%m-%d %H:%M:%S", utc=True)
+            data["geom"] = gpd.points_from_xy(data["longitude"], data["latitude"])
+            data["user_id"] = int(user_id)
+            data["elevation"] = data["elevation"] * FEET2METER
+            data.drop(columns=["date", "time", "longitude", "latitude"], inplace=True)
+            yield data
 
 
 def geolife_add_modes_to_triplegs(
diff --git a/trackintel/io/from_geopandas.py b/trackintel/io/from_geopandas.py
index 5e101c1..1bb34a1 100644
--- a/trackintel/io/from_geopandas.py
+++ b/trackintel/io/from_geopandas.py
@@ -401,7 +401,12 @@ def _trackintel_model(gdf, set_names=None, geom_col=None, crs=None, tz_cols=None
     if tz_cols is not None:
         for col in tz_cols:
             if not pd.api.types.is_datetime64tz_dtype(gdf[col]):
-                gdf[col] = _localize_timestamp(dt_series=gdf[col], pytz_tzinfo=tz, col_name=col)
+                try:
+                    gdf[col] = _localize_timestamp(dt_series=gdf[col], pytz_tzinfo=tz, col_name=col)
+                except ValueError:
+                    # Taken if column contains datetimes with different timezone informations.
+                    # Cast them to UTC in this case.
+                    gdf[col] = pd.to_datetime(gdf[col], utc=True)
 
     # If is not GeoDataFrame and no geom_col is set end early.
     # That allows us to handle DataFrames and GeoDataFrames in one function.
diff --git a/trackintel/io/postgis.py b/trackintel/io/postgis.py
index 7e2a9de..0746889 100644
--- a/trackintel/io/postgis.py
+++ b/trackintel/io/postgis.py
@@ -579,6 +579,118 @@ def write_trips_postgis(
         )
 
 
+@_handle_con_string
+def read_tours_postgis(
+    sql,
+    con,
+    geom_col=None,
+    crs=None,
+    index_col=None,
+    coerce_float=True,
+    parse_dates=None,
+    params=None,
+    chunksize=None,
+    **kwargs
+):
+    """Read tours from a PostGIS database.
+
+    Parameters
+    ----------
+    sql : str
+        SQL query e.g. "SELECT * FROM tours"
+
+    con : str, sqlalchemy.engine.Connection or sqlalchemy.engine.Engine
+        Connection string or active connection to PostGIS database.
+
+    geom_col : str, optional
+        The geometry column of the table (if exists).
+
+    crs : optional
+        Coordinate reference system if table has geometry.
+
+    index_col : string or list of strings, optional
+        Column(s) to set as index(MultiIndex)
+
+    coerce_float : boolean, default True
+        Attempt to convert values of non-string, non-numeric objects (like
+        decimal.Decimal) to floating point, useful for SQL result sets
+
+    parse_dates : list or dict, default None
+        - List of column names to parse as dates.
+        - Dict of ``{column_name: format string}`` where format string is
+            strftime compatible in case of parsing string times, or is one of
+            (D, s, ns, ms, us) in case of parsing integer timestamps.
+        - Dict of ``{column_name: arg dict}``, where the arg dict
+            corresponds to the keyword arguments of
+            :func:`pandas.to_datetime`. Especially useful with databases
+            without native Datetime support, such as SQLite.
+
+    params : list, tuple or dict, optional, default None
+        List of parameters to pass to execute method.
+
+    chunksize : int, default None
+        If specified, return an iterator where chunksize is the number
+        of rows to include in each chunk.
+
+    **kwargs
+        Further keyword arguments as available in trackintels trackintel.io.read_tours_gpd().
+        Especially useful to rename column names from the SQL table to trackintel conform column names.
+        See second example how to use it in code.
+
+    Returns
+    -------
+    GeoDataFrame (as trackintel tours)
+        A GeoDataFrame containing the tours.
+
+    Examples
+    --------
+    >>> tours = ti.io.read_tours_postgis("SELECT * FROM tours", con)
+    >>> tours = ti.io.read_tours_postgis("SELECT * FROM tours", con, index_col="id", started_at="start_time",
+    ...                                  finished_at="end_time", user_id="USER")
+    """
+    if geom_col is None:
+        tours = pd.read_sql(
+            sql,
+            con,
+            index_col=index_col,
+            coerce_float=coerce_float,
+            params=params,
+            parse_dates=parse_dates,
+            chunksize=chunksize,
+        )
+    else:
+        tours = gpd.GeoDataFrame.from_postgis(
+            sql,
+            con,
+            geom_col=geom_col,
+            crs=crs,
+            index_col=index_col,
+            coerce_float=coerce_float,
+            parse_dates=parse_dates,
+            params=params,
+            chunksize=chunksize,
+        )
+
+    return ti.io.read_tours_gpd(tours, **kwargs)
+
+
+@_handle_con_string
+def write_tours_postgis(
+    tours, name, con, schema=None, if_exists="fail", index=True, index_label=None, chunksize=None, dtype=None
+):
+    write_trips_postgis(
+        tours,
+        name=name,
+        con=con,
+        schema=schema,
+        if_exists=if_exists,
+        index=index,
+        index_label=index_label,
+        chunksize=chunksize,
+        dtype=dtype,
+    )
+
+
 # helper docstring to change __doc__ of all write functions conveniently in one place
 __doc = """Stores {long} to PostGIS. Usually, this is directly called on a {long}
     DataFrame (see example below).
@@ -628,3 +740,4 @@ write_triplegs_postgis.__doc__ = __doc.format(long="triplegs", short="tpls")
 write_staypoints_postgis.__doc__ = __doc.format(long="staypoints", short="sp")
 write_locations_postgis.__doc__ = __doc.format(long="locations", short="locs")
 write_trips_postgis.__doc__ = __doc.format(long="trips", short="trips")
+write_tours_postgis.__doc__ = __doc.format(long="tours", short="tours")
diff --git a/trackintel/model/tours.py b/trackintel/model/tours.py
index bc11abd..f36eabc 100644
--- a/trackintel/model/tours.py
+++ b/trackintel/model/tours.py
@@ -57,6 +57,16 @@ class ToursAccessor(object):
         """
         ti.io.file.write_tours_csv(self._obj, filename, *args, **kwargs)
 
+    def to_postgis(
+        self, name, con, schema=None, if_exists="fail", index=True, index_label=None, chunksize=None, dtype=None
+    ):
+        """
+        Store this collection of tours to PostGIS.
+
+        See :func:`trackintel.io.postgis.write_tours_postgis`.
+        """
+        ti.io.postgis.write_tours_postgis(self._obj, name, con, schema, if_exists, index, index_label, chunksize, dtype)
+
     def plot(self, *args, **kwargs):
         """
         Plot this collection of tours.


Test patch:
diff --git a/ci/envs/37-latest-conda-forge.yaml b/ci/envs/37-latest-conda-forge.yaml
index 016279d..5a64500 100644
--- a/ci/envs/37-latest-conda-forge.yaml
+++ b/ci/envs/37-latest-conda-forge.yaml
@@ -20,4 +20,5 @@ dependencies:
 - pytest-xdist
 - tqdm
 - similaritymeasures
+- pygeos
 - jupyter
diff --git a/ci/envs/38-latest-conda-forge.yaml b/ci/envs/38-latest-conda-forge.yaml
index 40c036e..e3896e5 100644
--- a/ci/envs/38-latest-conda-forge.yaml
+++ b/ci/envs/38-latest-conda-forge.yaml
@@ -20,4 +20,5 @@ dependencies:
 - pytest-xdist
 - tqdm
 - similaritymeasures
+- pygeos
 - jupyter
diff --git a/ci/envs/39-latest-conda-forge.yaml b/ci/envs/39-latest-conda-forge.yaml
index 814ac7c..488da4e 100644
--- a/ci/envs/39-latest-conda-forge.yaml
+++ b/ci/envs/39-latest-conda-forge.yaml
@@ -20,4 +20,5 @@ dependencies:
 - pytest-xdist
 - tqdm
 - similaritymeasures
+- pygeos
 - jupyter
diff --git a/tests/io/test_dataset_reader.py b/tests/io/test_dataset_reader.py
index 1a99221..165fdce 100644
--- a/tests/io/test_dataset_reader.py
+++ b/tests/io/test_dataset_reader.py
@@ -1,14 +1,14 @@
 import datetime
 import os
 
-import numpy as np
 import pandas as pd
 import pytest
-
-from geopandas.testing import assert_geoseries_equal
+from geopandas.testing import assert_geodataframe_equal
+from pandas.testing import assert_frame_equal
+from shapely.geometry import Point
 
 import trackintel as ti
-from trackintel.io.dataset_reader import read_geolife, geolife_add_modes_to_triplegs
+from trackintel.io.dataset_reader import _get_df, _get_labels, geolife_add_modes_to_triplegs, read_geolife
 
 
 @pytest.fixture
@@ -88,7 +88,7 @@ class TestReadGeolife:
         pfs_reRead = ti.read_positionfixes_csv(saved_file, index_col="id", crs="epsg:4326")
         os.remove(saved_file)
 
-        assert_geoseries_equal(pfs.geometry, pfs_reRead.geometry)
+        assert_geodataframe_equal(pfs, pfs_reRead, check_like=True)
 
     def test_print_progress_flag(self, capsys):
         """Test if the print_progress bar controls the printing behavior."""
@@ -114,17 +114,6 @@ class TestReadGeolife:
             assert key in [10, 20, 178]
             assert isinstance(value, pd.DataFrame)
 
-    def test_unavailable_label_reading(self):
-        """Test data types of the labels returned by read_geolife from a dictionary without label files."""
-        _, labels = read_geolife(os.path.join("tests", "data", "geolife_long"))
-
-        # the output is a dictionary
-        assert isinstance(labels, dict)
-
-        # the values are pandas dataframes
-        for key, value in labels.items():
-            assert isinstance(value, pd.DataFrame)
-
     def test_wrong_folder_name(self):
         """Check if invalid folder names raise an exception."""
         geolife_path = os.path.join("tests", "data", "geolife")
@@ -133,10 +122,56 @@ class TestReadGeolife:
 
         try:
             with pytest.raises(ValueError):
-                _, _ = read_geolife(geolife_path)
+                read_geolife(geolife_path)
         finally:
             os.rmdir(temp_dir)
 
+    def test_no_user_folders(self):
+        """Check if no user folders raise an exception."""
+        geolife_path = os.path.join("tests", "data", "geolife", "000", "Trajectory")
+        with pytest.raises(FileNotFoundError):
+            read_geolife(geolife_path)
+
+
+class Test_GetLabels:
+    def test_example_data(self):
+        """Read example data and test if it is valid."""
+        geolife_path = os.path.join("tests", "data", "geolife_modes")
+        uids = ["010", "020", "178"]
+        labels = _get_labels(geolife_path, uids)
+
+        assert len(labels) == 2
+        assert all(key in [10, 20] for key in labels.keys())
+        assert len(labels[10]) == 434
+        assert len(labels[20]) == 223
+        assert all(df.columns.tolist() == ["started_at", "finished_at", "mode"] for df in labels.values())
+
+    def test_defaultdict(self):
+        """Test if non existing entries return a correct empty pd.DataFrame"""
+        geolife_path = os.path.join("tests", "data", "geolife_modes")
+        uids = ["010", "020", "178"]
+        labels = _get_labels(geolife_path, uids)
+        empty_df = pd.DataFrame(columns=["started_at", "finished_at", "mode"])
+        assert_frame_equal(labels[-1], empty_df)
+
+
+class Test_GetDf:
+    def test_example_data(self):
+        """Read example data and test if it is valid."""
+        geolife_path = os.path.join("tests", "data", "geolife_modes")
+        uids = ["010", "020", "178"]
+        df_gen = _get_df(geolife_path, uids, False)
+
+        s = 0
+        df_lengths = [681, 818, 915, 1004, 66, 327, 256, 66, 84]
+        columns = ["elevation", "tracked_at", "geom", "user_id"]
+        for df in df_gen:
+            assert len(df) in df_lengths
+            assert df.columns.tolist() == columns
+            assert isinstance(df["geom"][0], Point)
+            s += 1
+        assert s == len(df_lengths)
+
 
 class TestGeolife_add_modes_to_triplegs:
     def test_duplicate_matching(self, matching_data):
diff --git a/tests/io/test_from_geopandas.py b/tests/io/test_from_geopandas.py
index c04a724..3dc0edd 100644
--- a/tests/io/test_from_geopandas.py
+++ b/tests/io/test_from_geopandas.py
@@ -91,6 +91,17 @@ class Test_Trackintel_Model:
         pfs = _trackintel_model(pfs, tz_cols=["tracked_at"], tz="UTC")
         assert_geodataframe_equal(pfs, example_positionfixes)
 
+    def test_multiple_timezones_in_col(self, example_positionfixes):
+        """Test if datetimes in column don't have the same timezone get casted to UTC."""
+        example_positionfixes["tracked_at"] = [
+            pd.Timestamp("2021-08-01 16:00:00", tz="Europe/Amsterdam"),
+            pd.Timestamp("2021-08-01 16:00:00", tz="Asia/Muscat"),
+            pd.Timestamp("2021-08-01 16:00:00", tz="Pacific/Niue"),
+        ]
+        pfs = _trackintel_model(example_positionfixes, tz_cols=["tracked_at"])
+        example_positionfixes["tracked_at"] = pd.to_datetime(example_positionfixes["tracked_at"], utc=True)
+        assert_geodataframe_equal(pfs, example_positionfixes)
+
 
 class TestRead_Positionfixes_Gpd:
     """Test `read_positionfixes_gpd()` function."""
@@ -296,6 +307,17 @@ class TestRead_Trips_Gpd:
         example_trips.rename(columns=mapper, inplace=True)
         assert_geodataframe_equal(trips, example_trips)
 
+    def test_multiple_timezones_in_col(self, example_trips):
+        """Test if datetimes in column don't have the same timezone get casted to UTC."""
+        example_trips["started_at"] = [
+            pd.Timestamp("2021-08-01 16:00:00", tz="Europe/Amsterdam"),
+            pd.Timestamp("2021-08-01 16:00:00", tz="Asia/Muscat"),
+            pd.Timestamp("2021-08-01 16:00:00", tz="Pacific/Niue"),
+        ]
+        trips = read_trips_gpd(example_trips)
+        example_trips["started_at"] = pd.to_datetime(example_trips["started_at"], utc=True)
+        assert_geodataframe_equal(example_trips, trips)
+
 
 @pytest.fixture
 def example_tours():
diff --git a/tests/io/test_postgis.py b/tests/io/test_postgis.py
index 6b9f03a..3791d46 100644
--- a/tests/io/test_postgis.py
+++ b/tests/io/test_postgis.py
@@ -160,6 +160,25 @@ def example_trips():
     return trips
 
 
+@pytest.fixture
+def example_tours():
+    """Tours to load into the database."""
+    t1 = pd.Timestamp("1971-01-01 00:00:00", tz="utc")
+    t2 = pd.Timestamp("1971-01-01 05:00:00", tz="utc")
+    t3 = pd.Timestamp("1971-01-02 07:00:00", tz="utc")
+    h = datetime.timedelta(hours=1)
+
+    list_dict = [
+        {"user_id": 0, "started_at": t1, "finished_at": t1 + h},
+        {"user_id": 0, "started_at": t2, "finished_at": t2 + h},
+        {"user_id": 1, "started_at": t3, "finished_at": t3 + h},
+    ]
+    tours = pd.DataFrame(data=list_dict)
+    tours.index.name = "id"
+    assert tours.as_tours
+    return tours
+
+
 def del_table(con, table):
     """Delete table in con."""
     try:
@@ -291,6 +310,28 @@ class TestPositionfixes:
         finally:
             del_table(conn, table)
 
+    def test_daylight_saving_tz(self, example_positionfixes, conn_postgis):
+        """Test if function can handle different tz informations in one column.
+
+        PostgreSQL saves all its datetimes in UTC and then on exports them to the local timezone.
+        That all works fine except when the local timezone changed in the past for example with daylight saving.
+        """
+        pfs = example_positionfixes.copy()
+        conn_string, conn = conn_postgis
+        table = "positionfixes"
+        sql = f"SELECT * FROM {table}"
+        t1 = pd.Timestamp("2021-08-01 16:00:00", tz="utc")  # summer time
+        t2 = pd.Timestamp("2021-08-01 15:00:00", tz="utc")  # summer time
+        t3 = pd.Timestamp("2021-02-01 14:00:00", tz="utc")  # winter time
+        pfs["tracked_at"] = [t1, t2, t3]
+        geom_col = pfs.geometry.name
+        try:
+            pfs.as_positionfixes.to_postgis(table, conn_string)
+            pfs_db = ti.io.read_positionfixes_postgis(sql, conn_string, geom_col, index_col="id")
+            assert_geodataframe_equal(pfs, pfs_db)
+        finally:
+            del_table(conn, table)
+
 
 class TestTriplegs:
     def test_write(self, example_triplegs, conn_postgis):
@@ -582,6 +623,51 @@ class TestTrips:
             del_table(conn, table)
 
 
+class TestTours:
+    """Test of postgis functions for tours."""
+
+    def test_write(self, example_tours, conn_postgis):
+        """Test if write of tours create correct schema in database."""
+        tours = example_tours
+        conn_string, conn = conn_postgis
+        table = "tours"
+        try:
+            tours.as_tours.to_postgis(table, conn_string)
+            columns_db, dtypes = get_table_schema(conn, table)
+            columns = tours.columns.tolist() + [tours.index.name]
+            assert len(columns_db) == len(columns)
+            assert set(columns_db) == set(columns)
+        finally:
+            del_table(conn, table)
+
+    def test_read(self, example_tours, conn_postgis):
+        """Test if tours written to and read back from database are the same."""
+        tours = example_tours
+        conn_string, conn = conn_postgis
+        table = "tours"
+        sql = f"SELECT * FROM {table}"
+
+        try:
+            tours.as_tours.to_postgis(table, conn_string)
+            tours_db = ti.io.read_tours_postgis(sql, conn_string, index_col="id")
+            assert_frame_equal(tours, tours_db)
+        finally:
+            del_table(conn, table)
+
+    def test_no_crs(self, example_tours, conn_postgis):
+        """Test if writing reading to postgis also works correctly without CRS."""
+        tours = example_tours
+        conn_string, conn = conn_postgis
+        table = "tours"
+        sql = f"SELECT * FROM {table}"
+        try:
+            tours.as_tours.to_postgis(table, conn_string)
+            tours_db = ti.io.read_tours_postgis(sql, conn_string, index_col="id")
+            assert_frame_equal(tours, tours_db)
+        finally:
+            del_table(conn, table)
+
+
 class TestGetSrid:
     def test_srid(self, example_positionfixes):
         """Test if `_get_srid` returns the correct srid."""
diff --git a/tests/preprocessing/test_positionfixes.py b/tests/preprocessing/test_positionfixes.py
index 95d75ff..df9af75 100644
--- a/tests/preprocessing/test_positionfixes.py
+++ b/tests/preprocessing/test_positionfixes.py
@@ -172,17 +172,21 @@ class TestGenerate_staypoints:
     def test_sliding_max(self):
         """Test if using large thresholds, stp extraction yield no pfs."""
         pfs, _ = ti.io.dataset_reader.read_geolife(os.path.join("tests", "data", "geolife"))
-        _, sp = pfs.as_positionfixes.generate_staypoints(
-            method="sliding", dist_threshold=sys.maxsize, time_threshold=sys.maxsize
-        )
+        warn_string = "No staypoints can be generated, returning empty sp."
+        with pytest.warns(UserWarning, match=warn_string):
+            _, sp = pfs.as_positionfixes.generate_staypoints(
+                method="sliding", dist_threshold=sys.maxsize, time_threshold=sys.maxsize
+            )
         assert len(sp) == 0
 
     def test_missing_link(self):
         """Test nan is assigned for missing link between pfs and sp."""
         pfs, _ = ti.io.dataset_reader.read_geolife(os.path.join("tests", "data", "geolife"))
-        pfs, _ = pfs.as_positionfixes.generate_staypoints(
-            method="sliding", dist_threshold=sys.maxsize, time_threshold=sys.maxsize
-        )
+        warn_string = "No staypoints can be generated, returning empty sp."
+        with pytest.warns(UserWarning, match=warn_string):
+            pfs, _ = pfs.as_positionfixes.generate_staypoints(
+                method="sliding", dist_threshold=sys.maxsize, time_threshold=sys.maxsize
+            )
 
         assert pd.isna(pfs["staypoint_id"]).any()
 
diff --git a/tests/preprocessing/test_staypoints.py b/tests/preprocessing/test_staypoints.py
index 10c4425..1cfab19 100644
--- a/tests/preprocessing/test_staypoints.py
+++ b/tests/preprocessing/test_staypoints.py
@@ -115,7 +115,7 @@ def example_triplegs_merge(example_staypoints_merge):
         {"id": 1, "user_id": 0, "started_at": t21, "finished_at": t22},
     ]
     # geometry is not required for the merge operation, so we leave it away
-    tpls = gpd.GeoDataFrame(data=list_dict, crs="EPSG:4326")
+    tpls = gpd.GeoDataFrame(data=list_dict)
     tpls = tpls.set_index("id")
     return sp, tpls
 
@@ -269,12 +269,14 @@ class TestGenerate_locations:
         _, sp = pfs.as_positionfixes.generate_staypoints(
             method="sliding", gap_threshold=1e6, dist_threshold=0, time_threshold=0
         )
-        _, locs_user = sp.as_staypoints.generate_locations(
-            method="dbscan", epsilon=1e18, num_samples=1000, agg_level="user"
-        )
-        _, locs_data = sp.as_staypoints.generate_locations(
-            method="dbscan", epsilon=1e18, num_samples=1000, agg_level="dataset"
-        )
+        warn_string = "No locations can be generated, returning empty locs."
+        with pytest.warns(UserWarning, match=warn_string):
+            _, locs_user = sp.as_staypoints.generate_locations(
+                method="dbscan", epsilon=1e18, num_samples=1000, agg_level="user"
+            )
+            _, locs_data = sp.as_staypoints.generate_locations(
+                method="dbscan", epsilon=1e18, num_samples=1000, agg_level="dataset"
+            )
         # "With large epsilon, every user location is an outlier"
         assert len(locs_user) == 0
         assert len(locs_data) == 0
@@ -286,7 +288,11 @@ class TestGenerate_locations:
         _, sp = pfs.as_positionfixes.generate_staypoints(
             method="sliding", gap_threshold=1e6, dist_threshold=0, time_threshold=0
         )
-        sp, _ = sp.as_staypoints.generate_locations(method="dbscan", epsilon=1e18, num_samples=1000, agg_level="user")
+        warn_string = "No locations can be generated, returning empty locs."
+        with pytest.warns(UserWarning, match=warn_string):
+            sp, _ = sp.as_staypoints.generate_locations(
+                method="dbscan", epsilon=1e18, num_samples=1000, agg_level="user"
+            )
 
         assert pd.isna(sp["location_id"]).any()