The Open Source Geospatial Community, PostGIS, & Postgres
Regina Obe
Buy our books! https://postgis.us/page_buy_book
Latest books
I'm Regina Obe and I'm President of a database consulting company located in Boston, Mass, USA, an author of several books which revolve around Postgres in some shape or form
, and a member of the PostGIS core development team and steering committee as well as steering committee of several other open source geospatial projects.
Email: lr@pcorp.us
Books Coming I'm working on 2 new books with my husband Leo Hsu, pgRouting for network routing and the book of PostgreSQL which we expect to be bigger than our other PostgreSQL book,
Hopefully we will get out these eventually.
What is PostGIS?
PostGIS is a set of PostgreSQL extensions to manage and query spatial data in a PostgreSQL database. Spatial data is any data involving space and what inhabits space.
PostGIS is commonly used for managing and querying geographic data (often referred to as geographic information systems), such as the physical shapes of places, points of interest, and even climate information,
but it can be used to manage and query other things such as gaming envrionments,
yet to be built buildings, and even theoretical concepts of space, that don't lend to visual.
What's in a name?
Post-Jis
Post G I S
Post Gees
There is a long running discussion, of how do you pronounce PostGIS? Is it PostGIS, Post GIS, Post Gees, or something else.
For me it's a matter of context.
If I want to say it fast, I say PostGIS. If I want to make a statement about how PostGIS is not just about GIS at all, that it
is the successor to GIS and eating up the spatial data world
and folding it into the larger body of water we call data
, I call it Post GIS. I have heard it called Post Gees by a fellow PostGIS core team member, but I personally do not call it that.
Why PostGIS?
To make spatial data not special
Spatial Standards and SQL
Before PostGIS, there did exist standards gaining popularity that tried to bridge the gap between relational and GIS.
These are the OpenGeospatial Consortium. Eventually this work was folded into an addendum to the SQL standard called SQL/MM (SQL for Multi-media).
2001 PostGIS is born
To make spatial data not special
Every good tale has a backstory, and PostGIS has a very rich one. PostGIS was born in 2001 and is part of a larger movement called SQL/MM. SQL/MM is an extension of the SQL standard to handle multi-media such as geographic information data (GIS).
At the time, the idea of being able to query spatial data in a relational database, was a fairly novel thing
supported by only really pricey databases or extensions for pricey databases. The favorite open source database at the time MySQL did not even have this functionality.
PostgreSQL was already spatial, geographic and graphics were one of the first extended types it had, which descend back as far back as the Ingres days.
The gres stands for IN teractive Graphics RE trieval S ystem. Postgres one of the first types in its inception were circle, polygons, lines, and boxes.
The intersection of two worlds
I think of PostGIS as the love child between Postgres and the Open Source Geospatial community.
If we were to do a DNA test on PostGIS, we would fine not just one thing but several key things under it's hood.
It's head is mostly Postgres which allows it to live in a Postgres database. It's heart is GEOS (Geometry Open Source engine) which provides all the intersects, intersection functionality and geometric processing like buffers and hulls.
It's feet is made of GDAL to power those matrices and allows it to understand 100s of different raster formats and import them. It also has CGAL (Computation Geometry Algorithm) to
power much of it's 3D analytics, and finally it's navigation sysem is PROJ to align all thst knowledge to a position in space, and also reproject that spatial orientation into other grids.
Open Source Geospatial, PostGIS, and Postgres
PostGIS is part of a much bigger community - the Open Source Geospatial community and more specifically the Open Source Geospatial Foundation (OSGEO).
PostGIS under the hood, is a set of projects working together, GDAL, GEOS, PROJ that help power the Engine we know as PostGIS.
In addition to those foundational components, PostGIS would be nothing without the rendering tools that grew up with it such as MapServer, QGIS and Geoserver, as well as many others
not listed here.
The existence of PostGIS helped drive the movement of another project, called OpenStreetMap. Though it originally built on MySQL, it eventually moved to PostgreSQL for improved transactional support
and to have it's map renderers (which relied on PostGIS), to be under the same DBMS as it's main database.
Spatial data is not special?
What? Not Only Special
You can use PostGIS anywhere you can use SQL and Postgres. If you know SQL, you know 90% of what you need to know about PostGIS.
.NET, C, Go, Java, NodeJS, PHP, Python, R, Rust, SQL , and more
But you can also use fancy tools to look at it in its full glory
Sure spatial data is often a binary blob of goo which is very special, but the idea was to give it a mouthpiece, so that it could
be queried with the same tools you'd use to query other data, and of course, you could still use specialized tools for deeper interaction.
Example PostGIS query 1:
What are the 3 closest roads to me?
SELECT road_name, s.geog <-> ST_Point(:param_longitude, :param_latitude)::geography AS dist_m
FROM roads
ORDER BY dist_m DESC LIMIT 3;
Sure spatial data is often a binary blob of goo which is very special, but the idea was to give it a mouthpiece, so that it could
be queried with the same tools you'd use to query other data, and of course, you could still use specialized tools for deeper interaction.
Example Query 2
Summarize Crime stats for NYC Boroughs
SELECT b.boro_name, count(*), c.type
FROM nyc_boros AS b
INNER JOIN crimes AS c ON ST_Intersects(b.geom, c.geom)
WHERE c.year = 2020
GROUP BY b.boro_name, c.type
ORDER BY b.boro_name, c.type;
PostGIS raises the bar for RDBMS With pressure from their communities, other relational databases were pressured to support spatial directly. Oracle was pressured, to provide more functionality for its light-weight built in locator, cause PostGIS
was giving this away for free (and in direct competition with Oracle spatial).
PostGIS models space in many ways
Flat, Round, Matrix, Relational (Declarative)
PostGIS provides several models for managing objects in space. I'll call those the Flat, Round, Matrix, and Relational (Declarative) models.
Flat: PostGIS geometry type
extension: postgisSpace is a cartesian grid. Supports drawing of linestrings, polygons, 3D polygons, points, 3d points, collections of polygons, points, linestrings, Polyhedral Surfaces, and TINS
Geometry type, part of postgis extension, models space like a cartesian grid much like you learned in geometry class in school. You draw lines, polygons, and points on a flat grid.
In addition supports 3D objects which you are drawing on a flat grid. The polyhedral surfaces and TINS they are closed can be marked as Solid so will have volume.
Round: PostGIS geography type
extension: postgisThe Round space model. Space is the surface of a spheroid where you can draw linestrings, polygons, points, and collections of polygons, points, linestrings on the surface.
The geography type, also part of core postgis extension, allows you to draw lines, polygons, and points and collections of those on a spheroid such as earth.
It's more limited in what you can draw on it than geometry and also a bit slower once you start dealing with linestrings and polygons, but it is easier to work with for most folks since coordinates are stored in longitude, latitude like you
get from GPS devices, so most newbies gravitate to it.
Matrix: PostGIS raster
extension: postgis_rasterModel of space as a numeric matrix (with cells (called pixels) that have values (on) or don't have values (off)).
Within that are things called bands that define the number of matrices you have stacked.
Elevation
Soil
Weather
Fire
Aerial
Then there is the matrix model, we call raster, which I consider the most fun. Think of a numeric matrix, think of you taking that matrix
and overlaying it on space in some location on earth (or imaginary location), and then take that and stuff values in each cell. How many values you stuff is determined by what we call
the number of bands of the raster, essentially you are stacking matrices. If a raster has 3 bands means you can store 3 values. Very popular for storing elevation, climate, slope.
Relational: PostGIS topogeometry type
extension: postgis_topologyDefined 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).*
FROM some_topo_table;Then there is the relational declarative model, which many think is the true model we should have been using all along, but tends to be slower.
It's used for what I'll call cadastral work, where you really care that things border each other really border each other. The reason I call it relational, is the topogeometry which is the object of interest
is modeled as a set of edges, nodes, and faces, so it can share elements with another topogeometry and you can have hierarchies, like this state is composed of these counties. PostGIS helper extensions
postgis_sfcgal - functions for volumetric and 3D surfacepostgis_tiger_geocoder - geocoding and reverse geocoding using US Census TIGER dataaddress_standardizer, address_standardizer_data_us Rule-based address standardizer (useful as precursor for geocoding)
PostGIS also provides helper functions, which don't introduce new datatypes, but have many functions that build on postgis extension packaged types.
The postgis_sfcgal introduces the idea that a closed 3D surface can be solid, and functions for handling solids as well as analysis on 3D surfaces and creating 3D geometries.
The postgis_tiger_geocoder only good for US and uses US Census TIGER data for geocoding/reverse geocoding.
The address standardizer can take addresses that are the same but expressed slightly differently and express them the same. It will also break an address into it's constituent parts like Address Number, Unit, Street etc.
It is often used as the pre-cursor to geocoding. PostGIS extended family of extensions
CREATE EXTENSION ...;
Thank you
You can reach me at:
Email: lr@pcorp.us
Mastodon: @robe@mapstodon.space
LinkedIn: https://www.linkedin.com/in/regina-obe-2000057/
Twitter: @reginaobe
