Using PostgreSQL, PostGIS, and pgRouting for street sweeping

Load, clean, store, and animate OpenStreetMap data for a street sweeping route-optimizer project
Schedule

Presented by:

8d6b3840ffad2a7aa8c02db30e1822b8

James E. Marca

Activimetrics LLC

Dr. James E. Marca earned his PhD in transportation engineering from the University of California, Irvine in 2002. He started working with PostgreSQL and PostGIS at that time to stash and process GPS data collected from in-vehicle data collection units streaming data over CDPD wireless modems. He continues to use PostgreSQL as a data store for research projects and websites, and has connected to PostgreSQL from Perl, Java, JavaScript, Python, Elixir, and R. Some of that code is available on github (https://github.com/jmarca)

No video of the event yet, sorry!
Download the Slides

In responding to an RFP to optimize and improve street sweeping routes for a small city in California, we developed a solver based on Google's Operations Research Tools. While the guts of the solver uses OR-Tools, virtually everything else about the project is made possible by PostgreSQL, PostGIS, and pgRouting.

In this presentation, I talk about the various ways PostgreSQL and friends made our solver possible. I provide specific examples of the SQL used to:

  • clean OpenStreetMap data (using WITH RECURSIVE)
  • convert one-way and two-way streets into a one-way network of curbs
  • convert the street network into its linegraph dual
  • save the solver output to a new table using Python
  • display the solver's routes using QGIS
  • produce a smooth animation using PostGIS aggregate functions and windowing

This presentation is intended to be of "medium" difficulty, but I cover more advanced SQL techniques like WITH RECURSIVE as well as more advanced PostGIS queries.

This presentation will be of interest to people who have thought about using OpenStreetMap data in their projects, or who would like a practical example of using PostGIS and pgRouting in a real project. It may also be interesting to Data Science types--although there isn't any machine learning, there is plenty of practical advice about cleaning up data and presenting results.

Note, the PDF slides I've uploaded are draft versions at this time, and the longer SQL sections do not fit in the PDF pages. Also, the animations do not work in the PDF. The current (draft) presentation can be viewed at https://activimetrics.com/presentations/pcp_2020.html.

Date:
Duration:
50 min
Room:
Conference:
Postgres Conference 2020
Language:
Track:
Case Studies
Difficulty:
Medium