---
title: "What is PostGIS? When and how should I use it?"
slug: "what-is-postgis-and-when-should-i-use-it"
published_at: "2022-11-24T07:45:44.000Z"
categories: "Advanced SQL, PostgreSQL, SQL Tutorials, SQL tutorials for beginners"
tags: "Geography, Geometry, Installation, Map, PostGIS, PostgreSQL, SRID"
---

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

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

![](/blog/creating-a-view-in-postgresql/image-2.png)

## 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](/how-to-install-postgis-pgadmin-postgresql-in-docker).

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

```sql
# 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:

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

![Create extension PostGIS](/blog/what-is-postgis-and-when-should-i-use-it/image-25-1024x274.png)

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

![Geography available in table](/blog/what-is-postgis-and-when-should-i-use-it/image-26-1024x512.png)

## 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:

```sql
SELECT postgis_extensions_upgrade();
```

For previous versions that also have been installed with extensions:

```sql
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.](https://postgis.net/docs/manual-3.3/postgis_administration.html#soft_upgrade "PostGIS Soft Update")

## The abstract model geometry data type

The geometry data type is an abstract model that accepts the followings [Features Access](https://www.ogc.org/standards/sfa) to generate geometrical values:

```sql
Point
LineString
LinearRing
Polygon
MultiPoint
MultiLineString
MultiPolygon
GeometryCollection
PolyhedralSurface
Triangle
TIN
```

### How to use the geometry data type?

```sql
-- 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:

![Geometrical Icon](/blog/what-is-postgis-and-when-should-i-use-it/image-27.png)

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

![Geometry Viewer](/blog/what-is-postgis-and-when-should-i-use-it/image-28-1024x241.png)

## 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**

```sql
-- 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:

![Geometry Viewer using map](/blog/what-is-postgis-and-when-should-i-use-it/image-29-1024x271.png)

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):

```sql
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](https://leafletjs.com/) or any other map library.

I encourage you to try it!

See you at the next one!