Postgres beyond basics : Exploring PostGIS

Introduction:

PostGIS is a popular extension for PostgreSQL that adds support for geographic and spatial data types and functions, allowing you to work with location-based data in your database.

PostGIS extends PostgreSQL by introducing new data types and functions for storing, querying, and analyzing spatial data, such as points, lines, polygons, and more complex geometries.

Installation of PostGIS:

We can download and install from the source code using the below URL:

https://postgis.net/development/source_code/

To compile assuming you have all the dependencies in your search path:

tar -xvfz postgis-3.3.5dev.tar.gz
cd postgis-3.3.5dev
./configure
make
make install

Login to psql and we can check if the postgis are available in the pg_available_extensions view

osdb=# create extension postgis ;
CREATE EXTENSION

We can check postgis in installed extensions

And we need to install some of the dependent postgis extensions as follows:

osdb=# create extension postgis_raster ;
osdb=# create extension postgis_sfcgal ;
osdb=# create extension fuzzystrmatch; --needed for postgis_tiger_geocoder 
osdb=# create extension postgis_tiger_geocoder;
osdb=# create extension postgis_topology;

Here is an example of how to use the PostGIS extension:

Now, let’s create a table to store geographic data. We’ll create a simple table to store points representing cities:

CREATE TABLE cities (
  id serial PRIMARY KEY,
  name varchar(100),
  location geography(Point, 4326)
);

In this table:

id is an auto-incrementing primary key.

name is the name of the city.

location is a geography column that stores point coordinates in the WGS 84 (EPSG:4326) coordinate system.

We can insert some sample data into the cities table, including the geographic coordinates of cities. 

INSERT INTO cities (name, location) VALUES
  ('New York', ST_GeogFromText('POINT(-74.006, 40.7128)')),
  ('Los Angeles', ST_GeogFromText('POINT(-118.2437, 34.0522)')),
  ('Chicago', ST_GeogFromText('POINT(-87.6298, 41.8781)'));
  ('Middletown', ST_GeogFromText('POINT(-56.006 38.7128)'));

You can perform various spatial queries on your data. For example, to find cities within a certain distance of a point, you can use the ST_DWithin function:

osdb=# SELECT name FROM cities 
WHERE ST_DWithin(ST_SetSRID(location, 4326)::geography, 
                 ST_GeomFromText('POINT(-56.006 38.7128)',4326)::geography, 
                 1600000 -–distance in meters);

   name    
------------
 New York
 Middletown
(2 rows)

This query will return the names of cities within 1600 kilometers of Middletown City.

Conclusion:

That’s a basic example of how to use the PostGIS extension in PostgreSQL to work with geographic data. PostGIS provides a wide range of functions for more advanced spatial analysis and manipulation of geographic data.

PostGIS is an open-source and flexible extension that adds spatial capabilities to PostgreSQL, making it a robust choice for applications that involve geographic and spatial data. It’s widely used in various domains, including urban planning, environmental science, transportation, and location-based services.