Install and Use PostGIS - Documentation

What PostGIS is¶

PostGIS is a spatial extension for PostgreSQL that adds geographic object support — spatial data types (geometry, geography), spatial indexing, and a large library of spatial functions (prefixed ST_, for "Spatial Type") for querying and manipulating that data directly in SQL. It's effectively what turns a regular Postgres database into a full GIS-capable spatial database, and it's the backing store for a large share of open-source GIS infrastructure — QGIS, GeoServer, and most PostgreSQL-backed web mapping stacks can all read and write directly against it.

Official project website: https://postgis.net

Geometry vs. geography types¶

PostGIS gives you two related but distinct spatial column types:

geometry — treats coordinates on a flat, Cartesian plane. Faster for most operations, and the right choice once your data is in a projected CRS (planar units like meters). Most PostGIS functions and spatial indexes were designed around this type first.
geography — treats coordinates on the actual curved surface of the Earth (great-circle math), always in WGS84 (EPSG:4326) with distances/areas in meters. Slower than geometry for the same operation, but gives correct real-world distance and area results without needing to reproject first — useful when your data spans a wide area (e.g. global point data) where planar math would introduce significant error.

Rule of thumb: if your data is regional and you're working in a locally-appropriate projected CRS, use geometry. If your data is global or you want distance/area in meters without manually reprojecting, use geography.

Every geometry/geography column carries an SRID (Spatial Reference ID — PostGIS's term for the EPSG code identifying its CRS). Mixing geometries with different SRIDs in the same operation without reprojecting first (ST_Transform) will raise an error or silently produce wrong results depending on the function.

Install¶

Install PostgreSQL & PostGIS¶

sudo apt -y install postgresql postgresql-contrib postgis

sudo systemctl enable postgresql

Create the postgis db & user and enable the postgis extension¶

sudo -u postgres createuser postgis -P
sudo -u postgres createdb postgis -O postgis
sudo -u postgres psql -d postgis -c "CREATE EXTENSION postgis;"

CREATE EXTENSION postgis is what actually registers the spatial types, functions, and system tables (spatial_ref_sys, geometry_columns) into that specific database — it's a per-database step, so a fresh database created later needs the same extension statement run against it again.

Loading spatial data¶

Install pre-requisites¶

sudo apt-get -y install gdal-bin unzip

This installs ogr2ogr and friends — the GDAL/OGR command-line toolkit that PostGIS relies on (rather than reimplementing itself) for translating between spatial formats and loading them into the database.

Natural Earth Data¶

Based on this doc.

Natural Earth Data downloads found here.

Download the spatial data¶

mkdir ~/spatial_data && \
cd ~/spatial_data

wget https://naciscdn.org/naturalearth/packages/natural_earth_vector.zip && \
unzip natural_earth_vector.zip && \
cd 110m_cultural

Load the spatial data into the postgis database¶

sudo -u postgres ogr2ogr -f PostgreSQL PG:dbname=postgis -progress -nlt PROMOTE_TO_MULTI ./ne_110m_admin_0_countries.shp

-nlt PROMOTE_TO_MULTI promotes single-geometry features (e.g. Polygon) to their multi-part equivalent (MultiPolygon) during load — this avoids failures when a Shapefile contains a mix of single and multi-part geometries, since a single PostGIS column can only hold one geometry type.

sudo -u postgres ogrinfo -so PG:dbname=postgis ne_110m_admin_0_countries

ogrinfo -so ("summary only") is a quick way to inspect a loaded layer's schema, geometry type, extent, and SRID without pulling any actual feature data.

Query the spatial data¶

sudo -u postgres psql -d postgis

postgis=# \d ne_110m_admin_0_countries

SELECT admin, ST_Y(ST_Centroid(wkb_geometry)) as latitude
FROM ne_110m_admin_0_countries
ORDER BY latitude DESC
LIMIT 10;

This lists the top ten most northerly countries by the latitude of their centroid:

   admin   |      latitude
-----------+--------------------
 Greenland |  74.77048769398986
 Norway    |  69.15685630975351
 Iceland   |  65.07427633529105
 Finland   |  64.50409403963651
 Sweden    | 62.811484968080336
 Russia    |    61.961663494923
 Canada    |  61.46907614534896
 Estonia   | 58.643695426630906
 Latvia    |  56.80717513427924
 Denmark   |  56.06393446179454

ST_Centroid returns the geometric center point of each country polygon, and ST_Y pulls just the latitude (Y coordinate) out of that point — a common pattern for reducing a polygon to a single representative coordinate for sorting, labeling, or simple proximity comparisons.

Common ST_ functions¶

A working subset covering most day-to-day spatial queries:

Function	Purpose
`ST_Distance(a, b)`	Distance between two geometries (units depend on SRID/type)
`ST_DWithin(a, b, distance)`	True if two geometries are within a given distance — prefer this over `ST_Distance(a,b) < x` since it can use a spatial index
`ST_Intersects(a, b)`	True if two geometries share any point
`ST_Contains(a, b)`	True if geometry `a` fully contains `b`
`ST_Within(a, b)`	True if geometry `a` is fully inside `b` (the inverse of `ST_Contains`)
`ST_Buffer(geom, distance)`	Returns a polygon representing the area within `distance` of `geom`
`ST_Union(a, b)`	Merges geometries into one
`ST_Intersection(a, b)`	Returns the overlapping area/geometry between two features
`ST_Centroid(geom)`	Returns the geometric center point of a feature
`ST_Area(geom)` / `ST_Length(geom)`	Area of a polygon / length of a line, in the units of the geometry's SRID
`ST_Transform(geom, srid)`	Reprojects a geometry to a different SRID
`ST_AsGeoJSON(geom)`	Converts a geometry to a GeoJSON string, useful when returning query results to a web client
`ST_SetSRID(geom, srid)`	Assigns an SRID to a geometry that doesn't have one set (does not reproject — use `ST_Transform` for that)
`ST_MakePoint(x, y)`	Builds a point geometry from raw coordinates

Example: find countries within 500km of a point¶

SELECT admin
FROM ne_110m_admin_0_countries
WHERE ST_DWithin(
  ST_Transform(wkb_geometry, 3857),
  ST_Transform(ST_SetSRID(ST_MakePoint(-79.38, 43.65), 4326), 3857),
  500000
);

This reprojects both the table's geometry and an ad hoc point (Toronto, roughly) into a projected CRS (Web Mercator, meters) before comparing distance, since ST_DWithin on raw WGS84 geometry would otherwise be comparing distances in degrees, not meters.

Spatial indexing¶

Spatial queries against a large table are slow without a spatial index — PostgreSQL's GiST index type is what makes functions like ST_Intersects, ST_DWithin, and ST_Contains fast at scale by letting Postgres quickly rule out non-matching rows via bounding-box comparisons before doing the more expensive exact geometry check.

CREATE INDEX idx_countries_geom
ON ne_110m_admin_0_countries
USING GIST (wkb_geometry);

After loading any table of meaningful size, adding a GIST index on its geometry column should be one of the first things done — it's the single biggest lever for spatial query performance in PostGIS.

Troubleshooting¶

Drop and re-create the postgis database¶

sudo -u postgres dropdb postgis && \
sudo -u postgres createuser postgis -P && \
sudo -u postgres createdb postgis -O geouser && \
sudo -u postgres psql -d postgis -c "CREATE EXTENSION postgis;"