Community • 18. Dezember 2023

PostGIS wünscht frohe Weihnachten!

Der Weihnachtsgruß - Text und Sterne - im Titelbild dieses Beitrags werden in PostGIS on the fly generiert. Es werden keine Geodaten benötigt und für die Ausgabe müssen weder Zwischentabellen noch Funktionen in der Datenbank gespeichert werden. Ausgangspunkt waren ein paar Spielereien mit ST_Letters, das seit PostGIS 3.3 zur Verfügung steht und aus übergebenen Zahlen und Buchstaben WKB-Polygone generiert. Jacob Coblentz hat dazu einen schönen Blog-Artikel veröffentlicht [1]. Schon das Experimentieren mit der ST_Letters-Funktion macht Spaß. Die Sterne aus dem Nichts zu erstellen war allerdings die größere und interessantere Herausforderung.

Für die ganz Ungeduldigen: Das komplette SQL befindet sich am Ende dieses Beitrags und kann in jeder PostGIS Datenbank Version >= 3.3 ausgeführt werden. Es funktioniert auch über den SQL-Editor im QGIS-DBManager. In QGIS hat man natürlich noch schönere Gestaltungsmöglichkeiten als in der Geometrievorschau in pgAdmin oder dbeaver.

Beginnen wir mit der Erläuterung bei der Textgenerierung, sie steht zwar ganz unten im SQL, ist jedoch unser Ausgangspunkt und ist für die zentrale Botschaft entscheidend.

SELECT
  ST_Boundary(
    ST_Translate(
      ST_Scale(
        ST_SetSRID(
          ST_Letters('PostGIS wuenscht'),
          4326
        ),
        0.01,
        0.008
      ),
      7,
      52.2
    )
  )

ST_Letters selbst bekommt nur den Text übergeben, der in Geometrien umgewandelt werden soll. Alle weiteren Funktionen drumherum sorgen lediglich dafür, dass die Geometrien an der gewünschten Stelle in der gewünschten Größe platziert werden: ST_SetSRID weist ihnen das SRS zu. ST_Scale sorgt für die gewünschte Größe inklusive dem Seitenverhältnis und ST_Translate schiebt die Geometrien an die gewünschte Stelle. Das umschließende ST_Boundary ist für die reine Generierung der Text-Geometrien nicht notwendig, wird aber im gesamten Script benötigt, um am Ende zusammen mit den Sternen einen einheitlichen Geometrie-Typ zu erhalten.

Die Sterne mittels SQL zu erstellen ist etwas komplexer und es brauchte ein paar Sackgassen, bevor ich die richtige Herangehensweise herausgefunden hatte.

Zunächst habe ich nach ‚postgis create stars‘ gesucht. Das brachte einen funktionierenden Treffer, [2] an dem mich zwei Punkte gestört haben. Zum einen wird dafür eine Funktion benötigt (mein Anliegen war es, möglichst wenig Spuren in der Datenbank zu hinterlassen), zum anderen empfand ich sie als nicht elegant genug, auch wenn das natürlich Ansichtssache ist und ich vielleicht nur nicht Mathematiker genug bin, die Schönheit der Funktion zu erkennen. Die nächste Idee, einfach ST_Letters ein Asterisk zu übergeben, hat nicht funktioniert, weil ST_Letters den * nicht kennt.

This is the way:

Die obige Grafik fasst die gefundene Lösung zusammen und bedarf eigentlich keiner weiteren Erläuterung. Man nehme ein kreisförmiges Polygon und ziehe jeden zweiten Stützpunkt nach außen.

Der Kreis wird mit ST_Buffer aus einem Punkt generiert (wo diese Punkte herkommen, wird am Schluss des Beitrags erläutert). Für das Versetzen der Stützpunkte könnte man nun mit Winkelberechnungen und einem echten Verschieben der Punkte arbeiten, einfacher ist es aber einen zweiten Buffer mit größerem Radius zu bilden und vom ersten Kreis die geraden und vom zweiten Kreis die ungeraden Stützpunkte zu nehmen und diese dann wieder mit ST_Makeline zu verbinden.


