Hallo, herzlich willkommen zum nächsten Vortrag. Diesmal werden Marion Baumgartner und Julian Haffner uns etwas erzählen zu geografischen Postgres-Erweiterungen, Pidgey-Rooting und Postgis,

bei Fragen nach Möglichkeit über den QR-Code stellen, damit wir die online haben und nicht durch den ganzen Raum laufen müssen mit dem Mikro. Danke. Danke für die Einleitung. Hallo zusammen. Wir werden heute euch zwei Themen näher bringen. In 20 Minuten sind das sehr große

Themen, so wir werden uns wirklich auf Beispiele und die eine grobe Einführung beschränken. Das sind die zwei Elefanten, die ich euch vorstellen möchte. Das eine ist Postgis und das zweite ist

Pidgey-Rooting. Bevor wir jetzt beginnen, möchte ich noch ein paar Worte zu uns sagen, dass wir nicht ganz anonym sind. Den Vortrag werde ich zusammen mit Julian Haffner halten. Ich bin Marion Baumgartner. Wir arbeiten für die Firma Camp2Camp. Ich bin Entwicklerin. Julian ist

Consultant, GIS Consultant und wir arbeiten im Standort Olten in der Schweiz, deshalb vielleicht auch ein bisschen mein Akzent. Wir sind gut 190 Mitarbeitende, alle zusammen und haben

Standorte in Frankreich, in der Schweiz und in Deutschland. Als erstes werde ich, ich glaube das ist wieder zurück, werde ich kurz motivieren, wieso wir das Routing und das Postgis in

der Datenbank machen, respektive wieso brauchen wir Postgres. Danach wird uns Julian etwas erzählen über das Postgis und am Ende kommen wir dann zurück auf das Pidgey-Rooting. Wieso

Postgres? Postgres hat eine extrem lange Geschichte. Es wurde vor mehr als 20 Jahren entwickelt, wird stetig weiterentwickelt. Es hat eine sehr große Community, eine wachsende Community, wie man auch sieht in den Konferenzen, die haben mehr als doppelt so viele Leute zum

Teil, als vor fünf Jahren noch. Und wenn man in der professionellen Welt rumschaut, zum Beispiel auf der Stackoverflow-Statistik, sieht man auch, dass es im Jahr 2023 als eine

der beliebtesten und die populärste Datenbank galt. Wieso schaue ich die Statistik von Stackoverflow an? Der Grund dafür ist, es ist nicht ganz einfach eine unabhängige Bewertung zu finden

und bei Stackoverflow gibt es die Möglichkeit, dass jeder seine Meinung dazu geben kann. Also man könnte sagen, es ist eine mehr oder weniger unabhängige Statistik, die einen

guten Einblick gibt, in wie es einem Tool geht. Für Postgres sieht es im Moment recht gut aus. Wir haben also eine solide Basis und was noch weiter wichtig ist, ist wir

können Postgres erweitern. Postgres ist so aufgebaut, dass man Extensions hinzufügen kann, wenn man sie benötigt, wie eben zum Beispiel PG-Routing oder PostGIS oder weitere, wie es unzählige gibt. Ich gebe jetzt das Wort an Julian. Er wird jetzt weitermachen

mit PostGIS. Danke Marion. Ich komme nun zum ersten Elefanten, den wir euch vorstellen, also dem PostGIS-Elefanten. PostGIS ist wie gesagt eine Extension für Postgres. Wenn

die Abhängigkeiten installiert sind, kommt man ganz einfach mit Create Extension, PostGIS, kommt man zu einer GIS-Datenbank von einer Postgres-Datenbank. Was heißt das jetzt nun eine GIS-Datenbank? PostGIS erweitert die Postgres-Datenbank mit Spatial Data

