Analytics on transport and geospatial data

Import time-series data into a hypertable

1. Unzip

   nyc_data.tar.gz
   to a <local folder>.

   This test dataset contains historical data from New York's yellow taxi network.

   To import up to 100GB of data directly from your current PostgreSQL based database, migrate with downtime using native PostgreSQL tooling. To seamlessly import 100GB-10TB+ of data, use the live migration tooling supplied by Timescale. To add data from non-PostgreSQL data sources, see Import and ingest data.

2. In Terminal, navigate to <local folder> and update the following string with your connection details to connect to your service.

   psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>?sslmode=require"

   Copy

3. Create tables to import time-series data:

   1. In your sql client, create a normal PostgreSQL table:

      CREATE TABLE "rides"(
        vendor_id TEXT,
        pickup_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        dropoff_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        passenger_count NUMERIC,
        trip_distance NUMERIC,
        pickup_longitude  NUMERIC,
        pickup_latitude   NUMERIC,
        rate_code         INTEGER,
        dropoff_longitude NUMERIC,
        dropoff_latitude  NUMERIC,
        payment_type INTEGER,
        fare_amount NUMERIC,
        extra NUMERIC,
        mta_tax NUMERIC,
        tip_amount NUMERIC,
        tolls_amount NUMERIC,
        improvement_surcharge NUMERIC,
        total_amount NUMERIC
      );

      Copy

   2. Convert rides to a hypertable and partitioned on time:

      SELECT create_hypertable('rides', by_range('pickup_datetime'), create_default_indexes=>FALSE);
      SELECT add_dimension('rides', by_hash('payment_type', 2));

      Copy

      To more fully understand how hypertables work, and how to optimize them for performance by tuning chunk intervals and enabling chunk skipping, see the hypertables documentation.

   3. Create an index to support efficient queries by vendor, rate code, and passenger count:

      CREATE INDEX ON rides (vendor_id, pickup_datetime DESC);
      CREATE INDEX ON rides (rate_code, pickup_datetime DESC);
      CREATE INDEX ON rides (passenger_count, pickup_datetime DESC);

      Copy

4. Create tables for relational data:

   1. Add a table to store the payment types data:

      CREATE TABLE IF NOT EXISTS "payment_types"(
        payment_type INTEGER,
        description TEXT
      );
      INSERT INTO payment_types(payment_type, description) VALUES
        (1, 'credit card'),
        (2, 'cash'),
        (3, 'no charge'),
        (4, 'dispute'),
        (5, 'unknown'),
        (6, 'voided trip');

      Copy

   2. Add a table to store the rates data:

      CREATE TABLE IF NOT EXISTS "rates"(
       rate_code   INTEGER,
       description TEXT
      );
      INSERT INTO rates(rate_code, description) VALUES
       (1, 'standard rate'),
       (2, 'JFK'),
       (3, 'Newark'),
       (4, 'Nassau or Westchester'),
       (5, 'negotiated fare'),
       (6, 'group ride');

      Copy

5. Upload the dataset to your service

   \COPY rides FROM nyc_data_rides.csv CSV;

   Copy

   To more fully understand how hypertables work, and how to optimize them for performance by tuning chunk intervals and enabling chunk skipping, see the hypertables documentation.

Have a quick look at your data

You query hypertables in exactly the same way as you would a relational PostgreSQL table. Use one of the following SQL editors to run a query and see the data you uploaded:

• Data mode: write queries, visualize data, and share your results in Timescale Console for all your Timescale Cloud services.
• SQL editor: write, fix, and organize SQL faster and more accurately in Timescale Console for a Timescale Cloud service.
• psql: easily run queries on your Timescale Cloud services or self-hosted TimescaleDB deployment from Terminal.

For example:

• Display the number of rides for each fare type:

  SELECT rate_code, COUNT(vendor_id) AS num_trips
  FROM rides
  WHERE pickup_datetime < '2016-01-08'
  GROUP BY rate_code
  ORDER BY rate_code;

  Copy

  This simple query runs in 3 seconds. You see something like:

  rate_code	num_trips
  1	2266401
  2	54832
  3	4126
  4	967
  5	7193
  6	17
  99	42

• To select all rides taken in the first week of January 2016, and return the total number of trips taken for each rate code:

  SELECT rates.description, COUNT(vendor_id) AS num_trips
  FROM rides
  JOIN rates ON rides.rate_code = rates.rate_code
  WHERE pickup_datetime < '2016-01-08'
  GROUP BY rates.description
  ORDER BY LOWER(rates.description);

  Copy

  On this large amount of data, this analytical query on data in the rowstore takes about 59 seconds. You see something like:

  description	num_trips
  group ride	17
  JFK	54832
  Nassau or Westchester	967
  negotiated fare	7193
  Newark	4126
  standard rate	2266401

Connect to your Timescale Cloud service

In Timescale Console open an SQL editor. The in-Console editors display the query speed. You can also connect to your service using psql.

Enable columnstore on a hypertable

