Fun with Letters in PostGIS 3.3!

Working at Crunchy Data on the spatial team, I'm always looking for new features and fun things to show on live demos. I recently started playing around with ST_Letters and wanted to jot down some quick code samples for playing around with this feature, introduced in PostGIS 3.3. These examples are super easy to use, they don't need any data!

The screenshots shown below came from pgAdmin's geometry viewer and will also work with other query GUI tools like QGIS or DBeaver.

ST_Letters

Here's a simple example to get started with ST_Letters. This will work on any Postgres database, running the PostGIS extension version 3.3+.

Select ST_Letters('PostGIS');

It's also possible to overlay letters on a map, just like any other polygon. Since the default for ST_Letters results in a polygon starting at the baseline at the origin of the chosen projection, with a maximum height of 100 "units" (from the bottom of the descenders to the tops of the capitals).

That's not ideal. We need a way to both move it and resize it.

First, we want to make a point in the middle of San Francisco in order to serve as a centroid for where we want to move the letters, and we also want to rescale the letters in order to approximately fit over the City of San Francisco. Using the formula for converting units in WGS84 to meters, 0.001 works approximately well enough to fit over the San Francisco Bay Area.

Next we use ST_Translate in order to move the letters from the top of the map to fit over the Bay Area. Finally, mostly because it looks cool, we use ST_Rotate to rotate the polygon 45 degrees.

WITH
san_fran_pt AS (
  SELECT ST_Point(-122.48, 37.758, 4326) AS geom),
letters AS (
  SELECT ST_Scale(ST_SetSRID(
           ST_Letters('San Francisco'), 4326),
           0.001, 0.001) AS geom),
letters_geom AS (
    SELECT ST_Translate(
            letters.geom,
            ST_X(san_fran_pt.geom) - ST_X(ST_Centroid(letters.geom)),
            ST_Y(san_fran_pt.geom) - ST_Y(ST_Centroid(letters.geom))
        ) AS geom
    FROM letters, san_fran_pt
)
SELECT ST_Rotate(geom, -pi() / 4, ST_Centroid(geom))
FROM letters_geom;

ST_ConcaveHull demo'd with ST_Letters

A great use case for ST_Letters is for demoing PostGIS functions. In this post, I'm going to demo the function ST_ConcaveHull, which creates a concave polygon which encloses the vertices of a target geometry. ST_ConcaveHull was recently updated in PostGIS 3.3.0, in order to use GEOS 3.11, which makes the input parameters easier to understand and results in a large speed upgrade. Here's a short demo of how different parameters of param_pctconvex and param_allow_holes for ST_ConcaveHull operate on points generated by ST_GeneratePoints and ST_Letters.

First, let's generate a table of randomly generated points that fill in the letters in 'postgis'.

CREATE TABLE public.word_pts AS
WITH word AS (
  SELECT ST_Letters('postgis') AS geom
  ),
letters AS ( -- dump letter multipolygons into individual polygons
  SELECT (ST_Dump(word.geom)).geom
  FROM word
  )
SELECT
  letters.geom AS polys,
  ST_GeneratePoints(letters.geom, 100) AS pts
FROM letters;

SELECT pts FROM word_pts.pts

Then, we set the convexity to a fairly high parameter (param_pctconvex=0.75, indicating a highly convex shape), and don't allow there to be holes in the shape (param_allow_holes=false)

SELECT ST_ConcaveHull(pts, 0.75, false) FROM word_pts;

Doesn't look much like 'postgis'!

Next, we reduce the convexity, but don't allow holes in the shape.

SELECT ST_ConcaveHull(pts, 0.5, false) FROM word_pts;

A little better, but still hard to recognize 'postgis'. What if we allowed holes?

SELECT ST_ConcaveHull(pts, 0.5, true) FROM word_pts;

This starts to look a bit more like the word 'postgis', with the hole in 'p' being clear.

As we start to make the shape more concave, it begins to take on more and more recognizable as 'postgis'....until it doesn't and starts to look closer to modern art.

SELECT ST_ConcaveHull(pts, 0.35, true) FROM word_pts;

SELECT ST_ConcaveHull(pts, 0.05, true) FROM word_pts;

Polygons too!

ST_ConcaveHull is also useful on multipolygons, and follows the same properties as demo'd on multipoints. It's important to note that if there are already holes in the existing multipolygon, setting param_allow_holes=false will still create convex polygons with "holes" in the middle, following the original polygon. The concave hulls will always contains the original polygons!

SELECT ST_ConcaveHull(ST_Letters('postgis'), 0.5, false);

As the convexity decreases and holes are allowed, the shape looks more and more like the original polygons in the original table.

SELECT ST_ConcaveHull(ST_Letters('postgis'), 0.1, true);

ST_TriangulatePolygon

The last demo here is the function ST_TriangulatePolygon, new in PostGIS 3.3. This function computes the "best quality" triangulation of a polygon (and also works on multipolygons too!). This can be extremely useful for computing meshes of polygons in a quick and efficient manner.

SELECT ST_TriangulatePolygon(ST_Letters('postgis'));

Summary

ST_Letters provides a useful starting point for demoing functions on points and polygons. The new improvements in ST_ConcaveHull make it more useful for generating concave hulls of geometries and they are significantly more intuitive to use. ST_TriangulatePolygon can be useful for finding meshes of polygons and multipolygons. The team at Crunchy Data will continue to make important contributions to PostGIS in order to help our users create interesting and innovative open source solutions!