Types. Das sind zum Beispiel Geometrien wie Point, Line, Polygon oder auch Multi-Point, Multi-Line, Multi-Polygon oder auch Raster. Für diese neuen Datentypen gibt es nun auch Indexe. Wer schon mal hinter einer Datenbank gearbeitet hat, ich nehme an, das

sind die meisten von euch, weiß, dass komplexe Abfragen in einer Datenbank dazu auch Indexe benötigen oder komplexe Anfragen verschnellern. Und wenn es jetzt neue Datentypen gibt oder vor allem sehr komplexe Datentypen, dafür gibt PostGIS neue Indexierungsmöglichkeiten.

Und dazu gibt es noch die Spatial Functions. Die fangen immer mit ST an, die Spatial Type Functions. Ich werde das jetzt konkreter an drei Use Cases erklären. Das erste Use Case, das wir

haben, ist ein Datensatz. Wir haben hier einen Datensatz eines Kunden mit verschiedenen Verkaufsstellen in der Schweiz. Das ist ein Table. Irgendwo gibt es ein Data Type Point, also es gibt Punkte mit Daten dazu, wie Verkaufsdaten, Umsatz. Gleichzeitig gibt es

eine regionale Unterteilung. In der Schweiz sind das hier jetzt die Kantone und wir möchten eine Auswertung der Punkte pro Kanton. In der Datenbank haben wir diese beiden Tabellen.

Wir haben eine Tabelle mit Verkaufsstellen, mit Punkten, eine Tabelle mit Polygonen, aber kein Attribut, das die beiden verknüpft, außer die Geometrie. Und wir können jetzt durch eine solche Spatial Function, zum Beispiel hier mit ST Intersects, können wir so

die beiden Tabellen mit einem Join ziemlich einfach verknüpfen. Wenn wir das verknüpfen, können wir danach auch die gängigen Postgres-Aggregationsfunktionen wie Summe, Average usw. verwenden. So können wir eigentlich ziemlich einfach Daten mit

den Geometrien verknüpfen. Wir sehen, wir bauen hier die Data Types wie Point und Polygon und auch eine Spatial Function. Ein anderes Beispiel, geometrische Daten können sehr

schnell sehr komplex werden. Besonders mit Polygonen, also wer schon mit Real-Life-Data gearbeitet hat oder mit Daten aus amtlichen Vermessungen, da gibt es schnell mal eine Million Vertices auf einem Polygon und um solche Daten darzustellen oder zu renderen, kann zum Teil ganz schön lange dauern. Wir sehen hier verschiedene Auflösungen und

eine Vereinfachung der Geometrie. PostGIS hat eine Funktion, die Simplify Funktion, die ziemlich einfach ist, um eine Geometrie zu vereinfachen. Das heißt, es werden

weniger Vertices verwendet. Wir sehen die verschiedenen Auflösungsstufen und wir können auch dann zum Beispiel die Funktion mit unterschiedlichen Toleranzwerten verwenden. Also man kann einen Toleranzwert eingeben, wie stark vereinfacht das Polygon werden

soll und wir sehen, je weiter rein gezumpt wird, desto eher sieht man die Vereinfachung. Also bei der maximalen Zoom-Stufe braucht man dann vielleicht die originale Geometrie, aber wenn man raus gezumpt ist, kann man auch eine vereinfachte Geometrie darstellen

eigentlich und hat somit den Vorteil von der schnelleren Darstellung. Es gibt auch eine Funktion für Distanzanalyse. Hier haben wir die Distanzanalyse, also es gibt die Funktion

STDistance, um diese Distanz zwischen Geometrien zu berechnen, wie ein Punkt und ein Polygon. Hier verwenden wir das, um Daten zu korrigieren. Wir wollen hier auch wieder Punkte einem Polygon zuordnen, aber wir sehen hier, ein Punkt liegt außerhalb des Polygons,

wir wollen diesen Punkt aber dem nächsten Polygon zuordnen und mit STDistance können wir so herausfinden, welches das nächste Polygon zu dem Punkt ist und können so diesen Punkt, diesen Polygon zuordnen. Um Distanzen zwischen Punkten oder auch Orten zu berechnen und das

