PostGIS Day 2023

In this talk, we will introduce PostgREST, a powerful open-source tool that can dramatically simplify the creation of custom PostGIS functions. PostgREST provides a RESTful API over a PostgreSQL database, enabling developers to quickly expose database functions as web services with minimal effort. This approach not only accelerates development but also promotes data accessibility and integration into modern web and mobile applications. During the presentation, we will explore the key features of PostgREST and demonstrate how to leverage its capabilities to create PostGIS functions within minutes. Attendees will learn how to: Set up PostgREST to work with PostGIS-enabled PostgreSQL databases. Expose and document PostGIS functions as RESTful APIs effortlessly. Utilize these APIs to perform geospatial operations from web and mobile applications. Enhance data security and access control through PostgREST's authentication and authorization mechanisms. Leverage PostgREST's scalability for handling high-demand geospatial applications.

PostGIS supports geometries with a Z dimension and geometries with M (measure) values, but there are not a lot of examples of both of these being used together. One use case is the analysis of airplane tracks which requires both - that is to say every vertex has an altitude and a timestamp. This talk will show how live positional data transmitted from aircraft can be accessed in a PostGIS database. I will then show how a sequence of these positions can be represented effectively as LINESTRINGZM geometries which can be analyzed as trajectories using native PostGIS functions. With spatial SQL, we can do things such as determine anomalous changes in an aircraft's velocity or altitude and find the exact point in time at which two aircraft came closest to one another. The focus on the talk will be showing how future work on large datasets of ADS-B data can be done using PostGIS and other open-source geospatial tools.

When computing the shortest path between two locations, the size of the roads dataset is often the number one impacting factor on performances. Let's discuss some ideas on how to reduce the size of this dataset, while preserving topology and attributes.

We present a solution to the problem of displaying the areas in a web map where no data (polygon data) is available. This problem comes up, for example, if polygons designate flood risk areas. One then wants to avoid interpreting an area that has not been mapped at all for flood risk as an area that has been mapped and found to have no flood risk. We present a solution using vector tiles and postgis.

MobilityDB is an open source data management and analytics platform for mobility data (https://github.com/MobilityDB/MobilityDB). Its core function is to efficiently store and query mobility tracks, such as vehicle GPS trajectories. It implements the Moving Features specification from the Open Geospatial Consortium (OGC). MobilityDB is engineered from PostgreSQL and PostGIS, providing spatiotemporal data management via SQL. It thus integrates with the PostgreSQL ecosystem allowing for complex architectures such as mobility stream and edge processing as well as cloud deployments. The presentation will explain the architecture of MobilityDB, its database types, indexes, and operations illustrated with several use case examples covering its use in multiple computing environments.

Aircraft flight tracks naturally lend themselves to being stored as moving objects, and the MobilityDB extension to PostGIS is used to store these tracks for analysis. These tracks can then be intersected both geospatially and temporally with airspace elements (called traffic management initiatives) and measured to determine the effectiveness of those air traffic procedures. The moving object analysis also allows for measuring flight path deviations which were a result of the procedures used.

Recent advances in geospatial analysis and the availability of digital maps have revealed the importance of urban form and built infrastructure as fundamental to understanding the vulnerabilities and vitality of global and local cities. Understanding that in the same way a stethoscope doesn’t diagnose disease, our tools are not our skill -- but they can help efficiencies and access. Let’s look at how I launched the Global Healthy and Sustainable City Indicators (GHSCI) software, along with a PostGIS spatial database used for processing and data management. And how OSM integrates with QGIS through PostgreSQL to complete the story of how our built infrastructure helps us tell the stories of our cities.

The database *is* the application. This is my very opinionated take on back-end programming. I discuss running Livewire, a Software-as-a-Service used by distribution engineers in electric utility companies to provide visibility to, and answers for the relevant questions they may have about the network. It also calculates reliability metrics such as SAIDI & SAIFI and does up and downstream energy traces on one or more phases. But this talk is not about what Livewire does or how useful it is. Instead I'm going to wax lyrical about building and running it on the PRAM stack along with pgrouting and using Postgrest as a very thin, but capable layer between the browser and the database. How feasible is doing *everything* in the database? Is it sustainable? can anyone do it? What about tests? How do you do versioning? And of course the question on everyone's mind: Will it scale? (Hopefully no one asks if it is Web Scale) Whereas my 2021 presentation, Hurricane Prediction Accuracy Analysis Using the PRAM Stack, was a whimsical take on Doing Everything in the Database (DEITDB), this is less so, as it involves customers and money. This is the direct successor to that 2021 talk and a spiritual successor to Brian Timoney's 2022 presentation: "Skating to Where The Puck Is Going To Be: Why SQL is the Future of Full Stack Analytics."

Let's review some of the most important DBA tasks involved in running a database.

SQL, and in turn spatial SQL, is fast becoming the leading language for analytics. More tools are adopting it from data warehouses, to DuckDB, to dbt, and beyond. I believe that PostGIS is and will be the central tool to any modern GIS infrastructure. In this talk we will show practical and ready to implement examples of how using PostGIS with emerging tools can help you get the most out of spatial SQL.

We present case studies in extending PostGIS with extensions: 1- Dissections for rearrangements and puzzles, 2 - hypergraphs and hypergraph drawings, and 3 - hyperbolic and elliptic geometry.

The UN OpenGIS initiative along with OSGeo Foundation organized the OSGeo UN Committee Educational Challenge where Challenge 2 was to create material for pgRouting. The challenge supports the objectives of the OSGeo UN Committee, i.e. promoting the development and use of open-source software that meets UN needs and supports the aims of the UN. pgRouting is not only useful for routing cars on roads but it can also be used to analyse water distribution networks, river flow or the connectivity of an electricity network. This presentation explains how pgRouting was used to address one of the three of the Seventeen Sustainable Development Goals developed during the OSGeo UN Committee Educational Challenge.

In 2018, NRGS moved Henry County’s 911 system from ESRI to PostGIS/QGIS. The setup follows the TN NG911 standard and uses a number of SQL scripts, QGIS Functions, and shell scripts to maintain and deliver data. Of course the question came up “What if all I have is Geopackage?”. This talk will cover the excitement, plusses, and minuses to running this setup with a geopackage.

You know about databases. You know about Geospatial. But accepting your identity around the office as merely 'SQL' || ['Guru','Rockstar','Nerd'] is holding your career back and costing you money. You're Superman stuck in a phone booth without a cape, or a Wonder Woman unable to spin. In this presentation I will live demo some features and functions of the PostGIS ecosystem while giving the audience alternative language that frames what we are seeing on the screen into vibrant value-creating propositions for which someone, somewhere will gladly open their wallet.

Polygonal coverages are a widely-used spatial data model. PostGIS recently added support for Simple Polygonal Coverages, which models coverages using regular spatial tables. This talk describes the model and compares it to other options for representing coverage topology. Coverage operations including validation, union and simplification are described. They are simple to use and provide excellent performance.

The most common cause of performance issues in PostGIS is data irregularity. Fortunately there are straightforward ways to address funky data to get cleaner faster queries. There are also a bunch of techniques from the non-spatial world, such as data pre-summarizing, partitioning and denormalizing that can be used to turbo charge your systems through slightly altering your data model and query assumptions. Even extremely large databases can be made high performance, if you are able to constrain your assumptions about the potential variability of query parameters, just a little.

This talk will cover various extensions that extend PostGIS. You'll learn what kinds of problems each extension is designed to solve and we'll showcase examples of their surprising use.

Ask us anything about PostGIS!