SELECT
      *
    FROM
      (
        SELECT
          p.pknz,
          (
            st_dumppoints(
              st_buffer(p.geom, 0.05, 'quad_segs=5')
            )
          ).path[2] as _path_2,
          (
            st_dumppoints(
              st_buffer(p.geom, 0.05, 'quad_segs=5')
            )
          ).geom as geom
        FROM
          p
      ) x
    WHERE
      _path_2 % 2 = 0

Oben der Code-Block für die geraden Punkten. Das Buffer-Polygon wird zwei Mal gebildet und gedumpt. Aus dem dump werden die Punkte und der Pfad geholt. Der Pfad (= Durchnummerierung der Punkte) wird benötigt, um hier nur die Punkte mit gerader Nummer zu selektieren (_path_2 % 2 = 0). p.pknz ist die eindeutige Kennung des Punktes aus dem mit ST_Buffer das Kreispolygon gebildet wird. Diese Kennung wird später benötigt, um beim Verbinden der Punkte zu den Sternen nur die Punkte miteinander zu verbinden, die zu einem Ursprungspunkt gehörten. Sonst würden alle Punkte aller Kreise zu einem großen Linienwirrwar verbunden werden. Dieser Block taucht im gesamten SQL zweimal auf, der zweite Block unterschiedet sich vom ersten Block durch den Radius des Buffers und die where-Bedingung, in der die ungeraden Punkte selektiert werden. Ergebnis sind dann die in der Grafik dargestellten geraden / blauen und die ungeraden / roten Stützpunkte. Beide befinden sich in den noch getrennten Common Tables b und c. In Common Table d werden beide zu einer Tabelle zusammengeführt (s. gesamtes SQL unten).

Im Common Table e werden schließlich mit ST_Makeline die zusammengehörenden Punkte zu einer Linie, dem Stern, verbunden. Hier taucht auch im GROUP BY die Punktkennung pkzn wieder auf, welche über die Gruppierung dafür sorgt, dass nur die Punkte miteinander verbunden werden, die zum selben Ursprungspunkt gehören:

SELECT
     d.pknz,
     st_Makeline(d.geom) as geom
   FROM
     d
   GROUP BY
     d.pknz

Zum Abschluss wird es wieder etwas einfacher und die Frage beantwortet, wo die oben erwähnten Basispunkte herkommen, aus denen die Sterne generiert werden.

SELECT
      ROW_NUMBER() OVER() as pknz,
      *
    FROM
      (
        SELECT
          (
            ST_SetSRID(
              (
                ST_Dump(
                  ST_GeneratePoints(
                    ST_BuildArea(
                      ST_Collect(
                        st_buffer(
                          ST_GeomFromText('LINESTRING(8 52, 14 52)'),
                          1
                        ),
                        st_buffer(
                          ST_GeomFromText('LINESTRING(4 52, 16 52)'),
                          4
                        )
                      )
                    ),
                    100
                  )
                )
              ).geom,
              4326
            )
          ) as geom
      ) y

Grundsätzlich werden die Punkte über ST_GeneratePoints erstellt, welche eine gegebene Anzahl von Punkten zufällig in einer Fläche verteilt, d.h. es wird zunächst die Bezugsfläche benötigt. Auch hier habe ich wieder ST_Buffer genutzt. Dieses Mal wird der Buffer um eine Linie gebildet, um eine Fläche zu erhalten, deren West-Ost-Ausdehnung größer ist als die Nord-Süd-Ausdehnung. Ein Punkt aus Ausgangsgeometrie würde natürlich genauso funktionieren. Oben ist zu sehen, dass ich zunächst zwei Polygone aus zwei Linien generiere. Der Grund dafür ist, dass ich den Bereich, in dem ich die Schrift ‚PostGIS wuenscht frohe Weihnachten‘ platziere, von Sternen unberührt lassen möchte. Das Donut-Polygon wird aus den beiden Buffer-Flächen über ST_BuildArea erstellt. ST_BuildArea erwartet eine Geometry Collection, wenn eine Fläche mit Loch gebildet werden soll, weshalb die beiden Pufferflächen zunächst mit ST_Collect zusammengefasst werden.