auch in Roots darzustellen, gibt es noch den nächsten Elefanten, den euch Marion nun vorstellen wird. Das ist auch schon der letzte Elefant von all den Elefanten, das PG-Routing.

Es ist grundsätzlich sehr geeignet, um Routen zwischen Punkten zu definieren. Das heißt, im Moment haben wir noch nicht unbedingt die geografische Gegebenheit mit einbezogen. Es

ist durchaus möglich, zwischen Netzwerken das Routing einzustellen. Ich werde nachher noch einige Beispiele zeigen. Es ist im Normalfall sehr, sehr oft gegeben, dass man auch einen geografischen Bezug hat und nicht einfach nur ein Netzwerk. Wie schon beim PG, beim PostGIS,

es ist auch eine Extension. Wenn man sie mal installiert hat, kann man sie einfach innerhalb von der Datenbank aktivieren. Es basiert im Prinzip auf Nodes, Vertices. Man kann mit Orientierung und Kosten arbeiten. Orientierung heisst, in eine Richtung geht

es, in eine andere Richtung geht es nicht. Kosten heisst, ein Vertex kostet mehr als ein anderen. Wenn man das in der Datenbank anschaut, hier ein Beispiel von einem Select,

wie das mit einer Funktion von PG-Routing gemacht wird. Die Funktion beginnt immer mit PGR und dann übergibt man im Prinzip das Select-Statement, das man wählt, um das Netzwerk zu bauen. Ich habe jetzt hier ein Beispiel, wo man versucht von 7

nach 12 zu kommen und wenn man das mit dieser Funktion auswertet in der Datenbank, dann bekommt man erstens einen Weg, der dann von der Datenbank mehr oder weniger so

zurückgegeben wird. Es gibt eine Sequenz, das ist einfach eine Laufsequenz, die durchnummeriert wird. Dann gibt es die Nodes und die Edges, die abgelaufen werden und am Schluss, was es gekostet hat, von den Punkten weiterzukommen. Das ganze war jetzt vielleicht ein bisschen

trocken und unanschaulich, deshalb habe ich euch noch ein paar Beispiele mitgebracht. Das sind wirklich Beispiele, die wir so in Projekten umgesetzt haben und das, was hier benutzt wird, also die Basis, das sind die Hier-Daten von ganz Frankreich,

die wurden im Postgres reingeladen. Das sieht dann in etwa so aus, wenn man es auf einer Karte veranschaulicht. Das sind etwa 11 Millionen Straßensegmente und für jedes

Parameter wie die Distanz, dann ist es befahrbar mit einem Lastwagen oder ist es nur für Fußgänger. Weiter gibt es auch noch verschiedene Beschränkungen. Für

zum Beispiel gelten normale Regeln oder gibt es gewisse Regeln, die bei einem Notfall nicht beachtet werden müssen. Im Endeffekt, die Performance-Requirement von diesem Projekt war, dass alle Queries unter einer Sekunde laufen mussten. Für längere Queries oder längere Routen waren fünf Minuten akzeptabel. Eine Sekunde ist sicher besser.

Wenn man da noch ein bisschen reinzoomt, sieht man, dass das Straßensystem doch recht komplex ist. Das erste Beispiel, das gemacht wurde, ist für einen Notfall, wenn es irgendwo

ein Feuer gibt, die schnellste Route zu berechnen, die ein Feuerwehrauto hat, um zu diesem Notfall zu kommen. Dafür wurde der Dijkstra-Algorithmus, der automatisch

mitkommt, mit dem PG-Routing verwendet, sowie auch das Turn-Restricted Shortest Path. Das wäre das erste. Sprich, da gibt es auch Restriktionen, ob man einen Turn nehmen

