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.
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+.
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;
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.
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
ST_ConcaveHull operate on points generated by
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 (
indicating a highly convex shape), and don't allow there to be holes in the
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;
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
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);
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.
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
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!
January 12, 2023 •More by this author