> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Analyze transport and geospatial data

> Simulate and analyze a transport dataset in Tiger Cloud

export const SELF_LONG = 'self-hosted TimescaleDB';

export const DATA_MODE = 'Data view';

export const CONSOLE = 'Tiger Console';

export const COMPANY = 'Tiger Data ';

export const HYPERTABLE_CAP = 'Hypertable';

export const HYPERCORE = 'hypercore';

export const ROWSTORE = 'rowstore';

export const HYPERCORE_CAP = 'Hypercore';

export const CHUNK = 'chunk';

export const HYPERTABLE = 'hypertable';

export const COLUMNSTORE = 'columnstore';

export const SERVICE_SHORT = 'service';

export const TIMESCALE_DB = 'TimescaleDB';

export const SERVICE_LONG = 'Tiger Cloud service';

export const PG = 'Postgres';

Real-time analytics refers to the process of collecting, analyzing, and interpreting data instantly as it
is generated. This approach enables you track and monitor activity, and make decisions based on real-time
insights on data stored in a {SERVICE_LONG}.

![Real-time analytics geolocation][real-time-analytics-geolocation]

This page shows you how to integrate [Grafana][grafana-docs] with a {SERVICE_LONG} and make insights based on visualization
of data optimized for size and speed in the {COLUMNSTORE}.

## Prerequisites

To follow the steps on this page:

* Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.<p />

  You need [your connection details][connection-info]. This procedure also
  works for [{SELF_LONG}][enable-timescaledb].

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted

[connection-info]: /integrations/find-connection-details

* Install and run [self-managed Grafana][grafana-self-managed], or sign up for [Grafana Cloud][grafana-cloud].

## Optimize time-series data in hypertables

{HYPERTABLE_CAP}s are {PG} tables in {TIMESCALE_DB} that automatically partition your time-series data by time. Time-series data represents the way a system, process, or behavior changes over time. {HYPERTABLE_CAP}s enable {TIMESCALE_DB} to work efficiently with time-series data. Each {HYPERTABLE} is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. When you run a query, {TIMESCALE_DB} identifies the correct chunk and runs the query on it, instead of going through the entire table.

[{HYPERCORE_CAP}][hypercore] is the hybrid row-columnar storage engine in {TIMESCALE_DB} used by {HYPERTABLE}s. Traditional
databases force a trade-off between fast inserts (row-based storage) and efficient analytics
(columnar storage). {HYPERCORE_CAP} eliminates this trade-off, allowing real-time analytics without sacrificing
transactional capabilities.

{HYPERCORE_CAP} dynamically stores data in the most efficient format for its lifecycle:

![Move from rowstore to columstore in hypercore][move-from-rowstore-to-columstore-in-hypercore]

* **Row-based storage for recent data**: the most recent chunk (and possibly more) is always stored in the {ROWSTORE},
  ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as a
  writethrough for inserts and updates to columnar storage.
* **Columnar storage for analytical performance**: chunks are automatically compressed into the {COLUMNSTORE}, optimizing
  storage efficiency and accelerating analytical queries.

Unlike traditional columnar databases, {HYPERCORE} allows data to be inserted or modified at any stage, making it a
flexible solution for both high-ingest transactional workloads and real-time analytics—within a single database.

[hypercore]: /manage-data/capabilities/hypercore/understand-hypercore

[move-from-rowstore-to-columstore-in-hypercore]: https://assets.timescale.com/docs/images/hypercore_intro.svg

Because {TIMESCALE_DB} is 100% {PG}, you can use all the standard {PG} tables, indexes, stored procedures, and other objects alongside your {HYPERTABLE}s. This makes creating and working with {HYPERTABLE}s similar to standard {PG}.

