The `postgis` extension

The postgis PostgreSQL extension provides storing, indexing and querying geographic data. It helps in spatial data analysis, the study of patterns, anomalies, and theories within spatial or geographical data.

For more information about these functions and the options available, see the PostGIS documentation.

Use the `postgis` extension to analyze geospatial data

The postgis PostgreSQL extension allows you to conduct complex analyses of your geospatial time-series data. Timescale understands that you have a multitude of data challenges and helps you discover when things happened, and where they occurred. In this example you can query when the covid cases were reported, where they were reported, and how many were reported around a particular location.

Using the `postgis` extension to analyze geospatial data

Install the postgis extension:
```
CREATE EXTENSION postgis;
```

You can confirm if the extension is installed using the \dx command. The extensions that are installed are listed:


List of installed extensions
Name         | Version |   Schema   |                                      Description                                      
---------------------+---------+------------+---------------------------------------------------------------------------------------
 pg_stat_statements  | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 pgcrypto            | 1.3     | public     | cryptographic functions
 plpgsql             | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis             | 3.3.3   | public     | PostGIS geometry and geography spatial types and functions
 timescaledb         | 2.11.0  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
 timescaledb_toolkit | 1.16.0  | public     | Library of analytical hyperfunctions,     time-series pipelining, and other SQL utilities
(6 rows)

Create a table named covid_location, where, location is a GEOGRAPHY type column that stores GPS coordinates using the 4326/WGS84 coordinate system, and time records the time the GPS coordinate was logged for a specific state_id:
```
CREATE TABLE covid_location (
time TIMESTAMPTZ NOT NULL,
state_id INT NOT NULL,
location GEOGRAPHY(POINT, 4326),
cases INT NOT NULL,
deaths INT NOT NULL 
);
```
Convert the standard table into a hypertable partitioned on the time column using the create_hypertable() function provided by Timescale. You must provide the name of the table and the column in that table that holds the timestamp data to use for partitioning:
```
SELECT create_hypertable('covid_location', by_range('time'));
```
Create an index on the state_id column, to support efficient queries:
```
CREATE INDEX ON covid_location (state_id, time DESC);
```

Insert some randomly generated values in the covid_location table. The longitude and latitude coordinates of New Jersey are (-73.935242 40.730610), and New York are (-74.871826 39.833851):


INSERT INTO covid_location VALUES
('2023-06-28 20:00:00',34,'POINT(-74.871826 39.833851)',5,2),
('2023-06-28 20:00:00',36,'POINT(-73.935242 40.730610)',7,1),
('2023-06-29 20:00:00',34,'POINT(-74.871826 39.833851)',14,0),
('2023-06-29 20:00:00',36,'POINT(-73.935242 40.730610)',12,1),
('2023-06-30 20:00:00',34,'POINT(-74.871826 39.833851)',10,4);

To fetch all cases of a specific state during a specific period, use:


SELECT * FROM covid_location 
WHERE state_id = 34 AND time BETWEEN '2023-06-28 00:00:00' AND '2023-06-30 23:59:59';

The data you get back looks a bit like this:


time          | state_id |                      location                      | cases | deaths 
------------------------+----------+----------------------------------------------------+-------+--------
 2023-06-28 20:00:00+00 |       34 | 0101000020E61000005C7347FFCBB752C0535E2BA1BBEA4340 |     5 |      2
 2023-06-29 20:00:00+00 |       34 | 0101000020E61000005C7347FFCBB752C0535E2BA1BBEA4340 |    14 |      0
 2023-06-30 20:00:00+00 |       34 | 0101000020E61000005C7347FFCBB752C0535E2BA1BBEA4340 |    10 |      4
(3 rows)

To fetch the latest logged cases of all states using the Timescale SkipScan feature. Replace <Interval_Time> with the number of days between the day you are running the query and the day the last report was logged in the table, in this case 30, June, 2023:
```
SELECT DISTINCT ON (state_id) state_id, ST_AsText(location) AS location 
FROM covid_location 
WHERE time > now() - INTERVAL '<Interval_Time>' 
ORDER BY state_id, 
time DESC;
```
The ST_AsText(location) function converts the binary geospatial data into human-readable format. The data you get back looks a bit like this:
```
state_id |          location           
----------+-----------------------------
34 | POINT(-74.871826 39.833851)
(1 row)
```

To fetch all cases and states that were within 10000 meters of Manhattan at any time:


SELECT DISTINCT cases, state_id 
FROM covid_location 
WHERE ST_DWithin(
location, 
ST_GeogFromText('POINT(-73.9851 40.7589)'), 
10000
);

The data you get back looks a bit like this:


cases | state_id 
-------+----------
 7 |       36
12 |       36
(2 rows)

The postgis extension

Use the postgis extension to analyze geospatial data

Using the postgis extension to analyze geospatial data

Related Content

The `postgis` extension

Use the `postgis` extension to analyze geospatial data

Using the `postgis` extension to analyze geospatial data