Create a job that automatically moves chunks in a hypertable to the columnstore at a specific time interval. By default, your table is orderedby the time column. For efficient queries on columnstore data, remember to segmentby the column you will use most often to filter your data:

ALTER TABLE rides SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = 'vendor_id',
  timescaledb.orderby = 'pickup_datetime DESC'
);

Copy

Add a policy to convert chunks to the columnstore at a specific time interval

For example, convert data older than 8 days old to the columstore:

CALL add_columnstore_policy('rides', INTERVAL '8 days');

Copy

See add_columnstore_policy.

View your data space saving

When you convert data to the columnstore, as well as being optimized for analytics, it is compressed by more than 90%. This saves on storage costs and keeps your queries operating at lightning speed. To see the amount of space saved:

SELECT 
     pg_size_pretty(before_compression_total_bytes) as before,
     pg_size_pretty(after_compression_total_bytes) as after
FROM hypertable_compression_stats('rides');

Copy

You see something like:

Faster analytical queries on data in the columnstore

Now run the analytical query again:

SELECT rates.description, COUNT(vendor_id) AS num_trips
FROM rides
JOIN rates ON rides.rate_code = rates.rate_code
WHERE pickup_datetime < '2016-01-08'
GROUP BY rates.description
ORDER BY LOWER(rates.description);

Copy

On this large amount of data, this analytical query on data in the columnstore takes about 6 seconds.

Log in to Grafana

In your browser, log in to either:

• Self-hosted Grafana: at http://localhost:3000/. The default credentials are admin, admin.
• Grafana Cloud: use the URL and credentials you set when you created your account.

Add your service as a data source

1. Open Connections > Data sources, then click Add new data source.

2. Select PostgreSQL from the list.

3. Configure the connection:

   • Host URL, Database name, Username, and Password

     Configure using your connection details. Host URL is in the format <host>:<port>.

   • TLS/SSL Mode: select require.

   • PostgreSQL options: enable TimescaleDB.

   • Leave the default setting for all other fields.

4. Click Save & test.

   Grafana checks that your details are set correctly.

Create the dashboard

1. On the Dashboards page, click New and select New dashboard.

2. Click Add visualization.

3. Select the data source that connects to your Timescale Cloud service. The Time series visualization is chosen by default.

   

   Expand image

4. In the Queries section, select Code, then select Time series in Format.

5. Select the data range for your visualization: the data set is from 2016. Click the date range above the panel and set:

   • From: 2016-01-01 01:00:00
   • To: 2016-01-30 01:00:00

Combine TimescaleDB and Grafana functionality to analyze your data

Combine a TimescaleDB time_bucket, with the Grafana $__timefilter() function to set the pickup_datetime column as the filtering range for your visualizations.

SELECT
  time_bucket('1 day', pickup_datetime) AS "time",
  COUNT(*)
FROM rides
WHERE $__timeFilter(pickup_datetime)
GROUP BY time
ORDER BY time;

Copy

This query groups the results by day and orders them by time.

Expand image

Click Save dashboard

Connect to your Timescale Cloud service and add the PostGIS extension:

CREATE EXTENSION postgis;

Copy

Add geometry columns for pick up and drop off locations:

ALTER TABLE rides ADD COLUMN pickup_geom geometry(POINT,2163);
ALTER TABLE rides ADD COLUMN dropoff_geom geometry(POINT,2163);

Copy

Convert the latitude and longitude points into geometry coordinates that work with PostGIS:

UPDATE rides SET pickup_geom = ST_Transform(ST_SetSRID(ST_MakePoint(pickup_longitude,pickup_latitude),4326),2163),
   dropoff_geom = ST_Transform(ST_SetSRID(ST_MakePoint(dropoff_longitude,dropoff_latitude),4326),2163);

Copy

This updates 10,906,860 rows of data on both columns, it takes a while. Coffee is your friend.

Create a geolocalization dashboard

1. In Grafana, create a new dashboard that is connected to your Timescale Cloud service data source with a Geomap visualization.

2. In the Queries section, select Code, then select the Time series Format.

   

   Expand image

3. To find rides longer than 5 miles in Manhattan, paste the following query:

   SELECT time_bucket('5m', rides.pickup_datetime) AS time,
          rides.trip_distance AS value,
          rides.pickup_latitude AS latitude,
          rides.pickup_longitude AS longitude
   FROM rides
   WHERE rides.pickup_datetime BETWEEN '2016-01-01T01:41:55.986Z' AND '2016-01-01T07:41:55.986Z' AND
     ST_Distance(pickup_geom,
                 ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)
     ) < 2000
   GROUP BY time,
            rides.trip_distance,
            rides.pickup_latitude,
            rides.pickup_longitude
   ORDER BY time
   LIMIT 500;

   Copy

   You see a world map with a dot on New York.

4. Zoom into your map to see the visualization clearly.

Customize the visualization

1. In the Geomap options, under Map Layers, click + Add layer and select Heatmap. You now see the areas where a taxi driver is most likely to pick up a passenger who wants a longer ride, and make more money.

   

   Expand image