What can you do with PostGIS?
Buy our books!
https://postgis.us/page_buy_book Latest books
I'm Regina Obe and I'm President of a database consulting company, an author, and a member of the PostGIS core development team. These are a list of my published books
Books I am working on are here. SQL in a Nutshell 4th edition is complete and will be out in print in 2 month, covers SQL:2016 and support or lack of in PostgreSQL, MySQL, MariaDb, SQL Server, and Oracle.
PostGIS related extensions
CREATE EXTENSION ...;
PostGIS is both an extension, pictured here, and an umbrella term for several different spatial extensions included in the PostGIS project source code. These are listed in the box labeled PostGIS 3+.
Then there are other extensions packaged separately that can use or rely on PostGIS in this other box. Lines drawn to extension within PostGIS they rely on. Dashed means the extension doesn't need PostGIS but can use it if available.
They all help you to solve problems about space, things and the interaction of space and things.
Why I started using PostGIS
I have land and projects. PostGIS ticked all the boxes.
Which gets us to the point of what can you do with
PostGIS. I started using PostGIS 20 years ago because I
was a data manager and I needed to manage an inventory of
city owned land which would be given out to create
projects like open spaces, shopping malls, affordable
We even had slivers of land inside buildings that
companies didn't know they didn't own until we told
That deserves a story for a later time.
These parcels of land, people needed to know where they
were, how big they were, what they were near by, and what
was their selling value given their proximity to other
things like transportation, parks, hospitals, and schools.
In addition they needed this information needed to sit
alongside things that weren't so spatial. It needed to
speak to data that lived in relational databases. Boring
stuff like project timelines,
who is working on construction, demographic data (before
and after project completion) to assess the impact the
city was making.
The standard way others did it was unpleasant to my
taste-buds. The popular solutions involved some gyrations
with desktop mapping software. This was something I
neither cared to learn or wanted to spend my time doing.
In short I was in a perfect storm and PostGIS came to the
And so my journey began with PostGIS and it's mothership
PostGIS understands data about location
Survey data about boundaries of land
Raster data (Thematic matrices
like population density, instrument data like temperature, soil acidity, elevation, aerial, pictures)
PostGIS speaks the language of space. It can take data
that defines locations in various industry standard forms
and tell you things like how big is it, how long is it,
what's the perimeter, what's the contamination level.
What's the elevation here.
PostGIS can use the key of space
With the key of space it can derive all sorts of information
Average sale price of an arbitrary area
Regions with highest traffic accidents
Income levels in your area
Distances from your area to closest high school, mall,
elementary school, hospital
Approximate location of this spot based on postal address
(using road network data)
Population impacted by outage
PostGIS adds a new kind of relational key - the key of
space. Now instead of just a join like equality you can
have a join about how far this thing is to something else.
There is an outage in this network of cable, who's
PostGIS helps you visualize data
Outputs data in numerous web-friendly and spatial standard ways
PostGIS supports more export formats than any other spatial database. Common favorites these days are Vector Tiles (exposed via ST_AsMVT) and ST_AsGeoJSON. 8857 WGS 84 / Equal Earth Greenwich
PostGIS helps you injest data
Input functions help consume spatial data in numerous formats
PostGIS is standards compliant
PostGIS is an extension to PostgreSQL, but also follows the SQL/MM (Multimedia) Part 3
standard. It is similar to below but unlike these, is an extension
rather than part of Db proper
MariaDB / MySQL
Most common relational databases systems have a spatial
component in them that follows a standard called
OpenGeospatial Simple Features for SQL which morphed into
an SQL standard called SQL/MM (The MM is for multi-media).
What sets PostGIS apart from these others is that PostGIS
is not a part of PostgreSQL proper where as the other
databases the spatial is baked in. This is both a good and
a bad thing. It's a bad thing because it's hard to explain
to other database folks who have spatial readily available
when they install their relational database.
"PostgreSQL follows the SQL/MM standard if you have
Luckily there aren't too many of those, so most
discussions end up being, let me show you how to use the
spatial in "Your relational database" and why it's not as
good as PostGIS and you should switch to PostgreSQL.
Where your is "Oracle, SQL Server, or MySQL".
PostGIS doesn't need to be a part of PostgreSQL because
PostgreSQL is easy to extend without touching the core and
most distributions of PostgreSQL make it easy to install
PostGIS. It allows PostGIS to move at its own pace which
is not completely aligned with PostgreSQL pace.
PostGIS is a de facto standard
PostGIS has become a de facto standard with many upcoming
relational databases adopting the PostGIS function names
(regardless of if they are defined in SQL/MM or not), well
because, many GIS people know how to use PostGIS already
and almost all spatial tools know how to work with
PostGIS models space in many ways
Flat, Round, Matrix, Declarative
geometry type (Flat) extension:
The flat space model. Space is a cartesian grid. Supports drawing of linestrings, polygons, 3D polygons, points, 3d points, collections of polygons, points, linestrings, Polyhedral Surfaces, and TINS
Basic geometric types
Polyhedral Surface Triangulated Irregular Network (TIN) The first is the flat-model which is called the geometry type. The geometry type models space as a cartesian grid and on that grid you can draw 2-dimensional objects such as linestrings, polygons, and points, 2d objects sitting in 3-d space (2d objects with elevation), or true 3-D objects called Polyhedral surfaces and TINS. Polyhedral surfaces are 3-dimensional objects formed by connecting polygons together. Similar to a regular polygon a closed surface can be marked as a solid. Triangulated Irregular Networks are surfaces formed using triangles. TINs come in both 2-dimensional and 3-dimensional forms, though the 3-D is more common.
geography type (round) extension:
Model of space as spheroid.
Takes into consideration the earth or any given planet whose spatial reference is defined in
spatial_ref_sys table. Geodetic (Geography) 4326 (WGS 84 Lon Lat) in geography
Picture from Wikipedia: http://en.wikipedia.org/wiki/Equirectangular_projection
The geography data type is the round model. It is also packaged as part of postgis extension. It uses a spheroid for modeling. Though it is mostly used to model earth, it can be used to model space on other planets.
raster type (matrix) extension:
Model of space as a flat numeric matrix (with cells (called pixels) that have values (on) or don't have values (off))
The 3rd model is the matrix model. This model is spun out as a separate extension called postgis_raster. postgis_raster includes a data type called raster.raster models space as a matrix, with cells called pixels. A raster object can have one or more matrices where each pixel in the matrix can have a numeric value. Each individual matrix is called a band.Bands in a raster need to share the same matrix cell structure.
topogeometry type (declarative) extension:
Defined in SQL/MM Topology-Geometry specs.
Spatialite has it too. Topology partitions space into edges, nodes, and faces. Then it says this thing called topogeometry is space composed of these edges, nodes, faces and other topogeometries (which are again just a bunch of edges, nodes,and faces). If two topogeometries have the same set of (edges,nodes,faces) then they are the same. (1,1,2,3) -> topology_id, layer_id, id, type
SELECT topo::geometry AS geom, (topo).*
The 4th type is called a topogeometry. A topogeometry is a geometry with seat-belts on. A topogeometry is tied to something called a topology. A topology defines all the edges, faces, and nodes that can form a topogeometry. topogeometry is most useful for people generating spatial data than consumers of spatial data. When editing a topology, topology ensures that objects that share boundaries or elements in common in the area you are editing remain to do so. So think about building roads, dividing land in to new parcels.
PostGIS packaged command-line tools
These are part of PostGIS, but often shipped by packagers in a separate package from PostGIS extensions.
shp2pgsql - Loads data from ESRI Shapefile format
pgsql2shp - Exports data from PostGIS format to ESRI shapefile format (for other formats use ogr2ogr (part of GDAL suite of tools) or ogr_fdw
raster2pgsql - Loads data into postgis raster from hundreds of different raster formats (thru the power of GDAL)
pgtopo_export - New in upcoming PostGIS 3.3.0, export topologies
pgtopo_import - Import topologies (New in PostGIS 3.3.0)
Where do you get PostGIS?
If you want to know what's coming in PostGIS 3.2, best
place is to check our development manual which is updated
whenever there are new features added to our development
If you'd like to try out new features, our windows
buildbot builds packages for PostgreSQL EDB Windows for
PostgreSQL 11-13. You can copy these binaries into a
development install to test.
Enable extensions PostGIS 3+
This is running in psql. If in pgAdmin just manually
reconnect to your gisdb. Example is gisdb but do for any
spatial databases you have.
CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=public,postgis,tiger,contrib;
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS contrib;
CREATE EXTENSION postgis SCHEMA postgis; -- core includes geometry / geography
CREATE EXTENSION postgis_raster SCHEMA postgis; -- raster type and functions
CREATE EXTENSION postgis_sfcgal SCHEMA postgis; -- extended support for 3D geometries
CREATE EXTENSION postgis_topology; -- topogeometry relational view of spatial data
CREATE EXTENSION fuzzystrmatch SCHEMA contrib; -- needed by tiger geocoder
CREATE EXTENSION address_standardizer SCHEMA contrib; -- for standardizing addresses
CREATE EXTENSION postgis_tiger_geocoder; -- geocoding and reverse geocoding using US TIGER data
How you upgrade
Check your installation
POSTGIS="3.3.0alpha1 3.3.0alpha1" [EXTENSION] PGSQL="150" GEOS="3.11.0dev-CAPI-1.16.0"
SFCGAL="SFCGAL 1.4.1, CGAL 5.3, BOOST 1.78.0"
PROJ="7.2.1" GDAL="GDAL 3.4.2, released 2022/03/08" L
LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER TOPOLOGY
Output full rows
json_agg( ST_AsGeoJSON(r.*)::json ) )
FROM recent_building_permits AS r;