# Fun with Letters in PostGIS 3.3!

Jacob Coblentz

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!

Written by

Jacob Coblentz

January 12, 2023 More by this author