darf oder nicht. Weiter wurden auch Isochrone und Iso-Distanzen berechnet, beraten schon im letzten Vortrag. In diesem Beispiel werden die Isochrone in der Datenbank berechnet.

Die Idee ist, dass, wenn man zum Beispiel einen Einbruch hat, wo der rote Punkt ist, dass man berechnet haben kann, wie weit kommen die Flüchtenden, in welcher Zeit,

wenn fünf Minuten vergangen sind, welcher Radius muss man dann abriegeln. Das wurde mit der PG-R-Driving-Distance-Methode gemacht. Was sie zurückgibt, sind Nodes und Edges und

ein Prinzip Punkte, welche danach mit PostGIS in einer Colnex-Hull-Funktion, also im Prinzip nimmt man die Punkte und stülpt so wie einen Schrumpf-Schlauch darüber und bekommt dann ein Polygon und das sieht dann eben am Ende so aus, wenn man es visualisiert. Für jeden

Ring sind zwei Minuten vergangen und die Distanz da drin ist das, was das Mögliche ist, also wie weit sie kommen. Ja, weshalb, was sind die Motivationen PG-Rooting zu verwenden?

Grundsätzlich ist es eine sehr mächtige Funktionalität. Der große Pluspunkt ist, dass es innerhalb von der Datenbank läuft, also die Daten müssen nicht irgendwie noch aus

der Datenbank herausgezogen werden und an eine Routinefunktion weitergegeben werden. Es gibt extrem viele Algorithmen, wir haben gesehen, es gibt Dijkstra, es gibt auch den Traveling Salesman Algorithmus, der implementiert ist, das Turn Restricted Shortest Path und viele, viele mehr. Es gibt auch Kostparameter, die man anpassen kann in den Daten selber und man

kann extrem viele Tools auch anhängen, um dann die Visualisierung darzustellen. Und der Pluspunkt ist, man hat PostGIS, dass man zusammenhängen kann mit PG-Rooting,

was ein extrem mächtiges Tool gibt, um Auswertungen zu machen, entweder vorher, bevor man die Daten verwendet und ins PG-Rooting reinsteckt oder man kann auch im Nachhinein dann die Daten aus dem PG-Rooting rausziehen und dann weiter verarbeiten mit

PostGIS. Es gibt eine extrem lange Geschichte für beide Extensions, sie sind open source, sie werden weiterentwickelt, es gibt große Communities und Interesse auch das weiter zu treiben und zu verbessern und ein Pluspunkt, den ich sehr, sehr schön finde, ist, dass es

eine große Dokumentation gibt, die einem den Einstieg vereinfacht und sie auch wirklich zugänglich ist für die allgemeinen Menge. Ja, dann kommen wir ans Ende. Wir bedanken uns fürs

Zuhören und sind offen für Fragen. Vielen Dank für den interessanten Vortrag. Bisher

sind drei Fragen da, also gerne noch welche stellen. Können im Rooting komplexere Regeln wie zeitabhängige Zugangsbeschränkungen oder multimodales Szenarien berücksichtigt werden? Ich habe jetzt gerade nicht so das Bild, was genau gemeint ist mit der

Frage, aber grundsätzlich würde ich... Ich könnte mir zum Beispiel vorstellen, dass eine Straße nachts zwischen 10 und morgens 6 Uhr gesperrt ist, dass die dann eben umgangen wird während dieser Zeit, aber sonst genutzt wird. Ja, das kann berücksichtigt werden, auch über die Kosten und die Kosten, also ich habe es glaube ich, hier ist es erwähnt,

die Kosten können auch dynamisch angepasst werden. Das könnte man dann über das steuern oder man kann es eben im Vorfeld schon restriktieren, dass es dann zeitlich nicht befahren wird oder nicht zurückgegeben wird. Berücksichtigt die Posgis Simplify Funktion die Lagegleichheit

zwischen zwei angrenzenden Polygonen, Stichwort Topologie, das heißt wird so simplifiziert, dass keine Lücken oder Überlappungen an Geomikrigrenzen entstehen? Nein, aber man kann

das ST, es gibt ja eine Snapping Funktion, also mit der Snapping Funktion, wenn man die Snapping Funktion plus Simplify kann man das umgehen vielleicht, aber an und für sich, wenn du nur Simplify benutzt, können Lücken entstehen. Welchen Vorteil bietet PG Routing im

Vergleich zu ORS? Na ja, das PG Routing, ich würde jetzt nicht sagen, dass das eine oder das andere besser hat, das hat im Prinzip Vorteile und Nachteile, die man abwägen muss, je nachdem was man möchte. Ich denke der klare Vorteil ist, dass es in der Datenbank

drin ist, dass es als Extension aufgeschaltet werden kann und dadurch halt eben keine Daten aus der Datenbank fließen müssen, die man an ein anderes Routing übergeben muss respektive. Es gibt da nicht noch eine weitere Schnittstelle, man kann das direkt in der Datenbank machen,

was dann eben auch den Vorteil bringt, dass man das PostGIS dann weiterverwenden kann für die Auswertungen, falls das notwendig ist. Im Prinzip, es kommt halt immer darauf an,

was man am Ende machen möchte und was das Ziel ist. Ob jetzt das eine vorteilhafter ist oder das andere, das kann ich so nicht beantworten, ohne das Ziel zu kennen. Mit welchem Viewer wurden die Daten hier in der Präsentation für die Folien gezeigt? Zum Beispiel die Frankreich-Karte. Die Frankreich-Karte, ich denke das

war eine von Kugis, das ist ein Screenshot, wenn man das mit Kugis anhängt und dann zum Beispiel weiter oben bei PostGIS, das ist auch Kugis, also wir haben da vorwiegend Kugis verwendet,

um das darzustellen. Postman wird zum Teil auch stark verwendet, aber so grundsätzlich, was ich empfehlen kann, ist Kugis, was auch gibt, ist für die, die PG-Admin kennen,

PG-Admin hat auch einen Viewer mit drin, wo man geografische Daten darstellen kann. Könnte der auch so eine komplexe Karte darstellen? Ja, also im PG-Admin kann es einfach eine Tabelle aufs Mal wird dargestellt, das ist nicht sehr komplex. Falls es bei DARF ist für Komplexität,

wo man mehrere Layers und mehrere Tabellen auf einmal darstellen möchte, dann Kugis. Können auch Traveling-Salesman-Probleme mit mehreren Fahrzeugen gelöst werden?

Ich denke, ja, haben wir jetzt nicht gemacht. Ich habe da nicht so viel Erfahrung mit mehreren Fahrzeugen, aber ja, sollte durchaus möglich sein. Beschäftigt ihr euch auch mit dem Datenimport von USM to PG-Rooting? Gibt es eine Möglichkeit, die Datenqualität zu prüfen?

Ich habe mich ein bisschen damit beschäftigt, nicht sehr ausgiebig. Die Datenprüfung ist nicht ganz einfach. Das, was ich bis jetzt gesehen habe, ist, dass man das effektiv genau anschauen muss und dann halt die Daten mehr oder weniger von Hand prüfen muss. Bis jetzt habe

ich noch nicht so viel anderes gefunden, aber falls es was gibt, bin ich da offen, um das auch zu erfahren. Was ist der Unterschied zwischen den Datentypen Geometrie

und Geografie? Willst du beantworten oder soll ich? Im Prinzip ist es eigentlich, dass das Grundsystem des Geometrie, also die Geografie wird im Moment,

ich glaube, haben wir nicht da irgendwo noch etwas. Also im Prinzip ist es eigentlich Bezugssystem, womit gearbeitet wird. Bei den Geometries kann man das Koordinatensystem setzen

und die Geometries gehen von einem anderen System aus, also da wird auch mehr in Meter gerechnet und das wird dann umgewandelt. Lange war es nicht ganz so gut supportet wie die Geometries. Mittlerweile gibt es sehr viele Funktionen, die auch in Geographies

funktionieren. Es wird immer mehr. Müssen die Ausgangsdaten in der Datenbank in einer bestimmten vorgegebenen Struktur vorliegen? Auf was bezieht sich das?

Im Prinzip braucht es für Post-Ges die Geometrien, die müssen in einer Tabelle oder in einer Spalte sein und in der Regel ist es auch gut, wenn die einer Struktur folgen,

dass man sagt, man hat vielleicht pro Spalte nur ein Geometrietyp, also Linien sind in einem, damit man das nicht irgendwie dann durcheinander bringt. Sie macht es auch einfacher damit zu arbeiten und die restlichen Daten, also die Attribute,

die auf die Geometrien kommen, die spielen eigentlich keine Rolle. Was da mit reinkommt, das kommt am Ende darauf an, was man rausziehen möchte auch, ob die eine gewisse Struktur haben oder nicht. Man kann auch einfach alle Attribute in eine JSON-Struktur schreiben.

Macht es wahrscheinlich nicht einfacher damit zu arbeiten, aber technisch ist es möglich. Was sind Nachteile von Pidgey Routing? Was sind Nachteile? Ein Nachteil ist sicher, dass die, wenn ich hier etwas weitergehe,

wenn wir diese Funktion anschauen, wir sehen das Select Statement, das da mit drin ist und die

ganze Optimierungsgeschichte ist nicht ganz einfach. Also wenn die ganze Funktion langsam ist, das zu optimieren und rauszufinden, wo kann ich dran schrauben, wie wähle ich das Select Statement, das ist eine aufwändige Sache. Ich würde sagen, das ist so der Hauptnachteil daran.

Könnte man Pidgey Routing für Zugfahrpläne verwenden? Ja, könnte man. Im Prinzip ist es ein Netzwerk, wenn man das Netzwerk hat und die

Ja, das waren die Fragen von hier. Gibt es noch welche aus dem Saal? Ich stimme dir zu, die Dokumentation ist perfekt. Die Dokumentation von Pidgey Routing ist wirklich sehr gut.

Ich hatte einen Fehler gefunden und bisher bin ich mit dem Support noch nicht ganz zurechtgekommen mit den Mails. Hast du da Erfahrungen? Also grundsätzlich die Mailing-Liste ist sicher ein guter Anfang, also jetzt spezifisch für Pidgey Routing

oder für Postgres. Ja, es gibt extrem viele, es gibt auch verschiedene Kanäle und verschiedene Mailing-Listen.

Ich bin nicht sicher, welche die beste ist. Es gibt auch, was es auch gibt, sind die verschiedene Chat-Kanäle auf Slack, glaube ich gibt es einen und man muss wie so ein bisschen reinkommen, um zu wissen, was man auch kann. Es gibt viele Dienstleister, die Support anbieten.

Das ist eventuell auch eine, je nachdem, Lösung. Die Community ist wirklich sehr groß und es macht es halt dann nicht einfach, rauszufinden, was wo zu fragen, aber ich denke in diese Richtung könnte man eine Lösung finden oder halt eben, ja wenn es ein Fehler ist, den man selber bewegen kann,

dann sie sind auch sehr offen für Unterstützung. Es ist offen und die Leute haben auch beschränkte Zeit. Aber eben wie bei Postgres, es gibt extrem viele Dienstleister, die auch

Dienste anbieten, um da Support zu stellen oder bug fixes zu machen oder was man dann braucht. Vielleicht noch als Ergänzung dazu, wir von Camp2Camp haben ja auch einen Stand hier, also falls ihr noch weitere Fragen habt, könnt ihr gerne auch da Fragen stellen können.

Dann vielen Dank.