What is PostGIS? When and how should I use it?

What is PostGIS?

Postgis is essentially an open-source add-on for PostgreSQL database which adds support for Geographic objects and converts PostgreSQL into a spatial database.

When should I use PostGIS?

If you have databases with addresses you want to allocate in a map or databases which store complex geometrical values, PostGIS is your option.

How to install PostGIS?

This will depend on your working environment, if you are using docker, here is a little tutorial.

In case you have a Linux working PostgreSQL, then you should use the next commands through your console:

# Please check if this is the last version here
wget http://postgis.net/stuff/postgis-3.3.3dev.tar.gz
tar -xvzf postgis-3.3.3dev.tar.gz
cd postgis-3.3.3dev
./configure
make
make install

Making PostGIS available in my Database

After PostGIS is installed, you have to make it available in your database (Don’t worry, you can do it in PgAdmin)

To achieve this, let’s query the next commands:

      CREATE EXTENSION IF NOT EXISTS plpgsql;
      CREATE EXTENSION postgis;
      CREATE EXTENSION postgis_raster; -- OPTIONAL
      CREATE EXTENSION postgis_topology; -- OPTIONAL

After doing this, you will notice that when you create a table, now you have geometry and geography columns available.

How do I upgrade PostGIS?

From version 3 afterward, if you installed PostGIS using extensions upgrading it is as simple as running the following query:

SELECT postgis_extensions_upgrade();

For previous versions that also have been installed with extensions:

ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();
-- This second call is needed to rebundle postgis_raster extension
SELECT postgis_extensions_upgrade();

In case you haven’t used any of these options or you have multiple PostGIS versions, you can check the documentation here.

The abstract model geometry data type

The geometry data type is an abstract model that accepts the followings Features Access to generate geometrical values:

Point
LineString
LinearRing
Polygon
MultiPoint
MultiLineString
MultiPolygon
GeometryCollection
PolyhedralSurface
Triangle
TIN

How to use the geometry data type?

-- Create table for geometrical values
CREATE TABLE geometrical_values (name varchar, geom geometry);

-- Insert some geometrical values
INSERT INTO geometrical_values VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(1 1, 2 2, 3 2, 3 3)'),
  ('Polygon', 'POLYGON((4 4, 5 4, 5 5, 4 5, 4 4))');

-- Select the geometrical values
SELECT name,geom FROM geometrical_values;

In PgAdmin 4 (That supports PostGIS extension) you will see the next icon after doing these queries:

If you click on it, it will give you the geometrical representation of our SELECT query:

The geography data type

The geography data type doesn’t support TINS, POLYHEDRALSURFACEs, and curves as it assumes you are using a world map as your canvas.

How to use the geography data type?

Pinpointing in a map with PostGIS

-- Create table for geography values

CREATE TABLE geographycal_values (name varchar, geof geography);

INSERT INTO geographycal_values (name, geof) VALUES ('USA', ST_GeogFromText('SRID=4326;POINT(-100 40)') );
INSERT INTO geographycal_values (name, geof) VALUES ('Mexico', ST_GeogFromText('SRID=4326;POINT(-100 20)') );
INSERT INTO geographycal_values (name, geof) VALUES ('France', ST_GeogFromText('SRID=4326;POINT(0 49)') );

SELECT name,geof FROM geographycal_values;

If you click your icon again, you might see something like this in your geometry viewer:

As you can see, now the points are set on a map.

See we use in our code POINT with some values inside it, well, these are longitude and latitude, see how this code works (And please note that google gives you latitude and longitude and not the other way around, will save you some headaches):

INSERT INTO geographycal_values (name, geof) VALUES ('Argentina', ST_GeogFromText('SRID=4326;POINT(-58.480599 -34.626677)') );

You could also use LINESTRING to trace a line between states, POLYGON to set a state area, or any other data type (Except the ones we said before).

Conclusion

PostGIS is a powerful tool to convert the tables in your database into powerful Geometrical or Geographical management. You could also create your own maps with it and display them using Leaflet or any other map library.

I encourage you to try it!

See you at the next one!

Share on Facebook