1. **Import time-series data into a {HYPERTABLE}**

   1. Unzip [nyc\_data.tar.gz][nyc-data-targz] 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 {PG}-based database,
      [migrate with downtime][migrate-with-downtime] using native {PG} tooling. To seamlessly import 100GB-10TB+
      of data, use the [live migration][migrate-live] tooling supplied by {COMPANY}. To add data from non-{PG}
      data sources, see [Import and ingest data][data-ingest].

   2. In Terminal, navigate to `<local folder>` and update the following string with [your connection details][connection-info]
      to connect to your {SERVICE_SHORT}.

      ```bash theme={"dark"}
      psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>?sslmode=require"
      ```

   3. Create an optimized {HYPERTABLE} for your time-series data:

      1. Create a [{HYPERTABLE}][hypertables-section] with [{HYPERCORE}][hypercore] enabled by default for your
         time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes]
         on data in the {COLUMNSTORE}, remember to `segmentby` the column you will use most often to filter your data.

         In your sql client, run the following command:

         ```sql theme={"dark"}
         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
         ) WITH (
           tsdb.hypertable,
           tsdb.create_default_indexes=false,
           tsdb.segmentby='vendor_id',
           tsdb.orderby='pickup_datetime DESC'
         );
         ```

         When you create a {HYPERTABLE} using [CREATE TABLE ... WITH ...][hypertable-create-table], the default partitioning
         column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
         [columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
         interval equal to the value of the [chunk\_interval][create_table_arguments], defined through `compress_after` in the
         policy. This columnar format enables fast scanning and
         aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the
         {COLUMNSTORE} conversion, {HYPERTABLE} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries.

         You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or
         `created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
         [remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].

         You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.

         [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

         [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples

         [convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore

         [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments

         [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

         [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy

      2. Add another dimension to partition your {HYPERTABLE} more efficiently:
         ```sql theme={"dark"}
         SELECT add_dimension('rides', by_hash('payment_type', 2));
         ```

      3. Create an index to support efficient queries by vendor, rate code, and passenger count:
         ```sql theme={"dark"}
         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);
         ```

   4. Create {PG} tables for relational data:

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

         ```sql theme={"dark"}
         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');
         ```

      2. Add a table to store the rates data:

         ```sql theme={"dark"}
         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');
         ```

   5. Upload the dataset to your {SERVICE_SHORT}
      ```sql theme={"dark"}
      \COPY rides FROM nyc_data_rides.csv CSV;
      ```

2. **Have a quick look at your data**

   You query {HYPERTABLE}s in exactly the same way as you would a relational {PG} 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 [{CONSOLE}][portal-data-mode] for all your {SERVICE_LONG}s.
   * **SQL editor**: write, fix, and organize SQL faster and more accurately in [{CONSOLE}][portal-ops-mode] for a {SERVICE_LONG}.
   * **psql**: easily run queries on your {SERVICE_LONG}s or {SELF_LONG} deployment from Terminal.

   For example:

   * Display the number of rides for each fare type:

     ```sql theme={"dark"}
     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;
     ```

     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:

     ```sql theme={"dark"}
     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);
     ```

     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    |

[connection-info]: /integrations/find-connection-details

[data-ingest]: /manage-data/import-and-ingest/import-terminal

[hypercore]: /manage-data/data-management/hypercore/understand-hypercore

[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

[hypertables-section]: /manage-data/data-management/hypertables/understand-hypertables

[migrate-live]: /manage-data/import-and-ingest/live-migration

[migrate-with-downtime]: /manage-data/import-and-ingest/migrate-with-downtime

[nyc-data-targz]: https://assets.timescale.com/docs/downloads/nyc_data.tar.gz

[portal-data-mode]: https://console.cloud.timescale.com/dashboard/services?popsql

[portal-ops-mode]: https://console.cloud.timescale.com/dashboard/services

[secondary-indexes]: /use-timescale/hypercore/secondary-indexes/

## Connect Grafana to Tiger Cloud

To visualize the results of your queries, enable Grafana to read the data in your {SERVICE_SHORT}:

1. **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.
2. **Add your {SERVICE_SHORT} 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][connection-info]. `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.

[cloud-login]: https://console.cloud.timescale.com/

[connection-info]: /integrations/find-connection-details

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[grafana-cloud]: https://grafana.com/get/

[grafana-self-managed]: https://grafana.com/get/?tab=self-managed

## Monitor performance over time

A Grafana dashboard represents a view into the performance of a system, and each dashboard consists of one or
more panels, which represent information about a specific metric related to that system.

To visually monitor the volume of taxi rides over time:

1. **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 {SERVICE_LONG}.
      The `Time series` visualization is chosen by default.
      ![Grafana create dashboard][grafana-create-dashboard]

   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`

2. **Combine {TIMESCALE_DB} and Grafana functionality to analyze your data**

   Combine a {TIMESCALE_DB} [time\_bucket][use-time-buckets], with the Grafana `$__timefilter()` function to set the
   `pickup_datetime` column as the filtering range for your visualizations.

   ```sql theme={"dark"}
   SELECT
     time_bucket('1 day', pickup_datetime) AS "time",
     COUNT(*)
   FROM rides
   WHERE $__timeFilter(pickup_datetime)
   GROUP BY time
   ORDER BY time;
   ```

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

   ![Grafana real-time analytics][grafana-real-time-analytics]

3. **Click `Save dashboard`**

## Optimize revenue potential

Having all this data is great but how do you use it? Monitoring data is useful to check what
has happened, but how can you analyse this information to your advantage? This section explains
how to create a visualization that shows how you can maximize potential revenue.

### Set up your data for geospatial queries

To add geospatial analysis to your ride count visualization, you need geospatial data to work out which trips
originated where. As {TIMESCALE_DB} is compatible with all {PG} extensions, use [PostGIS][postgis] to slice
data by time and location.

1. Connect to your [{SERVICE_LONG}][in-console-editors] and add the PostGIS extension:

   ```sql theme={"dark"}
   CREATE EXTENSION IF NOT EXISTS postgis;
   ```

2. Add geometry columns for pick up and drop off locations:

   ```sql theme={"dark"}
   ALTER TABLE rides ADD COLUMN pickup_geom geometry(POINT,2163);
   ALTER TABLE rides ADD COLUMN dropoff_geom geometry(POINT,2163);
   ```

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

   First, set the decompression limit to avoid errors when updating large amounts of data:

   ```sql theme={"dark"}
   SET timescaledb.max_tuples_decompressed_per_dml_transaction TO 0;
   ```

   Then run the UPDATE to convert coordinates:

   ```sql theme={"dark"}
   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);
   ```

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

### Visualize the area where you can make the most money

In this section you visualize a query that returns longer rides (greater than 5 miles)
within 2 km of Times Square. By filtering for longer trips and visualizing pickup locations,
you can identify the areas where a taxi driver is most likely to pick up a passenger who wants
a longer ride, and make more money.

1. **Create a geolocation dashboard**

   1. In Grafana, from the `Dashboards` page, click `New` and select `New dashboard`.

   2. Click `Add visualization`, then select the data source that connects to your {SERVICE_LONG} and the `Geomap` visualization type in the top right.

   3. In the `Queries` section, select `Code` and change the `Format` to `Time series`.

      ![Real-time analytics geolocation][real-time-analytics-geolocation]

   4. To find longer rides (over 5 miles) within 2 km of Times Square, paste the following query:

      ```sql theme={"dark"}
      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 AND
        rides.trip_distance > 5
      GROUP BY time,
               rides.trip_distance,
               rides.pickup_latitude,
               rides.pickup_longitude
      ORDER BY time
      LIMIT 500;
      ```

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

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

2. **Customize the visualization**

   1. In the Geomap options, under `Map Layers`, click `+ Add layer` and select `Heatmap`.

   2. Zoom into your map.

      The heatmap now shows the pickup locations for longer rides (over 5 miles) near Times Square.
      The areas with higher heat intensity indicate where taxi drivers are most likely to pick up
      passengers who want longer rides, helping them make more money.

      ![Real-time analytics geolocation][real-time-analytics-geolocation-heatmap]

[in-console-editors]: /deploy-and-operate/tiger-cloud/get-started/run-queries-from-console

[postgis]: http://postgis.net/

[real-time-analytics-geolocation]: https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-configure-dashboard.png

[real-time-analytics-geolocation-heatmap]: https://assets.timescale.com/docs/images/use-case-rta-grafana-heatmap.png

You have integrated Grafana with a {SERVICE_LONG} and made insights based on visualization of
your data.

[grafana-cloud]: https://grafana.com/get/

[grafana-create-dashboard]: https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-configure-dashboard.png

[grafana-docs]: https://grafana.com/docs/

[grafana-real-time-analytics]: https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-final-dashboard.png

[grafana-self-managed]: https://grafana.com/get/?tab=self-managed

[real-time-analytics-geolocation]: https://assets.timescale.com/docs/images/use-case-rta-grafana-heatmap.png

[use-time-buckets]: /manage-data/data-management/time-buckets/use-time-buckets