Zu guter Letzt: ST_GeneratePoints gibt einen Multipunkt zurück, mit ST_Dump mache ich daraus Einzelpunkte und der Ordnung halber setze ich noch das SRS. Mit „ROW_NUMBER() OVER() as pknz“ wird eine Zeilennummer für die Einzelpunkte erstellt, die später wie oben beschrieben, als eindeutige Kennung für die Punkte der Sterne dient.

Schlussbemerkung

Das gesamte SQL lässt sich kompakter gestalten, durch weniger Common Tables und dieZusammenfassung sich wiederholender Statements mit lediglich unterschiedlichen Parametern. Das mache ich dann nächstes Jahr an verregneten Adventsnachmittagen. So wie das SQL ist, lässt es sich einfach nachvollziehen, hoffe ich.

WITH p AS
(
       SELECT Row_number() OVER() AS pknz,
              *
       FROM   (
                     SELECT ( st_setsrid( ( St_dump( St_generatepoints( St_buildarea( St_collect( St_buffer( St_geomfromtext('LINESTRING(8 52, 14 52)'), 1 ), St_buffer( St_geomfromtext('LINESTRING(4 52, 16 52)'), 4 ) ) ), 100 ) ) ).geom, 4326 ) ) AS geom ) y ), b AS
(
       SELECT *
       FROM   (
                     SELECT p.pknz,
                            ( st_dumppoints( st_buffer(p.geom, 0.05, 'quad_segs=5') ) ).path[2] AS _path_2,
                            ( st_dumppoints( st_buffer(p.geom, 0.05, 'quad_segs=5') ) ).geom    AS geom
                     FROM   p ) x
       WHERE  _path_2 % 2 = 0 ), c AS
(
       SELECT *
       FROM   (
                     SELECT p.pknz,
                            ( st_dumppoints( st_buffer(p.geom, 0.2, 'quad_segs=5') ) ).path[2] AS _path_2,
                            ( st_dumppoints( st_buffer(p.geom, 0.2, 'quad_segs=5') ) ).geom    AS geom
                     FROM   p ) x
       WHERE  _path_2 % 2 <> 0 ), d AS
(
       SELECT *
       FROM   b
       UNION
       SELECT   *
       FROM     c
       ORDER BY pknz,
                _path_2 ), e AS
(
         SELECT   d.pknz,
                  st_makeline(d.geom) AS geom
         FROM     d
         GROUP BY d.pknz )
SELECT pknz,
       st_multi(geom) geom
FROM   e
UNION
SELECT max(e.pknz) + 1,
       st_boundary( st_translate( st_scale( st_setsrid( st_letters('PostGIS wuenscht'), 4326 ), 0.01, 0.008 ), 7, 52.2 ) )
FROM   e
UNION
SELECT   max(e.pknz) + 2,
         st_boundary( st_translate( st_scale( st_setsrid( st_letters('frohe Weihnachten!'), 4326 ), 0.01, 0.008 ), 7, 51.6 ) )
FROM     e
ORDER BY pknz;

Weiterführende Links und Referenzen

[1] https://www.crunchydata.com/blog/fun-with-letters-in-postgis-33

[2] https://selectoid.wordpress.com/tag/postgis/

 

Weitere Beiträge, die Dich interessieren könnten:

Jörg Thomsen

Jörg Thomsen ist Diplom-Geograph und seit vielen Jahren im Bereich Open-Source-GIS engagiert. Seit 2016 ist er Teil des WhereGroup-Teams in Berlin. Außerdem ist er langjähriger Dozent der FOSS Academy, dem Schulungsinstitut der WhereGroup und Lehrbeauftragter im Bereich Geoinformatik an der Beuth-Hochschule.

 

Artikel teilen:

Zurück

Tags