PostGIS family of extensions
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 solves spatial problems
PostGIS helps you analyze, manage, mold, and visualize space.
So what does PostGIS do? It helps you solve spatial problems with new data types and functions for analyzing, managing, molding and visualizing space.
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.
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)
Need to geocode US data?
Geocoding and Reverse Geocoding using US Census Tiger data.
Loading the data
SELECT ST_AsText(g.geomout), pprint_addy(g.addy)
FROM geocode('1600 Pennsylvania Avenue NW, Washington, DC 20500', 1) AS g;
st_astext | pprint_addy
POINT(-77.03511478601034 38.89867093615472) | 1600 Pennsylvania Ave NW, Washington, DC 20500
FROM reverse_geocode(ST_Point(-77.03511, 38.89867)) AS rg;
692 E Executive Ave NW, Washington, DC 20006
Power of CGAL in PostGIS via the
SFCGAL library. Used for advanced 3-D and 2-D processing. Many functions specifically targeted for Polyhedral surfaces and TINS
New in PostGIS 3.3.0
CREATE SCHEMA postgis;
ALTER DATABASE gisdb set search_path=public,postgis;
-- included with postgis
CREATE EXTENSION postgis SCHEMA postgis; --only vector in 3.0
CREATE EXTENSION postgis_raster SCHEMA postgis; -- raster separate in 3.0
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch; -- needed by geocoder
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_sfcgal SCHEMA postgis;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us; -- lex and rules
Uses graph algorithms:
edges have costs, nodes connect edges.
Find shortest path consider costs (road network, one way, toll price etc).
Maximum service area
Decision trees - compute most favorable path given where you are in the tree.
The reason you might need pgRouting is because the world is filled with obstacles. PostGIS can answer the question of what is the shortest distance between here and New York if you happened to be a bird. But the reality you have to go thru roads, and then blocked roads. pgRouting to help you route around obstacles. Used for geospatial mostly, but fundamentally a collection of graph algorithm functions. Types of functions: dijkstra, a-shooting star, k shortest-path, traveling salesman
Analysis and managing spatial movement in a compact way. Follows the
OGC Moving Features spec. Extends postgis geometry and geography types with time elements. Raw data often comes in General Transit Feed Specification (GTFS) format and GPX (that stuff from your GPS device). Extends postgis geometry and geography types to form temporal spatial types:
tgeogpoint, and many others.
MobilityDB is a newer project, just reached it's 1.0 milestone. Specifically designed for analyzing moving objects. Includes numerous types include several spatial temporal types that extend the PostGIS geometry and geography types.
Load data in using another open source tool
PDAL: Point Data Abstraction Library
What are point clouds? a bag of fat points collected by satellites, drones, planes, 3d scanners,
and used to develop other forms of data: spatial data formats (particularly 3D models).
Foreign Data Wrappers
Foreign data wrappers allow you to query data not in your database as if it is a table in your database.
oracle_fdw: Oracle sdo_geometry -> postgis::geometry
ogr_fdw: spatial anything -> postgis::geometry
Helps you link to data about things and space. Let's you pretend these things are part of your database.
ogr_fdw (mega foreign data wrapper) extension:
Links external (sometimes spatial) data in PostGIS/PostgreSQL format.
It is an abstraction: this data over there is a table in my database.
If you have spatial data it can link it.
If you have non-spatial data, it can link it.
If you have no clue what this random file someone gave you is, it can probably link it.
ogr_fdw is a mega foreign data wrapper. It can import many many different kinds of data. It is a thin-wrapper around the Geospatial Abstraction Library (GDAL) library. Although it is considered a spatial data wrapper, it can import many non-spatial formats as well. I use it more for loading non-spatial data such as spreadsheets or folders of CSVs. It also has this nifty feature via GDAL called a virtual file system. So it can link in files on the web http or files within zip files using this feature.
Uber's Hexagonal tiling scheme as a PostgreSQL SQL API. Leverages PostGIS geometry support.
Exposes Uber's H3 library as an SQL API and the hexagons as PostGIS polygons.
PostGIS 3.3.0alpha1 released last week
Works with PostgreSQL 11-15beta1. Packagers have started or finished packaging it.
Can't enable in database with
CREATE EXTENSION unless have binaries
Available from packagers: (apt.postgresql.org, yum.postgresql.org, Windows Stackbuilder, bsd ports, homebrew)
postgis packaged extensions, pointcloud/pointcloud_postgis, pgrouting, ogr_fdw, oracle_fdw
Support via Database As a Service (DBaaS): postgis extensions (except sometimes not postgis_sfcgal), pgrouting (a lot of the time, but popular ask when not), oracle_fdw (sometimes), ogr_fdw (rarely)
Need to compile yourself or use provided container setups: - mobilitydb,pgh3