Quickstart — MobilityDB SQLAlchemy 0.4 documentation
source link: https://mobilitydb-sqlalchemy.readthedocs.io/en/latest/quickstart.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Quickstart¶
from mobilitydb_sqlalchemy import TGeomPoint from sqlalchemy import Column, Integer from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Trips(Base): __tablename__ = "test_table_trips_01" car_id = Column(Integer, primary_key=True) trip_id = Column(Integer, primary_key=True) trip = Column(TGeomPoint) trips = session.query(Trips).all() # Querying using MobilityDB functions, for example - valueAtTimestamp session.query( Trips.car_id, func.asText( func.valueAtTimestamp(Trips.trip, datetime.datetime(2012, 1, 1, 8, 10, 0)) ), ).all()
Inserting temporal data¶
mobilitydb-sqlalchemy lets you use pandas DataFrame (which are great for timeseries data) while you are in the Python world, and translates it back and for to temporal types defined in mobilitydb.
A point to note here is that we assume that the DataFrame’s columns are named “value” (except in case of TGeomPoint where it is “geometry”) and “t” for the data and the timestamp respectively.
Here we show how we can store numeric data which changes over time (i.e. tfloat), using the mobilitydb_sqlalchemy.types.TFloat.TFloat
class.
Running the following code will create a new table with a tfloat column, and insert one row of hardcoded data into it.
import datetime import pandas as pd from mobilitydb_sqlalchemy import TFloat from sqlalchemy import Column, Integer, create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker # Setup the engine and session, make sure you set the right url to connect to your mobilitydb instance engine = create_engine("postgresql://docker:docker@localhost:25432/mobilitydb", echo=True) session = sessionmaker(bind=engine)() # Setup and create the tables (only one in our case here) Base = declarative_base() class TemporalFloats(Base): __tablename__ = "tfloat_test_001" id = Column(Integer, primary_key=True) tdata = Column(TFloat(True, False)) Base.metadata.create_all(engine) # Prepare and insert the data df = pd.DataFrame( [ {"value": 0, "t": datetime.datetime(2018, 1, 1, 12, 0, 0)}, {"value": 8.2, "t": datetime.datetime(2018, 1, 1, 12, 6, 0)}, {"value": 6.6, "t": datetime.datetime(2018, 1, 1, 12, 10, 0)}, {"value": 9.1, "t": datetime.datetime(2018, 1, 1, 12, 15, 0)}, ] ).set_index("t") row = TemporalFloats(tdata=df,) session.add(row) session.commit()
Inserting TGeomPoint data¶
While creating the DataFrame, make sure the column is named “geometry” and not “value”. This is to maintain compatibility with movingpandas. We can use Point objects from shapely for preparing the geometry data.
from mobilitydb_sqlalchemy import TGeomPoint from shapely.geometry import Point class Trips(Base): __tablename__ = "trips_test_001" car_id = Column(Integer, primary_key=True) trip_id = Column(Integer, primary_key=True) trip = Column(TGeomPoint) Base.metadata.create_all(engine) # Prepare and insert the data df = pd.DataFrame( [ {"geometry": Point(0, 0), "t": datetime.datetime(2012, 1, 1, 8, 0, 0),}, {"geometry": Point(2, 0), "t": datetime.datetime(2012, 1, 1, 8, 10, 0),}, {"geometry": Point(2, -1.9), "t": datetime.datetime(2012, 1, 1, 8, 15, 0),}, ] ).set_index("t") trip = Trips(car_id=1, trip_id=1, trip=df,) session.add(trip) session.commit()
Inserting TGeomPoint data, using movingpandas¶
movingpandas is an optional dependency, but if installed, you can insert TGeomPoint data with Trajectory objects directly. Just be sure to enable the flag use_movingpandas on the column beforehand.
from mobilitydb_sqlalchemy import TGeomPoint from shapely.geometry import Point from fiona.crs import from_epsg CRS_METRIC = from_epsg(31256) class Trips(Base): __tablename__ = "trips_test_001" car_id = Column(Integer, primary_key=True) trip_id = Column(Integer, primary_key=True) trip = Column(TGeomPoint(use_movingpandas=True)) Base.metadata.create_all(engine) # Prepare and insert the data df = pd.DataFrame( [ {"geometry": Point(0, 0), "t": datetime.datetime(2012, 1, 1, 8, 0, 0),}, {"geometry": Point(2, 0), "t": datetime.datetime(2012, 1, 1, 8, 10, 0),}, {"geometry": Point(2, -1.9), "t": datetime.datetime(2012, 1, 1, 8, 15, 0),}, ] ).set_index("t") geo_df = GeoDataFrame(df, crs=CRS_METRIC) traj = mpd.Trajectory(geo_df, 1) # Note: In case you are depending on movingpandas 0.1 or lower, # you might need to do mpd.Trajectory(1, geo_df) instead trip = Trips(car_id=1, trip_id=1, trip=traj,) session.add(trip) session.commit()
Using MobilityDB functions¶
SQLAlchemy’s func is pretty generic and flexible, allowing us to use MobilityDB’s functions without needing any new constructs.
Let’s take few example queries from MobilityDB’s documentation, and explain how we can achieve the same using this package.
-- Value at a given timestamp SELECT CarId, ST_AsText(valueAtTimestamp(Trip, timestamptz '2012-01-01 08:10:00')) FROM Trips; -- 10;"POINT(2 0)" -- 20;"POINT(1 1)" -- Restriction to a given value SELECT CarId, asText(atValue(Trip, 'Point(2 0)')) FROM Trips; -- 10;"{"[POINT(2 0)@2012-01-01 08:10:00+00]"}" -- 20; NULL -- Restriction to a period SELECT CarId, asText(atPeriod(Trip, '[2012-01-01 08:05:00,2012-01-01 08:10:00]')) FROM Trips; -- 10;"{[POINT(1 0)@2012-01-01 08:05:00+00, POINT(2 0)@2012-01-01 08:10:00+00]}" -- 20;"{[POINT(0 0)@2012-01-01 08:05:00+00, POINT(1 1)@2012-01-01 08:10:00+00]}" -- Temporal distance SELECT T1.CarId, T2.CarId, T1.Trip <-> T2.Trip FROM Trips T1, Trips T2 WHERE T1.CarId < T2.CarId; -- 10;20;"{[1@2012-01-01 08:05:00+00, 1.4142135623731@2012-01-01 08:10:00+00, 1@2012-01-01 08:15:00+00)}"
from sqlalchemy import func from shapely.wkt import loads # Value at a given timestamp session.query( Trips.car_id, func.asText( func.valueAtTimestamp(Trips.trip, datetime.datetime(2012, 1, 1, 8, 10, 0)) ), ).all() # Restriction to a given value session.query( Trip.car_id, func.asText(func.atValue(Trips.trip, Point(2, 0).wkt)), ).all() # Restriction to a period session.query( Trips.car_id, func.asText( func.atPeriod(Trips.trip, "[2012-01-01 08:05:00,2012-01-01 08:10:00]") ), ).all() # Temporal distance session.query( T1.c.car_id, T2.c.car_id, T1.c.trip.distance(T2.c.trip), ) \ .filter(T1.c.car_id < T2.c.car_id,) .all()
Using MobilityDB operators¶
session.query( T1.c.car_id, T2.c.car_id, T1.c.trip.distance(T2.c.trip), ) \ .filter(T1.c.car_id < T2.c.car_id,) .all()
For exhaustive listing of operators, see operators page.
Using MobilityDB ranges¶
MobilityDB also allows you to store the temporal data in either open or closed intervals on either site. While this is supported by the package at the column level, because we use pandas DataFrame to hold the values once we load them into python runtime, this data is lost, and hence not of much use. In future, this can be avoided with a better suiting data structure to hold this data instead of relying on pandas.
However, to define a column which stores temporal data as a left closed, right open interval, ie. ‘[)’, it can be done as shown below:
class Trips(Base): trip_id = Column(Integer, primary_key=True) trip = Column(TGeomPoint(True, False))
Making use of movingpandas Trajectory data structure¶
TGeomPoint objects can also be optioanlly mapped to movingpandas Trajectory objects.
For this the optional dependency “movingpandas” needs to be installed.
poetry install -E movingpandas
After this, movingpandas can be enabled with a flag on the TGeomPoint column
class Trips(Base): trip_id = Column(Integer, primary_key=True) trip = Column(TGeomPoint(use_movingpandas=True))
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK