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 IFNOTEXISTS;
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.
To fetch all locations of a specific vehicle (let's say, vehicle 1) during a specific period, you can query Timescale like this:
WHERE vehicle_id =1ANDtimeBETWEEN'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:
SELECTDISTINCTON(vehicle_id) vehicle_id, ST_AsText(location)AS location
WHEREtime>now()-INTERVAL'24 hours'ORDERBY vehicle_id,timeDESC;
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:
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.