PostgreSQL Extensions: Using PostGIS for Geospatial and Time-Series Data

The data landscape is vast and multifaceted, with different kinds of data requiring different handling techniques. For geospatial data, PostGIS extends PostgreSQL with powerful capabilities for handling location data. When combined with Timescale's time-series functionality, you get a database superpower: the ability to track "where" and "when" together.

Let's explore how to set up and use these tools together to build powerful location-aware, time-series applications.

🗺️ PostGIS + Timescale = Location & Time Analytics

First, connect to your Timescale instance and enable PostGIS:

CREATE EXTENSION postgis IF NOT EXISTS;

💡 Tip: In Timescale, navigate to Operations > Extensions from your service overview for installation instructions.

Creating a Geospatial Time-Series Table

Now, let's create create a practical example table that tracks delivery vehicles and their GPS coordinates over time.

CREATE TABLE vehicle_location (
    time TIMESTAMPTZ NOT NULL,
    vehicle_id INT NOT NULL,
    location GEOGRAPHY(POINT, 4326)
);

Here, location is a GEOGRAPHY type column that stores GPS coordinates (using the 4326/WGS84 coordinate system) while time records the time the GPS coordinate was logged for a specific vehicle_id.

To create a time-series hypertable from this regular PostgreSQL table and insert some dummy data, use:

SELECT create_hypertable('vehicle_location', 'time');
CREATE INDEX ON vehicle_location(vehicle_id, time DESC);

INSERT INTO vehicle_location VALUES 
  ('2023-05-29 20:00:00', 1, 'POINT(15.3672 -87.7231)'),
  ('2023-05-30 20:00:00', 1, 'POINT(15.3652 -80.7331)'),
  ('2023-05-31 20:00:00', 1, 'POINT(15.2672 -85.7431)');

Powerful Queries You Can Run

1. Find vehicle locations by time range
To fetch all locations of a specific vehicle (let's say, vehicle 1) during a specific period, you can query Timescale like this:

SELECT * 
FROM vehicle_location 
WHERE vehicle_id = 1 AND time BETWEEN '2023-05-30 00:00:00' AND '2023-05-31 23:59:59';

2. Get last known locations of all vehicles
To efficiently fetch the last known location of all vehicles using the Timescale SkipScan feature, we can write a slightly more complex query:

SELECT DISTINCT ON (vehicle_id) vehicle_id, ST_AsText(location) AS location 
FROM vehicle_location 
WHERE time > now() - INTERVAL '24 hours' 
ORDER BY vehicle_id, 
time DESC;

The ST_AsText() function converts binary geospatial data into readable coordinates.

3. Find vehicles near a specific location
To fetch all vehicles that were within 1 kilometer of a specific point (15.2 -85.743) at any time, we can use the following SQL:

SELECT DISTINCT vehicle_id 
FROM vehicle_location 
WHERE ST_DWithin(
          location, 
          ST_GeogFromText('POINT(15.2 -85.743)'), 
          1000
       );

In this query, ST_DWithin(location, ST_GeogFromText('POINT(15.2 -85.743)'), 1000) checks whether location is within 1,000 meters of the point (15.2 -85.743).

The Strategic Advantage

When we combine PostGIS and Timescale, PostgreSQL becomes a powerful platform for analyzing both where and when events occur. This opens possibilities for:

• Fleet tracking and management

• Location-based analytics

• IoT sensor networks

• Movement pattern analysis

• Geofencing with historical context

Both extensions offer much more functionality than shown here, enabling complex analyses of geospatial time-series data.

Get Started with PostGIS + Timescale

Remember, efficient data analysis is all about choosing the right tools. With PostGIS and Timescale in your arsenal, you're equipped to face a multitude of data challenges, describing when things happened and where they occurred.

Happy traveling in time and space!

Learn More

Looking to learn more about extending PostgreSQL for scale and times-series scenarios? Check out the tutorials in the Timescale documentation to get started.