Published at Mar 6, 2024
Geospatial DataThe 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.
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)');
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';
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.
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).
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.
Happy traveling in time and space!
Looking to learn more about extending PostgreSQL for scale and times-series scenarios? Check out the tutorials in the Timescale documentation to get started.