PostgreSQL Extensions: Using PostGIS and Timescale for Advanced Geospatial Insights
The data landscape is vast and multifaceted, with different kinds of data requiring different handling techniques. For geospatial data, a powerful Postgres extension called PostGIS can extend this open-source database, enriching your tables with best-in-class geospatial objects and a broad set of functions to interact with them.
This can be very powerful when linked with the time-series capabilities that you get when you choose a Timescale database.
In this blog post, we'll discuss how you can integrate these data types setup to manage and manipulate geospatial and time-series data together.
PostGIS: Working With Geospatial and Time-Series Data
Connect to your Timescale instance, and create a new database:
CREATE EXTENSION postgis IF NOT EXISTS;
On Timescale, you can find available extensions by going to Operations > Extensions from your service overview, which will also give you installation instructions.
Now, create a hypothetical table that stores geospatial time-series data: a fleet of 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)');
Basic Queries
1. Querying time-series data
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. Querying geospatial data
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;
Here, ST_AsText(location) is used to convert the binary geospatial data into human-readable format.
3. Querying geospatial and time-series data together
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).
Get Started With PostGIS and Timescale
When we combine PostGIS and Timescale, PostgreSQL can become a powerful tool for handling both geospatial and time-series data. The examples shown in this post just scratch the surface of what you can do with these tools. Both extensions offer much more functionalities, and you can use them to conduct complex analyses of your geospatial time-series data.
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.
How OpenSauced Is Building a Copilot for Git History With pgvector and Timescale